sql_mode ANSI is incompatible with WP in MySQL 5.7.5+

[jnylen0]

Starting in MySQL 5.7.5 the sql_mode value ANSI implies ONLY_FULL_GROUP_BY: ​https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_ansi

The ONLY_FULL_GROUP_BY mode is already excluded by code in the wpdb class, because several queries in WordPress are invalid according to this mode. However, in MySQL 5.7.5 and up, when the ANSI mode is enabled, the ONLY_FULL_GROUP_BY mode remains enabled even after being "unset" as done by the relevant wpdb code.

This leads to failure to load the media library page, with the following errors in the log, and possibly others:

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'dbname.wp_posts.post_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by for query SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/themename/404.php'), the_widget, WP_Widget_Archives->widget, wp_get_archives

Expression #1 of ORDER BY clause is not in SELECT list, references column 'dbname.wp_posts.post_date' which is not in SELECT list; this is incompatible with DISTINCT for query SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month FROM wp_posts WHERE post_type = 'attachment' ORDER BY post_date DESC made by wp_enqueue_media

The ANSI SQL mode is not enabled in default MySQL installations, but it is enabled in managed DigitalOcean databases, which is where I saw this error: ​https://www.digitalocean.com/products/managed-databases-mysql/

DigitalOcean does not allow setting the global sql_mode value, but this can be patched in core either by always excluding the ANSI mode from the allowed `sql_mode values, or only excluding it for MySQL 5.7.5 and up.

In the meantime here is a workaround: ​https://gist.github.com/nylen/abc5969a7bda5b3531edf84dfe9166ba

Related: #26847

[jnylen0]

Proposed patch

[williampatton]

Also running into this same issue here caused by changes with ANSI in various different MySQL servers. MySQL 8 installs are becoming more common (this is what I think runs at DO managed DB service) and this issue affects those users on default installs of mysql 8. It is wher eI have faced this same issue anyway but I guess it's from mysql 5.7.5 onward.

Since frequency I am seeing this has increased I think we should tackle this ASAP. The 5.4 milestone seems reasonable but pushing back any longer than that I would be worried.

[jnylen0]

Yes, DO managed MySQL is MySQL 8, without the possibility to change the global mode permanently (they don't give you a true admin user, or access to the server config file).

If this happened with all MySQL 8 servers then someone probably would have reported this by now. The MySQL documentation also indicates that ANSI is not enabled by default: ​https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-setting

I guess DO must have done some custom configuration to enable the ANSI mode.