WordPress.org

Make WordPress Core

Opened 2 months ago

Closed 2 months ago

Last modified 2 months ago

#49344 closed enhancement (fixed)

Make tests running with MySQL 8.0

Reported by: kaggdesign Owned by: SergeyBiryukov
Milestone: 5.4 Priority: normal
Severity: normal Version: 5.4
Component: Database Keywords: has-patch
Focuses: Cc:

Description

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.

Attachments (2)

Make_tests_running_with_MySQL_8_0.patch (2.3 KB) - added by kaggdesign 2 months ago.
49344.diff (12.0 KB) - added by SergeyBiryukov 2 months ago.

Download all attachments as: .zip

Change History (9)

#1 @SergeyBiryukov
2 months ago

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

Last edited 2 months ago by SergeyBiryukov (previous) (diff)

#2 @SergeyBiryukov
2 months ago

  • Milestone changed from Awaiting Review to 5.4
  • Owner set to SergeyBiryukov
  • Status changed from new to reviewing

#3 follow-up: @SergeyBiryukov
2 months ago

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.
  1. 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.
  1. 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.diff accounts for that as well.
  1. 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().
Last edited 2 months ago by SergeyBiryukov (previous) (diff)

#4 @SergeyBiryukov
2 months ago

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.

#5 in reply to: ↑ 3 @SergeyBiryukov
2 months ago

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.

#6 @SergeyBiryukov
2 months ago

  • Resolution set to fixed
  • Status changed from reviewing to closed

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.

#7 @SergeyBiryukov
2 months ago

In 47185:

Coding Standards: Fix WPCS issue in [47184].

See #49344.

Note: See TracTickets for help on using tickets.