Changeset 53897 for trunk/src/wp-admin/includes/upgrade.php

Timestamp:

08/15/2022 01:16:22 PM (2 years ago)

Author:

SergeyBiryukov

Message:

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.

File:

• trunk/src/wp-admin/includes/upgrade.php (modified) (5 diffs)

trunk/src/wp-admin/includes/upgrade.php

@@ -2714 +2714 @@
  *
  * @since 1.5.0
+ * @since 6.1.0 Ignores display width for integer data types on MySQL 8.0.17 or later,
+ *              to match MySQL behavior. Note: This does not affect MariaDB.
  *
  * @global wpdb $wpdb WordPress database abstraction object.
@@ -2790 +2792 @@
     $text_fields = array( 'tinytext', 'text', 'mediumtext', 'longtext' );
     $blob_fields = array( 'tinyblob', 'blob', 'mediumblob', 'longblob' );
-
-    $global_tables = $wpdb->tables( 'global' );
+    $int_fields  = array( 'tinyint', 'smallint', 'mediumint', 'int', 'integer', 'bigint' );
+
+    $global_tables  = $wpdb->tables( 'global' );
+    $db_version     = $wpdb->db_version();
+    $db_server_info = $wpdb->db_server_info();
+
     foreach ( $cqueries as $table => $qry ) {
         // Upgrade global tables only for the main site. Don't upgrade at all if conditions are not optimal.
@@ -2949 +2955 @@
             $tablefield_type_lowercased  = strtolower( $tablefield->Type );
 
+            $tablefield_type_without_parentheses = preg_replace(
+                '/'
+                . '(.+)'       // Field type, e.g. `int`.
+                . '\(\d*\)'    // Display width.
+                . '(.*)'       // Optional attributes, e.g. `unsigned`.
+                . '/',
+                '$1$2',
+                $tablefield_type_lowercased
+            );
+
+            // Get the type without attributes, e.g. `int`.
+            $tablefield_type_base = strtok( $tablefield_type_without_parentheses, ' ' );
+
             // If the table field exists in the field array...
             if ( array_key_exists( $tablefield_field_lowercased, $cfields ) ) {
@@ -2956 +2975 @@
                 $fieldtype            = $matches[1];
                 $fieldtype_lowercased = strtolower( $fieldtype );
+
+                $fieldtype_without_parentheses = preg_replace(
+                    '/'
+                    . '(.+)'       // Field type, e.g. `int`.
+                    . '\(\d*\)'    // Display width.
+                    . '(.*)'       // Optional attributes, e.g. `unsigned`.
+                    . '/',
+                    '$1$2',
+                    $fieldtype_lowercased
+                );
+
+                // Get the type without attributes, e.g. `int`.
+                $fieldtype_base = strtok( $fieldtype_without_parentheses, ' ' );
 
                 // Is actual field type different from the field type in query?
@@ -2968 +3000 @@
                     if ( in_array( $fieldtype_lowercased, $blob_fields, true ) && in_array( $tablefield_type_lowercased, $blob_fields, true ) ) {
                         if ( array_search( $fieldtype_lowercased, $blob_fields, true ) < array_search( $tablefield_type_lowercased, $blob_fields, true ) ) {
+                            $do_change = false;
+                        }
+                    }
+
+                    if ( in_array( $fieldtype_base, $int_fields, true ) && in_array( $tablefield_type_base, $int_fields, true )
+                        && $fieldtype_without_parentheses === $tablefield_type_without_parentheses
+                    ) {
+                        /*
+                         * MySQL 8.0.17 or later does not support display width for integer data types,
+                         * so if display width is the only difference, it can be safely ignored.
+                         * Note: This is specific to MySQL and does not affect MariaDB.
+                         */
+                        if ( version_compare( $db_version, '8.0.17', '>=' )
+                            && ! str_contains( $db_server_info, 'MariaDB' )
+                        ) {
                             $do_change = false;
                         }