[wp-trac] [WordPress Trac] #48377: sql_mode ANSI is incompatible with WP in MySQL 5.7.5+

[WordPress Trac]

#48377: sql_mode ANSI is incompatible with WP in MySQL 5.7.5+
--------------------------+-----------------------------
 Reporter:  jnylen0       |      Owner:  (none)
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 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

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/48377>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform