SQL mode test results broken in MySQL 8.0.11

[jeremyfelt]

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.

[afragen]

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

[afragen]

Related #44384

[SergeyBiryukov]

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.

[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.