Question

sql_mode=only_full_group_by

Posted September 16, 2019 2.4k views
MySQLUbuntu 18.04

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.

Add a comment
Giroin
By:
Giroin
Add a comment
2 comments
  • taxworldit October 17, 2019
    Reply 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
    Reply 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

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
6 answers
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

Reply Report
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

Reply 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

    Reply Report
    • Giroin October 4, 2019

      Hi James,

      I also get these warnings, I confirm.

      Thanks,
      Giorgos

      Reply 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']);
}
      Reply Report
bobbyiliev 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

Reply 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

    Reply Report
    • bobbyiliev 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

      Reply 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

Reply 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
Reply Report
Giroin December 13, 2019
Show answer This answer has been marked as resolved by Giroin.
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help