dbDelta() should not change display width for integer data types on MySQL 8.0.17+

[SergeyBiryukov]

Background: #49344

​MySQL 8.0.17 removed support for 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.

With this change, when creating a table, BIGINT(20) becomes just BIGINT, causing quite a few 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

Most of these failures will be addressed in #49344.

This ticket is for addressing the remaining failure caused by the same BIGINT(20)/BIGINT discrepancy coming from wp_get_db_schema():

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  

Currently, when running on MySQL 8.0.17+, dbDelta() tries to convert BIGINT back to BIGINT(20), INT back to INT(11), etc. This does not have any effect on the database, but is pointless and should not happen.

[SergeyBiryukov]

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.

[SergeyBiryukov]

Related: #51740, #52496.

[leewillis77]

The original ticket states:

Currently, when running on MySQL 8.0.17+, dbDelta() tries to convert BIGINT back to BIGINT(20), INT back to INT(11), etc. This does not have any effect on the database, but is pointless and should not happen.

However, this isn't always a silent failure. It can cause failures where an INT with a display width is also a primary key. Consider the plugin below:

<?php
/*
 * Plugin Name: dbDelta Display Width Issue
 * Description: dbDelta Display Width Issue
 * Version: 1.0.0
*/

if ( ! defined( 'ABSPATH' ) ) {
        exit; // Exit if accessed directly
}

function ddwi_install() {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        dbDelta( <<<SQL
CREATE TABLE `wp_ddwi_test` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
 )
SQL
        );
}

register_activation_hook( __FILE__, 'ddwi_install' );

The first activation will go without issues. However deactivating and re-activating the plugin attempts to run the following (unnecessary) alter statement:

ALTER TABLE wp_ddwi_test CHANGE COLUMN `id` `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY

This yields the following error:

WordPress database error Multiple primary key defined for query ALTER TABLE wp_ddwi_test CHANGE COLUMN `id` `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY

and gives an error on WP-CLI , or through online activation about excess output produced during activation.

[desrosj]

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

Add regx to dbDelta() function so any calls in the old form are handled

Trac ticket: https://core.trac.wordpress.org/ticket/49364

Add regex to dbDelta() function so any calls in the old form are handled
and included the corrected schema

Trac ticket: https://core.trac.wordpress.org/ticket/49364

[pbearne]

Hi All

I dug into this and checked the testing

It seems to me that we will have issues with current code/plugins still trying to use the old format
So I added a regx_replace to fix the SQL on the fly

Maybe we should look at adding a do_it_wrong notice but I didn't do that it would increase the overhead

The new patch is in pull request #1781 and the combined patch is in #1782

I hope this helps

Paul