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.

[SergeyBiryukov]

In 47171:

Database: Add ANSI to the list of incompatible SQL modes.

Starting with MySQL 5.7.5, the ANSI mode implies ONLY_FULL_GROUP_BY, which is already listed in wpdb as incompatible per [27072].

When ANSI is enabled on MySQL 5.7.5+, ONLY_FULL_GROUP_BY remains enabled even after being "unset" by wpdb::set_sql_mode().

To prevent this, the ANSI mode should also be listed as incompatible. It is not enabled on default MySQL installations.

Props jnylen0.
Fixes #48377.

[kenjigarland]

For any fellow WP + Digital Ocean managed database users out there who come across this ticket in the future, sometime after @jnylen0's comment above, Digital Ocean added a "Global SQL Mode" option to the Settings tab for managed db servers in their control panel, so now customers can change the default sql_mode.

That setting came in handy for my agency, where my coworkers & I ran into a few issues with plugins that used custom queries containing double quotes. Turned out we still needed to explicitly disable ANSI_QUOTES, even though ANSI had been disabled by core.

[jnylen0]

Replying to kenjigarland:

For any fellow WP + Digital Ocean managed database users out there who come across this ticket in the future, sometime after @jnylen0's comment above, Digital Ocean added a "Global SQL Mode" option to the Settings tab for managed db servers in their control panel, so now customers can change the default sql_mode.

Thanks for the update @kenjigarland.

That setting came in handy for my agency, where my coworkers & I ran into a few issues with plugins that used custom queries containing double quotes. Turned out we still needed to explicitly disable ANSI_QUOTES, even though ANSI had been disabled by core.

I have also seen this recently on a plugin, but we are going to fix the plugin instead.

Another option would be to adapt the code I mentioned above (​https://gist.github.com/nylen/abc5969a7bda5b3531edf84dfe9166ba) to disable ANSI_QUOTES, for me at least this would be better than requiring the database to have certain settings.