[wp-trac] [WordPress Trac] #49344: Make tests running with MySQL 8.0

[WordPress Trac]

#49344: Make tests running with MySQL 8.0
-------------------------+-----------------------------
 Reporter:  kaggdesign   |       Owner:  SergeyBiryukov
     Type:  enhancement  |      Status:  reviewing
 Priority:  normal       |   Milestone:  5.4
Component:  Database     |     Version:  trunk
 Severity:  normal       |  Resolution:
 Keywords:  has-patch    |     Focuses:
-------------------------+-----------------------------

Comment (by SergeyBiryukov):

 Thanks for the patch!

 Some history on the affected tests, for reference: [27072] / #26847,
 [30704], [37574] / #36948.

 1. The failure in `test_set_sql_mode()` (also reported in #44586) is
 caused by `NO_AUTO_CREATE_USER`, which is indeed
 [https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html#mysqld-8-0-11
 -deprecation-removal no longer supported as of MySQL 8.0.11]. The query
 results in an error:
 {{{
 SET SESSION sql_mode='IGNORE_SPACE,NO_AUTO_CREATE_USER'

 #1231 - Variable 'sql_mode' can't be set to the value of
 'NO_AUTO_CREATE_USER'
 }}}
  Replacing it with `NO_AUTO_VALUE_ON_ZERO`, which is available on both
 MySQL 5.7 and 8.0, works as expected.
  Replacing `ONLY_FULL_GROUP_BY` with `STRICT_TRANS_TABLES`, however, seems
 unnecessary in my testing, since `ONLY_FULL_GROUP_BY` is also available on
 both MySQL 5.7 and 8.0.

 2. The failure in `test_spatial_indices()` (also reported in #44384) is
 caused by changing the `GeometryCollection` data type name to
 `GeomCollection` in
 [https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html#mysqld-8-0-11
 -spatial-support MySQL 8.0.11], which the latter being the preferred
 name.[[BR]][[BR]]
  Adding `SRID 0` did not fix the issue in my testing. Checking the
 database version and changing the data type accordingly in
 [attachment:49344.diff] did the trick.

 3. The above two changes should be enough to run the tests on MySQL
 8.0.16. On MySQL 8.0.19, however, I get more failures in
 `tests/dbdelta.php` (25 in total). They all look similar to this:
 {{{
 2) Tests_dbDelta::test_column_type_change
 Failed asserting that two arrays are equal.
 --- Expected
 +++ Actual
 @@ @@
  Array (
 -    'wptests_dbdelta_test.id' => 'Changed type of wptests_dbdelta_test.id
 from bigint(20) to int(11)'
 +    'wptests_dbdelta_test.id' => 'Changed type of wptests_dbdelta_test.id
 from bigint to int(11)'
  )

 tests/phpunit/tests/dbdelta.php:151
 }}}
  This is due to [https://dev.mysql.com/doc/refman/8.0/en/numeric-type-
 attributes.html MySQL 8.0.17+ no longer supporting the display width
 attribute] for integer data types:[[BR]][[BR]]
 > As of MySQL 8.0.17, the ZEROFILL attribute is deprecated for numeric
 data types, as is the display width attribute for integer data types.
 Support for ZEROFILL and display widths for integer data types will be
 removed in a future MySQL version. Consider using an alternative means of
 producing the effect of these attributes.
  When creating the table, `bigint(20)` becomes just `bigint`.
 [attachment:49344.2.diff] accounts for that as well.

 4. With the above issues resolved, there's still one failure on MySQL
 8.0.17+:
 {{{
 1)
 Tests_dbDelta::test_wp_get_db_schema_does_no_alter_queries_on_existing_install
 Failed asserting that an array is empty.
 tests/phpunit/tests/dbdelta.php:693
 }}}
  This is caused by the same `bigint(20)`/`bigint` discrepancy coming from
 `wp_get_db_schema()`. It should probably be explored in a new ticket,
 since it would likely require changes not just to the tests, but also to
 `wp_get_db_schema()` and/or `dbDelta()`.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/49344#comment:3>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform