WordPress.org

Make WordPress Core

Opened 4 months ago

Closed 3 weeks ago

#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)

48377.diff (462 bytes) - added by jnylen0 4 months ago.
Proposed patch

Download all attachments as: .zip

Change History (5)

@jnylen0
4 months ago

Proposed patch

#1 @SergeyBiryukov
4 months ago

  • Milestone changed from Awaiting Review to 5.4

#2 @williampatton
4 months 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 @jnylen0
4 months 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.

Last edited 4 months ago by jnylen0 (previous) (diff)

#4 @SergeyBiryukov
3 weeks ago

  • Owner set to SergeyBiryukov
  • Resolution set to fixed
  • Status changed from new to closed

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.

Note: See TracTickets for help on using tickets.