[wp-trac] [WordPress Trac] #49364: dbDelta() should not change display width for integer data types on MySQL 8.0.17+

[WordPress Trac]

#49364: dbDelta() should not change display width for integer data types on MySQL
8.0.17+
-------------------------------------+-----------------------------
 Reporter:  SergeyBiryukov           |       Owner:  (none)
     Type:  defect (bug)             |      Status:  new
 Priority:  normal                   |   Milestone:  Future Release
Component:  Database                 |     Version:
 Severity:  normal                   |  Resolution:
 Keywords:  has-patch needs-testing  |     Focuses:
-------------------------------------+-----------------------------

Comment (by 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
 <?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.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/49364#comment:5>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform