WordPress.org

Make WordPress Core

Opened 18 months ago

Last modified 4 weeks ago

#49364 new defect (bug)

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

Reported by: SergeyBiryukov Owned by:
Milestone: 5.9 Priority: normal
Severity: normal Version:
Component: Database Keywords: has-patch needs-testing
Focuses: Cc:

Description

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.

Attachments (1)

49364.diff (12.9 KB) - added by SergeyBiryukov 11 months ago.

Download all attachments as: .zip

Change History (7)

#1 @SergeyBiryukov
18 months ago

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.

This ticket was mentioned in Slack in #core by sergey. View the logs.


11 months ago

#3 @SergeyBiryukov
11 months ago

  • Keywords has-patch needs-testing added

#5 @leewillis77
2 months ago

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.

#6 @desrosj
4 weeks ago

  • Milestone changed from Future Release to 5.9

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

Note: See TracTickets for help on using tickets.