Make WordPress Core

Opened 3 years ago

Closed 20 months ago

Last modified 20 months ago

#51740 closed defect (bug) (fixed)

Test failures using MySQL 8

Reported by: desrosj's profile desrosj Owned by: sergeybiryukov's profile SergeyBiryukov
Milestone: 6.1 Priority: normal
Severity: normal Version:
Component: Database Keywords: has-patch
Focuses: Cc:

Description

I was doing some experimenting adding multiple MySQL versions to the GitHub Action workflow matrix, and noticed that there are 4 test failures in the local Docker environment when running MySQL 8.

1) Tests_dbDelta::test_wp_get_db_schema_does_no_alter_queries_on_existing_install
Failed asserting that an array is empty.

/var/www/tests/phpunit/tests/dbdelta.php:711

2) Tests_Meta_Query::test_meta_type_key_should_be_passed_to_meta_query
Failed asserting that two arrays are identical.
--- Expected
+++ Actual
@@ @@
-Array &0 (
-    0 => 1424
-)
+Array &0 ()

/var/www/tests/phpunit/includes/abstract-testcase.php:704
/var/www/tests/phpunit/tests/meta/query.php:775

3) Tests_Query_MetaQuery::test_compare_key_regexp_rlike
Failed asserting that two arrays are identical.
--- Expected
+++ Actual
@@ @@
-Array &0 (
-    0 => 36059
-)
+Array &0 ()

/var/www/tests/phpunit/includes/abstract-testcase.php:704
/var/www/tests/phpunit/tests/query/metaQuery.php:2126

4) Tests_Query_MetaQuery::test_compare_key_not_regexp
Failed asserting that two arrays are identical.
--- Expected
+++ Actual
@@ @@
-Array &0 (
-    0 => 36063
-    1 => 36064
-)
+Array &0 ()

/var/www/tests/phpunit/includes/abstract-testcase.php:704
/var/www/tests/phpunit/tests/query/metaQuery.php:2169

I haven't looked into the failures yet, but I added some var_dumps() to help debugging.

Attachments (1)

51740.diff (1.8 KB) - added by SergeyBiryukov 20 months ago.

Download all attachments as: .zip

Change History (16)

#1 @SergeyBiryukov
3 years ago

Related to the first failure: #49364.

Previously: #44384, #49344.

#3 @desrosj
3 years ago

  • Milestone changed from Future Release to 5.9

I'd like to solve the MySQL 8 test failures for 5.9 to that we can look at expanding our testing combinations.

#4 @hellofromTonya
2 years ago

  • Milestone changed from 5.9 to 6.0

5.9 Beta 1 is in 4 days. Like the other MySQL 8.0 tickets, moving to 6.0.

This ticket was mentioned in Slack in #core by costdev. View the logs.


2 years ago

#6 @peterwilsoncc
2 years ago

  • Milestone changed from 6.0 to Future Release

I'm moving this to a future release as RC 1 is approaching in a few days.

#7 @SergeyBiryukov
20 months ago

In 53897:

Database: Ignore display width for integer data types in dbDelta() on MySQL 8.0.17 or later.

MySQL 8.0.17 deprecated 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. You should expect support for ZEROFILL and display widths for integer data types to be removed in a future version of MySQL. Consider using an alternative means of producing the effect of these attributes. For example, applications can use the LPAD() function to zero-pad numbers up to the desired width, or they can store the formatted numbers in CHAR columns.

In practice, this means that display width is removed for integer types when creating a table:

  • BIGINT(20)BIGINT
  • INT(11)INT
  • MEDIUMINT(9)MEDIUMINT
  • SMALLINT(6)SMALLINT
  • TINYINT(4)TINYINT

Note: This only applies specifically to MySQL 8.0.17 or later. In MariaDB, display width for integer types is still available and expected.

This commit ensures that dbDelta(), which relies on the DESCRIBE SQL command to get the existing table structure and field types, when running on MySQL 8.0.17 or later, does not unnecessarily attempt to convert BIGINT fields back to BIGINT(20), INT back to INT(11), etc. When comparing the field type in the query with the existing field type, if display width is the only difference, it can be safely ignored to match MySQL behavior.

The change is covered by existing dbDelta() unit tests:

  • A test for not altering wp_get_db_schema() queries on an existing install using MySQL 8.0.17+ now passes.
  • More than twenty tests which previously failed on PHP 8.0.x + MariaDB due to incorrect expectations, caused by MariaDB version reporting not being consistent between PHP versions, now pass.

References:

Follow-up to [1575], [18899], [37525], [47183], [47184].

Props SergeyBiryukov, pbearne, leewillis77, JavierCasares, desrosj, costdev, johnbillion.
Fixes #49364. See #51740.

#8 @SergeyBiryukov
20 months ago

Some more context on the remaining three failures:

2) Tests_Meta_Query::test_meta_type_key_should_be_passed_to_meta_query
Failed asserting that two arrays are identical.
--- Expected
+++ Actual
@@ @@
-Array &0 (
-    0 => 1424
-)
+Array &0 ()

/var/www/tests/phpunit/includes/abstract-testcase.php:704
/var/www/tests/phpunit/tests/meta/query.php:775

caused by:

WordPress database error Character set 'utf8mb4_unicode_520_ci' cannot be used in conjunction with 'binary' in call to regexp_like. for query
                        SELECT SQL_CALC_FOUND_ROWS  wptests_posts.ID
                        FROM wptests_posts  INNER JOIN wptests_postmeta ON ( wptests_posts.ID = wptests_postmeta.post_id )
                        WHERE 1=1  AND (
  wptests_postmeta.meta_key REGEXP BINARY 'AAA_FOO_.*'
) AND ((wptests_posts.post_type = 'post' AND (wptests_posts.post_status = 'publish')))
                        GROUP BY wptests_posts.ID
                        ORDER BY wptests_posts.post_date DESC
                        LIMIT 0, 10

3) Tests_Query_MetaQuery::test_compare_key_regexp_rlike
Failed asserting that two arrays are identical.
--- Expected
+++ Actual
@@ @@
-Array &0 (
-    0 => 36059
-)
+Array &0 ()

/var/www/tests/phpunit/includes/abstract-testcase.php:704
/var/www/tests/phpunit/tests/query/metaQuery.php:2126

caused by:

WordPress database error Character set 'utf8mb4_unicode_520_ci' cannot be used in conjunction with 'binary' in call to regexp_like. for query
                        SELECT SQL_CALC_FOUND_ROWS  wptests_posts.ID
                        FROM wptests_posts  INNER JOIN wptests_postmeta ON ( wptests_posts.ID = wptests_postmeta.post_id )
                        WHERE 1=1  AND (
  wptests_postmeta.meta_key RLIKE BINARY 'AAA_FOO_.*'
) AND ((wptests_posts.post_type = 'post' AND (wptests_posts.post_status = 'publish')))
                        GROUP BY wptests_posts.ID
                        ORDER BY wptests_posts.post_date DESC
                        LIMIT 0, 10

4) Tests_Query_MetaQuery::test_compare_key_not_regexp
Failed asserting that two arrays are identical.
--- Expected
+++ Actual
@@ @@
-Array &0 (
-    0 => 36063
-    1 => 36064
-)
+Array &0 ()

/var/www/tests/phpunit/includes/abstract-testcase.php:704
/var/www/tests/phpunit/tests/query/metaQuery.php:2169

caused by:

WordPress database error Character set 'utf8mb4_unicode_520_ci' cannot be used in conjunction with 'binary' in call to regexp_like. for query
                        SELECT SQL_CALC_FOUND_ROWS  wptests_posts.ID
                        FROM wptests_posts  INNER JOIN wptests_postmeta ON ( wptests_posts.ID = wptests_postmeta.post_id )
                        WHERE 1=1  AND (
  NOT EXISTS (SELECT 1 FROM wptests_postmeta mt1 WHERE mt1.post_ID = wptests_postmeta.post_ID AND mt1.meta_key REGEXP BINARY 'AAA_FOO_.*' LIMIT 1)
) AND ((wptests_posts.post_type = 'post' AND (wptests_posts.post_status = 'publish')))
                        GROUP BY wptests_posts.ID
                        ORDER BY wptests_posts.post_date DESC
                        LIMIT 0, 10

#9 @SergeyBiryukov
20 months ago

Seems to be related to Bug #104387 CHARACTER_SET_MISMATCH issue with regex comparisons.

From the MySQL 8.0.22 changelog:

Regular expression functions such as REGEXP_LIKE() yielded inconsistent results with binary string arguments. These functions now reject binary strings with an error. (Bug #98951, Bug #98950)

#10 @SergeyBiryukov
20 months ago

  • Keywords has-patch added; needs-patch removed
  • Milestone changed from Future Release to 6.1

51740.diff resolves the remaining failures in my testing.

Tested with MySQL 8.0.30 and MariaDB 10.6.8.

#11 @SergeyBiryukov
20 months ago

  • Owner set to SergeyBiryukov
  • Status changed from new to accepted

#12 @SergeyBiryukov
20 months ago

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

In 53901:

Query: Cast the meta key to BINARY for case-sensitive key comparisons in WP_Meta_Query.

This addresses an error on MySQL 8.0.22 or later:

Character set 'utf8mb4_unicode_520_ci' cannot be used in conjunction with 'binary' in call to regexp_like

From the MySQL 8.0.22 changelog:

Regular expression functions such as REGEXP_LIKE() yielded inconsistent results with binary string arguments. These functions now reject binary strings with an error. (Bug #98951, Bug #98950)

WordPress meta queries use the BINARY data type for case-sensitive meta key comparisons using regular expressions. By explicitly casting the meta key to BINARY, we can make sure the values being compared use the same character set and produce consistent results.

The change is covered by existing meta query unit tests: three tests which previously failed on MySQL 8.0.22 or later now pass.

References:

Follow-up to [46188].

Fixes #51740.

This ticket was mentioned in Slack in #core by sergey. View the logs.


20 months ago

This ticket was mentioned in Slack in #hosting-community by javier. View the logs.


20 months ago

This ticket was mentioned in Slack in #core by sergey. View the logs.


20 months ago

Note: See TracTickets for help on using tickets.