Make tests running with MySQL 8.0

[kaggdesign]

3 tests are failing with MySQL 8.0 due to deprecated modes and use of spacial keys.

Attached is the patch which works as with MySQL 5.7, so with MySQL 8.0.

[SergeyBiryukov]

Related: #38032, #44586, #47280, #48377, #44384.

[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 ​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 ​MySQL 8.0.11, with the latter being the preferred name.
   
   Adding SRID 0 did not fix the issue in my testing. Checking the database version and changing the data type accordingly in 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 ​MySQL 8.0.17+ no longer supporting the display width attribute for integer data types:
   
   

   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. 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().

[SergeyBiryukov]

In 47183:

Tests: Allow wpdb::set_sql_mode() tests to run on MySQL 8.0.11+.

MySQL 8.0.11 removed support for the NO_AUTO_CREATE_USER SQL mode, which was used in as an example of a mode compatible with WordPress.

NO_AUTO_VALUE_ON_ZERO is now used in the affected tests instead, which is supported on both MySQL 5.7.x and 8.0.x.

Props kaggdesign, jeremyfelt, afragen.
Fixes #44586. See #49344.

[SergeyBiryukov]

Replying to SergeyBiryukov:

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().

Created #49364 to address this.

[SergeyBiryukov]

In 47184:

Tests: Allow dbDelta() tests to (mostly) run on MySQL 8.0.11+.

• MySQL 8.0.11 changed the GeometryCollection data type name to GeomCollection, with the latter being the preferred name.
• MySQL 8.0.17 removed support for the display width attribute for integer data types. Previously, default display width of 20 digits was used: BIGINT(20).

The affected tests now check the MySQL server version and use the appropriate data types.

This leaves one unresolved failure on MySQL 8.0.17+ to be addressed in the future, caused by the same BIGINT display width discrepancy coming from wp_get_db_schema().

Props kaggdesign, ottok, jeremyfelt, SergeyBiryukov.
Fixes #44384, #49344. See #49364.

[SergeyBiryukov]

In 47185:

Coding Standards: Fix WPCS issue in [47184].

See #49344.