Changeset 39921

Timestamp:

01/17/2017 04:00:09 AM (8 years ago)

Author:

pento

Message:

dbDelta: Ignore index subparts when checking for duplicate indices.

If index lengths change in table definitions, we don't recreate the index - instead, we throw a database error, as dbDelta() tries to create a new index with the same name.

It's better to leave the index as is, MySQL doesn't have an efficient process for resizing indices, and dropping/creating is a slow process which we don't want to trigger automatically.

Fixes #34870.

Location:

trunk

Files:

• src/wp-admin/includes/upgrade.php (modified) (5 diffs)
• tests/phpunit/tests/dbdelta.php (modified) (1 diff)

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

@@ -2217 +2217 @@
 
         // Clear the field and index arrays.
-        $cfields = $indices = array();
+        $cfields = $indices = $indices_without_subparts = array();
 
         // Get all of the field names in the query from between the parentheses.
@@ -2290 +2290 @@
 
                     // Parse the columns. Multiple columns are separated by a comma.
-                    $index_columns = array_map( 'trim', explode( ',', $index_matches['index_columns'] ) );
+                    $index_columns = $index_columns_without_subparts = array_map( 'trim', explode( ',', $index_matches['index_columns'] ) );
 
                     // Normalize columns.
-                    foreach ( $index_columns as &$index_column ) {
+                    foreach ( $index_columns as $id => &$index_column ) {
                         // Extract column name and number of indexed characters (sub_part).
                         preg_match(
@@ -2320 +2320 @@
                         $index_column = '`' . $index_column_matches['column_name'] . '`';
 
+                        // We don't need to add the subpart to $index_columns_without_subparts
+                        $index_columns_without_subparts[ $id ] = $index_column;
+
                         // Append the optional sup part with the number of indexed characters.
                         if ( isset( $index_column_matches['sub_part'] ) ) {
@@ -2328 +2331 @@
                     // Build the normalized index definition and add it to the list of indices.
                     $indices[] = "{$index_type} {$index_name} (" . implode( ',', $index_columns ) . ")";
+                    $indices_without_subparts[] = "{$index_type} {$index_name} (" . implode( ',', $index_columns_without_subparts ) . ")";
 
                     // Destroy no longer needed variables.
-                    unset( $index_column, $index_column_matches, $index_matches, $index_type, $index_name, $index_columns );
+                    unset( $index_column, $index_column_matches, $index_matches, $index_type, $index_name, $index_columns, $index_columns_without_subparts );
 
                     break;
@@ -2447 +2451 @@
                     // Add the field to the column list string.
                     $index_columns .= '`' . $column_data['fieldname'] . '`';
-                    if ($column_data['subpart'] != '') {
-                        $index_columns .= '('.$column_data['subpart'].')';
-                    }
                 }
 
-                // The alternative index string doesn't care about subparts
-                $alt_index_columns = preg_replace( '/\([^)]*\)/', '', $index_columns );
-
                 // Add the column list to the index create string.
-                $index_strings = array(
-                    "$index_string ($index_columns)",
-                    "$index_string ($alt_index_columns)",
-                );
-
-                foreach ( $index_strings as $index_string ) {
-                    if ( ! ( ( $aindex = array_search( $index_string, $indices ) ) === false ) ) {
-                        unset( $indices[ $aindex ] );
-                        break;
-                    }
+                $index_string .= " ($index_columns)";
+
+                // Check if the index definition exists, ignoring subparts.
+                if ( ! ( ( $aindex = array_search( $index_string, $indices_without_subparts ) ) === false ) ) {
+                    // If the index already exists (even with different subparts), we don't need to create it.
+                    unset( $indices_without_subparts[ $aindex ] );
+                    unset( $indices[ $aindex ] );
                 }
             }

trunk/tests/phpunit/tests/dbdelta.php

@@ -827 +827 @@
 
     /**
+     * @ticket 34870
+     */
+    function test_unchanged_key_lengths_do_not_recreate_index() {
+        global $wpdb;
+
+        $updates = dbDelta(
+            "
+            CREATE TABLE {$wpdb->prefix}dbdelta_test (
+                id bigint(20) NOT NULL AUTO_INCREMENT,
+                column_1 varchar(255) NOT NULL,
+                column_2 text,
+                column_3 blob,
+                PRIMARY KEY  (id),
+                KEY key_1 (column_1(255)),
+                KEY compound_key (id,column_1),
+                FULLTEXT KEY fulltext_key (column_1)
+            ) ENGINE=MyISAM
+            ", false );
+
+        $this->assertEmpty( $updates );
+    }
+
+    /**
+     * @ticket 34870
+     */
+    function test_changed_key_lengths_do_not_recreate_index() {
+        global $wpdb;
+
+        $updates = dbDelta(
+            "
+            CREATE TABLE {$wpdb->prefix}dbdelta_test (
+                id bigint(20) NOT NULL AUTO_INCREMENT,
+                column_1 varchar(255) NOT NULL,
+                column_2 text,
+                column_3 blob,
+                PRIMARY KEY  (id),
+                KEY key_1 (column_1),
+                KEY compound_key (id,column_1),
+                KEY changing_key_length (column_1(20)),
+                FULLTEXT KEY fulltext_key (column_1)
+            ) ENGINE=MyISAM
+            " );
+
+        $this->assertSame( array(
+            "Added index {$wpdb->prefix}dbdelta_test KEY `changing_key_length` (`column_1`(20))"
+        ), $updates );
+
+        $updates = dbDelta(
+            "
+            CREATE TABLE {$wpdb->prefix}dbdelta_test (
+                id bigint(20) NOT NULL AUTO_INCREMENT,
+                column_1 varchar(255) NOT NULL,
+                column_2 text,
+                column_3 blob,
+                PRIMARY KEY  (id),
+                KEY key_1 (column_1),
+                KEY compound_key (id,column_1),
+                KEY changing_key_length (column_1(50)),
+                FULLTEXT KEY fulltext_key (column_1)
+            ) ENGINE=MyISAM
+            " );
+
+        $this->assertEmpty( $updates );
+
+        $updates = dbDelta(
+            "
+            CREATE TABLE {$wpdb->prefix}dbdelta_test (
+                id bigint(20) NOT NULL AUTO_INCREMENT,
+                column_1 varchar(255) NOT NULL,
+                column_2 text,
+                column_3 blob,
+                PRIMARY KEY  (id),
+                KEY key_1 (column_1),
+                KEY compound_key (id,column_1),
+                KEY changing_key_length (column_1(1)),
+                FULLTEXT KEY fulltext_key (column_1)
+            ) ENGINE=MyISAM
+            " );
+
+        $this->assertEmpty( $updates );
+
+        $updates = dbDelta(
+            "
+            CREATE TABLE {$wpdb->prefix}dbdelta_test (
+                id bigint(20) NOT NULL AUTO_INCREMENT,
+                column_1 varchar(255) NOT NULL,
+                column_2 text,
+                column_3 blob,
+                PRIMARY KEY  (id),
+                KEY key_1 (column_1),
+                KEY compound_key (id,column_1),
+                KEY changing_key_length (column_1),
+                FULLTEXT KEY fulltext_key (column_1)
+            ) ENGINE=MyISAM
+            " );
+
+        $this->assertEmpty( $updates );
+    }
+
+    /**
      * @ticket 31679
      */