Changeset 37938

Timestamp:

07/01/2016 11:41:57 AM (10 years ago)

Author:

pento

Message:

Database: dbDelta() will no longer try to downgrade the size of TEXT and BLOB columns.

When upgrading to utf8mb4, TEXT fields will be upgraded to MEDIUMTEXT (and likewise for all other *TEXT and *BLOB fields). This is to allow for the additional space requirements of utf8mb4.

On the subsequent upgrade, dbDelta() would try and downgrade the fields to their original size again. At best, this it a waste of time, at worst, this could truncate any data larger than the original size. There's no harm in leaving them at their new size, so let's do that.

The FULLTEXT indexes are removed from the tests, as dbDelta()'s FULLTEXT support was added in WordPress 4.4.

This also fixes a typo in the dbDelta() tests.

Merge of [37525] to the 4.3 branch.
Partial merge of [36552] to the 4.3 branch.

See #36748.

Location:

branches/4.3

Files:

• src/wp-admin/includes/upgrade.php (modified) (2 diffs)
• tests/phpunit/tests/dbdelta.php (modified) (7 diffs)

branches/4.3/src/wp-admin/includes/upgrade.php

@@ -2063 +2063 @@
         $iqueries = apply_filters( 'dbdelta_insert_queries', $iqueries );
 
+        $text_fields = array( 'tinytext', 'text', 'mediumtext', 'longtext' );
+        $blob_fields = array( 'tinyblob', 'blob', 'mediumblob', 'longblob' );
+
         $global_tables = $wpdb->tables( 'global' );
         foreach ( $cqueries as $table => $qry ) {
@@ -2132 +2135 @@
                                 // Is actual field type different from the field type in query?
                                 if ($tablefield->Type != $fieldtype) {
+                                        $do_change = true;
+                                        if ( in_array( strtolower( $fieldtype ), $text_fields ) && in_array( strtolower( $tablefield->Type ), $text_fields ) ) {
+                                                if ( array_search( strtolower( $fieldtype ), $text_fields ) < array_search( strtolower( $tablefield->Type ), $text_fields ) ) {
+                                                        $do_change = false;
+                                                }
+                                        }
+
+                                        if ( in_array( strtolower( $fieldtype ), $blob_fields ) && in_array( strtolower( $tablefield->Type ), $blob_fields ) ) {
+                                                if ( array_search( strtolower( $fieldtype ), $blob_fields ) < array_search( strtolower( $tablefield->Type ), $blob_fields ) ) {
+                                                        $do_change = false;
+                                                }
+                                        }
+
+                                        if ( $do_change ) {
                                         // Add a query to change the column type
-                                        $cqueries[] = "ALTER TABLE {$table} CHANGE COLUMN {$tablefield->Field} " . $cfields[strtolower($tablefield->Field)];
-                                        $for_update[$table.'.'.$tablefield->Field] = "Changed type of {$table}.{$tablefield->Field} from {$tablefield->Type} to {$fieldtype}";
+                                                $cqueries[] = "ALTER TABLE {$table} CHANGE COLUMN {$tablefield->Field} " . $cfields[strtolower($tablefield->Field)];
+                                                $for_update[$table.'.'.$tablefield->Field] = "Changed type of {$table}.{$tablefield->Field} from {$tablefield->Type} to {$fieldtype}";
+                                        }
                                 }
 

branches/4.3/tests/phpunit/tests/dbdelta.php

@@ -31 +31 @@
                                 id bigint(20) NOT NULL AUTO_INCREMENT,
                                 column_1 varchar(255) NOT NULL,
-                                PRIMARY KEY  (id),
-                                KEY key_1 (column_1),
-                                KEY compoud_key (id,column_1)
+                                column_2 text,
+                                column_3 blob,
+                                PRIMARY KEY  (id),
+                                KEY key_1 (column_1),
+                                KEY compound_key (id,column_1)
                         )
                         "
@@ -104 +106 @@
                                 PRIMARY KEY  (id),
                                 KEY key_1 (column_1),
-                                KEY compoud_key (id,column_1)
+                                KEY compound_key (id,column_1)
                         )
                         "
@@ -127 +129 @@
                                 PRIMARY KEY  (id),
                                 KEY key_1 (column_1),
-                                KEY compoud_key (id,column_1)
+                                KEY compound_key (id,column_1)
                         )
                         "
@@ -156 +158 @@
                                 PRIMARY KEY  (id),
                                 KEY key_1 (column_1),
-                                KEY compoud_key (id,column_1)
+                                KEY compound_key (id,column_1)
                         )
                         "
@@ -188 +190 @@
                                 PRIMARY KEY  (id),
                                 KEY key_1 (column_1),
-                                KEY compoud_key (id,column_1)
+                                KEY compound_key (id,column_1)
                         )
                         "
@@ -214 +216 @@
                                 PRIMARY KEY  (id),
                                 KEY key_1 (column_1),
-                                KEY compoud_key (id,column_1)
+                                KEY compound_key (id,column_1)
                         )
                         "
@@ -323 +325 @@
                 $this->assertSame( array(), $actual );
         }
+
+        /**
+         * @ticket 36748
+         */
+        function test_dont_downsize_text_fields() {
+                global $wpdb;
+
+                $result = dbDelta(
+                        "
+                        CREATE TABLE {$wpdb->prefix}dbdelta_test (
+                                id bigint(20) NOT NULL AUTO_INCREMENT,
+                                column_1 varchar(255) NOT NULL,
+                                column_2 tinytext,
+                                column_3 blob,
+                                PRIMARY KEY  (id),
+                                KEY key_1 (column_1),
+                                KEY compound_key (id,column_1)
+                        ) ENGINE=MyISAM
+                        ", false );
+
+                $this->assertSame( array(), $result );
+        }
+
+        /**
+         * @ticket 36748
+         */
+        function test_dont_downsize_blob_fields() {
+                global $wpdb;
+
+                $result = 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 tinyblob,
+                                PRIMARY KEY  (id),
+                                KEY key_1 (column_1),
+                                KEY compound_key (id,column_1)
+                        ) ENGINE=MyISAM
+                        ", false );
+
+                $this->assertSame( array(), $result );
+        }
+
+        /**
+         * @ticket 36748
+         */
+        function test_upsize_text_fields() {
+                global $wpdb;
+
+                $result = dbDelta(
+                        "
+                        CREATE TABLE {$wpdb->prefix}dbdelta_test (
+                                id bigint(20) NOT NULL AUTO_INCREMENT,
+                                column_1 varchar(255) NOT NULL,
+                                column_2 bigtext,
+                                column_3 blob,
+                                PRIMARY KEY  (id),
+                                KEY key_1 (column_1),
+                                KEY compound_key (id,column_1)
+                        ) ENGINE=MyISAM
+                        ", false );
+
+                $this->assertSame(
+                        array(
+                                "{$wpdb->prefix}dbdelta_test.column_2"
+                                        => "Changed type of {$wpdb->prefix}dbdelta_test.column_2 from text to bigtext"
+                        ), $result );
+        }
+
+        /**
+         * @ticket 36748
+         */
+        function test_upsize_blob_fields() {
+                global $wpdb;
+
+                $result = 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 mediumblob,
+                                PRIMARY KEY  (id),
+                                KEY key_1 (column_1),
+                                KEY compound_key (id,column_1)
+                        ) ENGINE=MyISAM
+                        ", false );
+
+                $this->assertSame(
+                        array(
+                                "{$wpdb->prefix}dbdelta_test.column_3"
+                                        => "Changed type of {$wpdb->prefix}dbdelta_test.column_3 from blob to mediumblob"
+                        ), $result );
+        }
 }