Test failures using MySQL 8

[desrosj]

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.

[SergeyBiryukov]

Related to the first failure: #49364.

Previously: #44384, #49344.

[SergeyBiryukov]

Related: #52496

[desrosj]

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

[hellofromTonya]

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

[peterwilsoncc]

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

[SergeyBiryukov]

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:

• ​MySQL: Nymeric Type Attributes
• ​MariaDB: Numeric Data Types

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

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

[SergeyBiryukov]

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

[SergeyBiryukov]

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)

[SergeyBiryukov]

51740.diff​ resolves the remaining failures in my testing.

Tested with MySQL 8.0.30 and MariaDB 10.6.8.

[SergeyBiryukov]

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:

• ​MySQL 8.0.22 changelog
• ​Bug #104387 CHARACTER_SET_MISMATCH issue with regex comparisons

Follow-up to [46188].

Fixes #51740.