[wp-trac] [WordPress Trac] #34872: dbDelta Missing Index Name Creates Duplicate Indexes

[WordPress Trac]

#34872: dbDelta Missing Index Name Creates Duplicate Indexes
--------------------------------------+------------------------------
 Reporter:  charlestonsw              |       Owner:
     Type:  defect (bug)              |      Status:  new
 Priority:  normal                    |   Milestone:  Awaiting Review
Component:  Database                  |     Version:  3.5.1
 Severity:  normal                    |  Resolution:
 Keywords:  has-patch has-unit-tests  |     Focuses:  performance
--------------------------------------+------------------------------

Comment (by charlestonsw):

 As of 4.4.02 this is still an issue.


 Correct Syntax
 {{{
     /**
      * This is the approved WP 4.0 format.
      *
      * Double space after PRIMARY KEY
      *
      * Other indexes have KEY <keyname> (<key field>)
      *
      * Does NOT generate duplicate indexes.
      * Does NOT generate database errors (see test 004 below)
      */
     private function index_test_001() {
         global $wpdb;
         $table_name = $wpdb->prefix . 'dbdelta_test_001';
         $wpdb_collate = $wpdb->collate;
         $sql =
             "CREATE TABLE {$table_name} (
             id mediumint(8) unsigned NOT NULL auto_increment ,
             first varchar(255) NULL,
             PRIMARY KEY  (id),
             KEY first (first)
             )
             COLLATE {$wpdb_collate}";

         require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
         dbDelta( $sql );
         dbDelta( $sql );
     }
 }}}


 Test That Fails

 {{{
     /**
      * Skips index name on secondary index.
      *
      * AS OF WP 4.4.2 this produces TWO indexes named "first"
      */
     private function index_test_003() {
         global $wpdb;
         $table_name = $wpdb->prefix . 'dbdelta_test_003';
         $wpdb_collate = $wpdb->collate;
         $sql =
             "CREATE TABLE {$table_name} (
             id mediumint(8) unsigned NOT NULL auto_increment ,
             first varchar(255) NULL,
             PRIMARY KEY  (id),
             KEY (first)
             )
             COLLATE {$wpdb_collate}";

         require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
         dbDelta( $sql );
         dbDelta( $sql );
     }
 }}}



 Test That Works (using INDEX keyword) but generates WPDB duplicate key
 notice

 {{{
     /**
      * Uses INDEX instead of KEY on second index.
      *
      * AS OF WP 4.4.2 this generates and error in dbDelta
      * DOES NOT create a duplicate index
      * WordPress database error Duplicate key name 'first' for query ALTER
 TABLE wp_dbdelta_test_004 ADD INDEX first (first)
      */
     private function index_test_004() {
         global $wpdb;
         $table_name = $wpdb->prefix . 'dbdelta_test_004';
         $wpdb_collate = $wpdb->collate;
         $sql =
             "CREATE TABLE {$table_name} (
             id mediumint(8) unsigned NOT NULL auto_increment ,
             first varchar(255) NULL,
             PRIMARY KEY  (id),
             INDEX first (first)
             )
             COLLATE {$wpdb_collate}";

         require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
         dbDelta( $sql );
         dbDelta( $sql );
     }

 }}}

 I have these tests (and others) in a private plugin I've created named
 "db-delta-test".  I'll post on Github if anyone is interested.   Could be
 useful for doing phpUnit testing if someone wants to help me convert it to
 a proper phpUnit format.

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