sql_mode=only_full_group_by

September 16, 2019 1.7k views
MySQL Ubuntu 18.04
Giroin
By:
Giroin

I have an ubuntu 18.04 connected with a shared mysql, both fresh installations. Yesterday I transfered the website and everything looked fine so far. This morning I realised that the search function is not working at all on wp-admin. Check the errors and I got this 

WordPress database error Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dbname.lt.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by for query

Any ideas as the mysql server is shared and I can’t change that.

Thanks
G.

2 comments
  • taxworldit October 17, 2019
    Report

    I did a change in this code and I believe is a little better use of the function setsqlmode:

    add_action( 'init', 'mysqlfix', -1);

function mysqlfix() {
    global $wpdb;
    # Removed ONLY_FULL_GROUP_BY
    $wpdb->set_sql_mode(['REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','ANSI','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','ERROR_FOR_DIVISION_BY_ZERO','NO_ENGINE_SUBSTITUTION']);
}

    Solve the Warnings.

  • Giroin October 21, 2019
    Report

    Hi @taxworldit

    In my case your script doesn’t work. Search stops working again. It seems that the SET SESSION sql_mode = is required.

    Thanks,
    Giorgos

Sign In to Comment
6 Answers
Giroin October 2, 2019

Hi,

Sure!

Although is the latest wp version I managed to solve this problem by using the following function on functions.php

add_action( 'init', 'mysqlfix',-1 );

function mysqlfix() {
    global $wpdb;
    # Removed ONLY_FULL_GROUP_BY
    $wpdb->set_sql_mode("SET SESSION sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'");
}

Technicians from DO said they will let me know when is fixed on their side so I won’t need this piece of code anymore. Still haven’t heard form them.

Thanks,
Giorgos

Report
  • James2017 October 3, 2019

    Hi Giorgos,
    Thanks for sharing the fix, it helps on both our WP sites with the error but now they have the following 3 persistent warnings, did you see any of these?

    Warning: arraychangekey_case() expects parameter 1 to be array, string given in /var/www/html/wp-includes/wp-db.php on line 870

    Warning: Invalid argument supplied for foreach() in /var/www/html/wp-includes/wp-db.php on line 881

    Warning: implode(): Invalid arguments passed in /var/www/html/wp-includes/wp-db.php on line 887

    Thanks
    James

    Report
    • Giroin October 4, 2019

      Hi James,

      I also get these warnings, I confirm.

      Thanks,
      Giorgos

      Report
    • taxworldit October 17, 2019

      use this way:

      add_action( 'init', 'mysqlfix', -1);

function mysqlfix() {
    global $wpdb;
    # Removed ONLY_FULL_GROUP_BY
    $wpdb->set_sql_mode(['REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','ANSI','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','ERROR_FOR_DIVISION_BY_ZERO','NO_ENGINE_SUBSTITUTION']);
}
      Report
bobbyiliev MOD September 16, 2019

Hello,

What is the WordPress version that you are using?

As far as I can see with the latest WordPress version there is a set_sql_mode method that auto checks the sql_mode and ensures that the correct one is set for the current session:

https://github.com/WordPress/WordPress/blob/master/wp-includes/wp-db.php

If you are already using the latest one and it still does not work, I think that you might have to contact your SQL provider and ask them if they could adjust this for you.

Regards,
Bobby

Report
  • Giroin September 16, 2019

    Thanks Bobby,

    So are you suggesting to add this on wp-config? if yes what’s the value to disable that?

    I am using the new service of DO ‘managed mysql’ and I contacted them already, expecting their reply.

    Thanks,
    Giorgos

    Report
    • bobbyiliev MOD September 16, 2019

      Hello,

      There’s no need to add this to the wp-config, it should be available with the latest Wordpress versions out of the box. What is your current Wordpress version?

      I’m currently running a couple of Wordpress websites with the DO managed MySQL databases without any issues, so I think that you might not have to change the sql_mode but only update your WP version to the latest one.

      Regards,
      Bobby

      Report
Giroin November 1, 2019

Hi @jorgv

Actually I haven’t got any reply since my first contact, will do a follow up to see if they have any news.

Thanks

Report
suderman November 20, 2019

Does anyone have a working solution for this yet? I’m still seeing errors like the following on current WordPress sites using DigitalOcean’s managed MySQL database:

WordPress database error: [Expression #1 of ORDER BY clause is not in SELECT list, references column 'mastersgallery.wp_posts.post_date' which is not in SELECT list; this is incompatible with DISTINCT]
SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month FROM wp_posts WHERE post_type = 'attachment' ORDER BY post_date DESC
Report
partnersf511c532042fdf525d December 10, 2019

To solve this on the DO MySQL Cloud DBs, we added the following to the MySQL 8 SQL mode defaults:

ANSI (Shorthand)
ERRORFORDIVISIONBYZERO
NOENGINESUBSTITUTION
NOZERODATE
NOZEROINDATE
STRICTALLTABLES
STRICTTRANS_TABLES

Report
Giroin December 13, 2019
Show answer This comment has been marked as resolved by Giroin.
Have another answer? Share your knowledge.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Related