Make WordPress Core

Opened 6 years ago

Closed 4 years ago

#44586 closed defect (bug) (fixed)

SQL mode test results broken in MySQL 8.0.11

Reported by: jeremyfelt's profile jeremyfelt Owned by: sergeybiryukov's profile SergeyBiryukov
Milestone: 5.4 Priority: normal
Severity: normal Version:
Component: Database Keywords: needs-patch
Focuses: Cc:

Description

I have 2 failing tests related to SQL modes when using MySQL 8.0.11 locally:

1) Tests_DB::test_set_sql_mode
Failed asserting that two arrays are equal.
--- Expected
+++ Actual
@@ @@
 Array (
-    0 => 'IGNORE_SPACE'
-    1 => 'NO_AUTO_CREATE_USER'
+    0 => 'ERROR_FOR_DIVISION_BY_ZERO'
+    1 => 'NO_ENGINE_SUBSTITUTION'
+    2 => 'NO_ZERO_IN_DATE'

/tests/phpunit/includes/testcase.php:533
/tests/phpunit/tests/db.php:303

2) Tests_DB::test_set_allowed_incompatible_sql_mode
Failed asserting that an array contains 'ONLY_FULL_GROUP_BY'.

/tests/phpunit/tests/db.php:345

A 3rd test failure is documented as part of #44384.

Change History (4)

#1 @afragen
4 years ago

I can replicate this as well as an additional DB test failure. I am using MySQL 8.0.16 locally.

There were 3 failures:

1) Tests_DB::test_set_sql_mode
Failed asserting that two arrays are equal.
--- Expected
+++ Actual
@@ @@
 Array (
-    0 => 'IGNORE_SPACE'
-    1 => 'NO_AUTO_CREATE_USER'
+    0 => 'ERROR_FOR_DIVISION_BY_ZERO'
+    1 => 'NO_ENGINE_SUBSTITUTION'
+    2 => 'NO_ZERO_IN_DATE'
 )

.../tests/phpunit/includes/abstract-testcase.php:682
.../tests/phpunit/tests/db.php:303

2) Tests_DB::test_set_allowed_incompatible_sql_mode
Failed asserting that an array contains 'ONLY_FULL_GROUP_BY'.

.../tests/phpunit/tests/db.php:343

3) Tests_dbDelta::test_spatial_indices
Failed asserting that an array is empty.

.../tests/phpunit/tests/dbdelta.php:576

#2 @afragen
4 years ago

Related #44384

#3 @SergeyBiryukov
4 years ago

  • Milestone changed from Future Release to 5.4
  • Owner set to SergeyBiryukov
  • Status changed from new to reviewing

The failure in test_set_sql_mode() (also reported in #49344) 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.

See comment:3:ticket:49344 and the patch on #49344 for more details.

Last edited 4 years ago by SergeyBiryukov (previous) (diff)

#4 @SergeyBiryukov
4 years ago

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

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.

Note: See TracTickets for help on using tickets.