Question

WordPress database error: [Expression #1 of ORDER BY clause is not in SELECT list...

Posted October 2, 2019 1.1k views
DigitalOcean Managed MySQL Database

Hi,
We’re using DO managed database for two of our Wordpress sites, one of them shows the following error with debug=true in wp-config.php

WordPress database error: [Expression #1 of ORDER BY clause is not in SELECT list, references column ‘wordpress-programs.wpposts.postdate’ which is not in SELECT list; this is incompatible with DISTINCT]
SELECT DISTINCT YEAR( postdate ) AS year, MONTH( postdate ) AS month FROM wpposts WHERE posttype = 'attachment’ ORDER BY post_date DESC

Wordpress 5.2.3. PHP 7.3.9-1+ubuntu18.04.1+deb.sury.org+1, MySQL 8.0.16 - Linux

The other site is just fine, see config below and the content is identical i.e we have similar posts on both

Wordpress 5.2.3 PHP 7.2.19-0ubuntu0.18.04.2, MySQL 8.0.16 - Linux

We’ve tried the fixes for Disable ONLYFULLGROUP_BY from the following link but didn’t help. Can you please review and help?

https://stackoverflow.com/questions/23921117/disable-only-full-group-by

Thanks
James

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.

×
2 answers

Hello,

I think recently another person had the same issue. You can have a look at this question here:

https://www.digitalocean.com/community/questions/sql_mode-only_full_group_by?comment=82397

They were able to fix the problem by setting the sql_mode on session as instructed by the DO support team.

Hope that this helps!
Regards,
bobby

Hi Bobby,

Thanks, that was quick!

I did see this post before posting but please note that I’m not privy to what the DO support guys instructed this user to do, and also just guessing it was similar to - SET SESSION sqlmode=(SELECT REPLACE(@@sqlmode,‘ONLYFULLGROUP_BY’,“));

Which I tried with no luck

James

  • I also had this issue. Removing ONLYFULLGROUPBY didn’t do a thing.

    I believe it’s because MySQL was in strict mode. The solution for me was, at the top of the functions.php file in my theme folder, I added the line

    $wpdb->query("SET SESSION sql_mode = 'TRADITIONAL'");
    

    If you want to use an action instead, place something like this in your functions.php file:

    add_action( 'init', 'mysql_set_sql_mode_traditional', -1);
    
    function mysql_set_sql_mode_traditional() {
        global $wpdb;
        $wpdb->query("SET SESSION sql_mode = 'TRADITIONAL'");
    }
    

    Hope it helps.

    • Hi I tried the above solutions and added the above code in functions.php, but the error still persists. Is there any other solution to this problem?

      • DO recently changed some things. You can now configure the global SQL mode in the Settings page of the Database Cluster in your DigitalOcean account.

        If you remove STRICT_ALL_TABLES or select TRADITIONAL in the list of SQL modes, this problem will go away.

Submit an Answer