Changeset 31349

Timestamp:

02/06/2015 04:50:19 AM (10 years ago)

Author:

pento

Message:

WPDB: If a site is using the utf8 charset, and their version of MySQL supports utf8mb4, auto-upgrade them to utf8mb4.

This patch also resizes some indexes, to allow for the 767 byte index size limit in standard MySQL installs.

See #21212

Location:

trunk

Files:

• src/wp-admin/includes/schema.php (modified) (9 diffs)
• src/wp-admin/includes/upgrade.php (modified) (5 diffs)
• src/wp-admin/setup-config.php (modified) (1 diff)
• src/wp-includes/version.php (modified) (1 diff)
• src/wp-includes/wp-db.php (modified) (8 diffs)
• tests/phpunit/tests/db/charset.php (modified) (2 diffs)

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

@@ -44 +44 @@
     // Engage multisite if in the middle of turning it on from network.php.
     $is_multisite = is_multisite() || ( defined( 'WP_INSTALLING_NETWORK' ) && WP_INSTALLING_NETWORK );
+
+    /*
+     * Indexes have a maximum size of 767 bytes. Historically, we haven't need to be concerned about that.
+     * As of 4.2, however, we moved to utf8mb4, which uses 4 bytes per character. This means that an index which
+     * used to have room for floor(767/3) = 255 characters, now only has room for floor(767/4) = 191 characters.
+     */
+    $max_index_length = 191;
 
     // Blog specific tables.
@@ -52 +59 @@
  term_group bigint(10) NOT NULL default 0,
  PRIMARY KEY  (term_id),
- KEY slug (slug),
- KEY name (name)
+ KEY slug (slug($max_index_length)),
+ KEY name (name($max_index_length))
 ) $charset_collate;
 CREATE TABLE $wpdb->term_taxonomy (
@@ -80 +87 @@
   PRIMARY KEY  (meta_id),
   KEY comment_id (comment_id),
-  KEY meta_key (meta_key)
+  KEY meta_key (meta_key($max_index_length))
 ) $charset_collate;
 CREATE TABLE $wpdb->comments (
@@ -137 +144 @@
   PRIMARY KEY  (meta_id),
   KEY post_id (post_id),
-  KEY meta_key (meta_key)
+  KEY meta_key (meta_key($max_index_length))
 ) $charset_collate;
 CREATE TABLE $wpdb->posts (
@@ -164 +171 @@
   comment_count bigint(20) NOT NULL default '0',
   PRIMARY KEY  (ID),
-  KEY post_name (post_name),
+  KEY post_name (post_name($max_index_length)),
   KEY type_status_date (post_type,post_status,post_date,ID),
   KEY post_parent (post_parent),
@@ -214 +221 @@
   PRIMARY KEY  (umeta_id),
   KEY user_id (user_id),
-  KEY meta_key (meta_key)
+  KEY meta_key (meta_key($max_index_length))
 ) $charset_collate;\n";
 
@@ -262 +269 @@
   path varchar(100) NOT NULL default '',
   PRIMARY KEY  (id),
-  KEY domain (domain,path)
+  KEY domain (domain(140),path(51))
 ) $charset_collate;
 CREATE TABLE $wpdb->sitemeta (
@@ -270 +277 @@
   meta_value longtext,
   PRIMARY KEY  (meta_id),
-  KEY meta_key (meta_key),
+  KEY meta_key (meta_key($max_index_length)),
   KEY site_id (site_id)
 ) $charset_collate;
@@ -289 +296 @@
   KEY user_email (user_email),
   KEY user_login_email (user_login,user_email),
-  KEY domain_path (domain,path)
+  KEY domain_path (domain(140),path(51))
 ) $charset_collate;";
 

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

@@ -519 +519 @@
     if ( $wp_current_db_version < 29630 )
         upgrade_400();
+
+    if ( $wp_current_db_version < 31349 )
+        upgrade_420();
 
     maybe_disable_link_manager();
@@ -1408 +1411 @@
 
 /**
+ * Execute changes made in WordPress 4.2.0.
+ *
+ * @since 4.2.0
+ */
+function upgrade_420() {
+    global $wp_current_db_version, $wpdb;
+
+    if ( $wp_current_db_version < 31349 && $wpdb->charset === 'utf8mb4' ) {
+        if ( is_multisite() ) {
+            $tables = $wpdb->tables( 'blog' );
+        } else {
+            $tables = $wpdb->tables( 'all' );
+        }
+
+        foreach ( $tables as $table ) {
+            maybe_convert_table_to_utf8mb4( $table );
+        }
+    }
+}
+
+/**
  * Executes network-level upgrade routines.
  *
@@ -1503 +1527 @@
         }
     }
+
+    // 4.2
+    if ( $wp_current_db_version < 31349 && $wpdb->charset === 'utf8mb4' ) {
+        if ( ! ( defined( 'DO_NOT_UPGRADE_GLOBAL_TABLES' ) && DO_NOT_UPGRADE_GLOBAL_TABLES ) ) {
+            $wpdb->query( "ALTER TABLE $wpdb->site DROP INDEX domain, ADD INDEX domain(domain(140),path(51))" );
+            $wpdb->query( "ALTER TABLE $wpdb->sitemeta DROP INDEX meta_key, ADD INDEX meta_key(meta_key(191))" );
+            $wpdb->query( "ALTER TABLE $wpdb->signups DROP INDEX domain, ADD INDEX domain(domain(140),path(51))" );
+
+            $tables = $wpdb->tables( 'global' );
+
+            foreach ( $tables as $table ) {
+                maybe_convert_table_to_utf8mb4( $table );
+            }
+        }
+    }
 }
 
@@ -1606 +1645 @@
     }
     return false;
+}
+
+/**
+ * If a table only contains utf8 or utf8mb4 columns, convert it to utf8mb4.
+ *
+ * @since 4.2.0
+ *
+ * @param string $table The table to convert.
+ * @return bool true if the table was converted, false if it wasn't.
+ */
+function maybe_convert_table_to_utf8mb4( $table ) {
+    global $wpdb;
+
+    $results = $wpdb->get_results( "SHOW FULL COLUMNS FROM `$table`" );
+    if ( ! $results ) {
+        return false;
+    }
+
+    $has_utf8 = false;
+    foreach ( $results as $column ) {
+        if ( $column->Collation ) {
+            if ( 'utf8' === $column->Collation ) {
+                $has_utf8 = true;
+            } elseif ( 'utf8mb4' !== $column->Collation ) {
+                // Don't upgrade tables that have non-utf8 columns.
+                return false;
+            }
+        }
+    }
+
+    if ( ! $has_utf8 ) {
+        // Don't bother upgrading tables that don't have utf8 columns.
+        return false;
+    }
+
+    return $wpdb->query( "ALTER TABLE $table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" );
 }
 
@@ -2285 +2360 @@
         $wpdb->query( "ALTER TABLE $wpdb->terms DROP INDEX slug" );
     }
+
+    // Upgrade versions prior to 4.2.
+    if ( $wp_current_db_version < 31349 ) {
+        // So that we can change tables to utf8mb4, we need to shorten the index lengths to less than 767 bytes
+        $wpdb->query( "ALTER TABLE $wpdb->usermeta DROP INDEX meta_key, ADD INDEX meta_key(meta_key(191))" );
+        $wpdb->query( "ALTER TABLE $wpdb->terms DROP INDEX slug, ADD INDEX slug(slug(191))" );
+        $wpdb->query( "ALTER TABLE $wpdb->terms DROP INDEX name, ADD INDEX name(name(191))" );
+        $wpdb->query( "ALTER TABLE $wpdb->commentmeta DROP INDEX meta_key, ADD INDEX meta_key(meta_key(191))" );
+        $wpdb->query( "ALTER TABLE $wpdb->postmeta DROP INDEX meta_key, ADD INDEX meta_key(meta_key(191))" );
+        $wpdb->query( "ALTER TABLE $wpdb->posts DROP INDEX post_name, ADD INDEX post_name(post_name(191))" );
+    }
 }
 

trunk/src/wp-admin/setup-config.php

@@ -281 +281 @@
                 $config_file[ $line_num ] = "define('" . $constant . "'," . $padding . "'" . addcslashes( constant( $constant ), "\\'" ) . "');\r\n";
                 break;
+            case 'DB_CHARSET'  :
+                if ( 'utf8mb4' === $wpdb->charset || ( ! $wpdb->charset && $wpdb->has_cap( 'utf8mb4' ) ) ) {
+                    $config_file[ $line_num ] = "define('" . $constant . "'," . $padding . "'utf8mb4');\r\n";
+                }
+                break;
             case 'AUTH_KEY'         :
             case 'SECURE_AUTH_KEY'  :

trunk/src/wp-includes/version.php

@@ -12 +12 @@
  * @global int $wp_db_version
  */
-$wp_db_version = 30133;
+$wp_db_version = 31349;
 
 /**

trunk/src/wp-includes/wp-db.php

@@ -625 +625 @@
         }
 
-        $this->init_charset();
-
         $this->dbuser = $dbuser;
         $this->dbpassword = $dbpassword;
@@ -718 +716 @@
         if ( function_exists('is_multisite') && is_multisite() ) {
             $this->charset = 'utf8';
-            if ( defined( 'DB_COLLATE' ) && DB_COLLATE )
+            if ( defined( 'DB_COLLATE' ) && DB_COLLATE ) {
                 $this->collate = DB_COLLATE;
-            else
+            } else {
                 $this->collate = 'utf8_general_ci';
+            }
         } elseif ( defined( 'DB_COLLATE' ) ) {
             $this->collate = DB_COLLATE;
         }
 
-        if ( defined( 'DB_CHARSET' ) )
+        if ( defined( 'DB_CHARSET' ) ) {
             $this->charset = DB_CHARSET;
+        }
+
+        if ( ( $this->use_mysqli && ! ( $this->dbh instanceof mysqli ) )
+          || ( empty( $this->dbh ) || ! ( $this->dbh instanceof mysqli ) ) ) {
+            return;
+        }
+
+        if ( 'utf8' === $this->charset && $this->has_cap( 'utf8mb4' ) ) {
+            $this->charset = 'utf8mb4';
+        }
+
+        if ( 'utf8mb4' === $this->charset && ( ! $this->collate || stripos( $this->collate, 'utf8_' ) === 0 ) ) {
+            $this->collate = 'utf8mb4_unicode_ci';
+        }
     }
 
@@ -1477 +1490 @@
             return false;
         } elseif ( $this->dbh ) {
+            if ( ! $this->has_connected ) {
+                $this->init_charset();
+            }
+
             $this->has_connected = true;
+
             $this->set_charset( $this->dbh );
+
             $this->ready = true;
             $this->set_sql_mode();
@@ -2250 +2269 @@
      *
      * @since 4.2.0
-     * @access protected
+     * @access public
      *
      * @param string $table  Table name.
@@ -2257 +2276 @@
      *               character set. {@see WP_Error} object if there was an error.
      */
-    protected function get_col_charset( $table, $column ) {
+    public function get_col_charset( $table, $column ) {
         $tablekey = strtolower( $table );
         $columnkey = strtolower( $column );
@@ -2357 +2376 @@
             'ujis'    => 'EUC-JP',
             'utf32'   => 'UTF-32',
-            'utf8mb4' => 'UTF-8',
         );
 
@@ -2392 +2410 @@
             }
 
-            // utf8(mb3) can be handled by regex, which is a bunch faster than a DB lookup.
-            if ( 'utf8' === $charset || 'utf8mb3' === $charset ) {
+            // utf8 can be handled by regex, which is a bunch faster than a DB lookup.
+            if ( 'utf8' === $charset || 'utf8mb3' === $charset || 'utf8mb4' === $charset ) {
                 $regex = '/
                     (
@@ -2401 +2419 @@
                         |   [\xE1-\xEC][\x80-\xBF]{2}
                         |   \xED[\x80-\x9F][\x80-\xBF]
-                        |   [\xEE-\xEF][\x80-\xBF]{2}
-                        ){1,50}                          # ...one or more times
+                        |   [\xEE-\xEF][\x80-\xBF]{2}';
+
+                if ( 'utf8mb4' === $charset) {
+                    $regex .= '
+                        |    \xF0[\x90-\xBF][\x80-\xBF]{2} # four-byte sequences   11110xxx 10xxxxxx * 3
+                        |    [\xF1-\xF3][\x80-\xBF]{3}
+                        |    \xF4[\x80-\x8F][\x80-\xBF]{2}
+                    ';
+                }
+
+                $regex .= '){1,50}                          # ...one or more times
                     )
                     | .                                  # anything else

trunk/tests/phpunit/tests/db/charset.php

@@ -131 +131 @@
 
     /**
-     * @ ticket 21212
+     * @ticket 21212
      */
     function test_process_fields_failure() {
         global $wpdb;
-        $data = array( 'post_content' => "H€llo\xf0\x9f\x98\x88World¢" );
+        // \xf0\xff\xff\xff is invalid in utf8 and utf8mb4.
+        $data = array( 'post_content' => "H€llo\xf0\xff\xff\xffWorld¢" );
         $this->assertFalse( self::$_wpdb->process_fields( $wpdb->posts, $data, null ) );
     }
@@ -437 +438 @@
     function test_invalid_characters_in_query() {
         global $wpdb;
-        $this->assertFalse( $wpdb->query( "INSERT INTO {$wpdb->posts} (post_content) VALUES ('foo\xf0\x9f\x98\x88bar')" ) );
+        $this->assertFalse( $wpdb->query( "INSERT INTO {$wpdb->posts} (post_content) VALUES ('foo\xf0\xff\xff\xffbar')" ) );
     }
 }