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.
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.
To solve this on the DO MySQL Cloud DBs, we added the following to the MySQL 8 SQL mode defaults:
ANSI (Shorthand) ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION NO_ZERO_DATE NO_ZERO_IN_DATE STRICT_ALL_TABLES STRICT_TRANS_TABLES
If anyone is still having this issue on droplets, please check this out. It will solve it. HERE
Hi all,
now we dont need any custom code on function or wherever else. You can easily control these options from database dashboard on DO admin panel.
Here you got all information. https://www.digitalocean.com/docs/databases/mysql/how-to/set-sql-mode/
Thanks, Giorgos
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
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
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
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
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
I did a change in this code and I believe is a little better use of the function set_sql_mode:
Solve the Warnings.