Related

Join the DigitalOcean Community Community

Join 1M+ other developers and:

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
 Join Now
Private connection to Managed DB not working from within same region droplet. Question Question
Connecting from Node.js to MySQL managed database Question Question

Question

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

Posted October 2, 2019 3.8k 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

Add a comment
James2017
By:
James2017

Related

Join the DigitalOcean Community Community

Join 1M+ other developers and:

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
 Join Now
Private connection to Managed DB not working from within same region droplet. Question Question
Connecting from Node.js to MySQL managed database Question Question

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.

×
Submit an Answer
2 answers
bobbyiliev October 2, 2019

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

Reply Report
James2017 October 2, 2019

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

Reply Report
  • MuCao October 31, 2019

    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.

    Reply Report
    • webmaster92cb0a5eb126adfa8 January 10, 2020

      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?

      Reply Report
      • MuCao January 10, 2020

        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.

        Reply Report

Related

Looking for something else?

Ask a new question Search for more help