#48377 closed defect (bug) (fixed)
sql_mode ANSI is incompatible with WP in MySQL 5.7.5+
Reported by: | jnylen0 | Owned by: | SergeyBiryukov |
---|---|---|---|
Milestone: | 5.4 | Priority: | normal |
Severity: | normal | Version: | |
Component: | Database | Keywords: | |
Focuses: | Cc: |
Description
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
Attachments (1)
Change History (9)
#2
@
5 years ago
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.
#3
@
5 years ago
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.
#4
@
5 years ago
- Owner set to SergeyBiryukov
- Resolution set to fixed
- Status changed from new to closed
In 47171:
#5
follow-up:
↓ 6
@
4 years ago
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.
#6
in reply to:
↑ 5
@
4 years ago
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 thoughANSI
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.
#7
@
4 years ago
+1 for disabling ANSI_QUOTES
. Just faced some hard to debug issues on a client's site because of this setting.
#8
@
3 years ago
I found this code with my modification to take care of this error if you run into it.
add_action( 'init', 'mysql_set_sql_mode_traditional', -1); function mysql_set_sql_mode_traditional() { global $wpdb; $wpdb->query("SET SESSION sql_mode = 'TRADITIONAL'"); $wpdb->query("SET SESSION sql_mode = 'NO_ZERO_DATE'"); $wpdb->query("SET SESSION sql_mode = 'NO_ZERO_IN_DATE'"); }
Add it into your function.php theme file or a file in mu-plugin
Proposed patch