[wp-trac] [WordPress Trac] #31388: Database Error During Upgrade

[WordPress Trac]

#31388: Database Error During Upgrade
--------------------------+---------------------
 Reporter:  miqrogroove   |       Owner:  dd32
     Type:  defect (bug)  |      Status:  closed
 Priority:  normal        |   Milestone:  4.2.3
Component:  Database      |     Version:  4.2
 Severity:  normal        |  Resolution:  fixed
 Keywords:                |     Focuses:
--------------------------+---------------------

Comment (by jstensved):

 Replying to [comment:18 dd32]:
 > Replying to [comment:6 jstensved]:
 > > Current db_version is 29630 so when trying to upgrade line 2452 in
 upgrade.php below will first remove the index and five rows down run the
 DROP followed by ADD INDEX which breaks since the index was just removed.
 > >
 > > {{{
 > > if ( $wp_current_db_version < 30133 ) {
 > >             // dbDelta() can recreate but can't drop the index.
 > >             $wpdb->query( "ALTER TABLE $wpdb->terms DROP INDEX slug"
 );
 > > }
 > >
 > >     // Upgrade versions prior to 4.2.
 > >     if ( $wp_current_db_version < 31351 ) {
 > >             if ( ! is_multisite() ) {
 > >                     $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))" );
 > > }}}
 >
 >
 > Confirmed that this is the most likely cause of these notices. However,
 there's a few things to note
 >  * This is only shown when WP_DEBUG is true (and in server logs)
 >  * This isn't actually a problem, only a warning
 >  * This only affects when updating from 4.0 or earlier.
 >
 > The current operations are in the case where this warning is shown:
 >  * `$wpdb->query( "ALTER TABLE $wpdb->terms DROP INDEX slug" );` -
 Succeeds
 >  * `$wpdb->query( "ALTER TABLE $wpdb->terms DROP INDEX slug, ADD INDEX
 slug(slug(191))" );` - Fails with warning
 >  * `dbDelta()` creates `slug(slug(191))` - Succeeds
 >
 > When updating from 4.1 to 4.2+ the operations are:
 >  * `$wpdb->query( "ALTER TABLE $wpdb->terms DROP INDEX slug, ADD INDEX
 slug(slug(191))" );` - Succeeds
 >  * `dbDelta()` see's that `slug(slug(191))` already exists - nothing to
 do.
 >
 > The `30133` `DROP INDEX` case just needs removing. Reducing Severity
 because this isn't actually as bad as it first seems, and it doesn't block
 anything from proceeding.

 Thank your for great input. A quick note to "This isn't actually a
 problem, only a warning". It is actually an MySQL error, not a warning.

 Since the index is dropped and both DROP INDEX and ADD INDEX is issued in
 the same statement it is executed within a single transaction (MySql wraps
 all single statements in a transaction with autocommit=1 by default) which
 fails since the drop cannot be done on a non-existing index. Therefore the
 ADD is never executed and there is no index on the table.

 On a large install a missing index can indeed be blocking.

 Run the following sql to verify, no index is in place after the drop/add
 index command.

 [CREATE TABLE `wp_terms` (
   `term_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
   `slug` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
   `term_group` bigint(10) NOT NULL DEFAULT '0',
   PRIMARY KEY (`term_id`),
   -- KEY `slug` (`slug`(191)), removed for this test
   KEY `name` (`name`(191))
 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
 COLLATE=utf8mb4_unicode_ci;


 ALTER TABLE test DROP INDEX slug, ADD INDEX slug(slug(191)); -- <--
 FAILS!]

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