Database Error During Upgrade

[miqrogroove]

I used the Beta Tester plugin to update a test site from 4.0 to 4.2-alpha-31473. I was greeted with an error after the database upgrade step:

WordPress database error: [Can't DROP 'slug'; check that column/key exists]
ALTER TABLE wp_terms DROP INDEX slug, ADD INDEX slug(slug(191))

[boonebgorges]

Confirmed. This appears to be tied to [31349]. When upgrading a database with the unique index on the 'slug' column, the index is removed in pre_schema_upgrade(), with the intention of re-adding it during make_db_current_silent(). But something about the way dbDelta() interprets KEY slug (slug(191)) is making it try to re-drop the index before recreating it. Thus the error message.

The simplest solution would be to recreate the 'slug' index at the same time that it's dropped in pre_schema_upgrade() (it will then be dropped and readded again during make_db_current()). I'd like @pento to chime in first, though, to make sure that this ain't crazy talk.

[pento]

The index is dropped and re-added at the same time, in pre_schema_upgrade(), because dbDelta() doesn't have the ability to drop indexes, and I'd much prefer the drop/re-create to be a single query.

This error was caused because the slug index didn't exist before the upgrade started. Historically, we haven't checked for the existence of an index before dropping it.

I'm inclined to leave this ticket open for a bit during 4.2 beta to see if we receive any more reports of this error. If there's no-one else, it'll be closed as wontfix.

[jstensved]

I'd like to confirm that this is still an issue. The exact same error message appears when trying to upgrade a client site to 4.2 and blocks the whole upgrade process.

Can't DROP 'slug'; check that column/key exists

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))" );

[lazab]

Found this page from a little Google. The issue is still there.

[joeldfairbank]

My client's website is down due to this error:

WordPress database error Can't DROP 'slug'; check that column/key exists for query ALTER TABLE wp_terms DROP INDEX slug, ADD INDEX slug(slug(191)) made by wp_upgrade, pre_schema_upgrade

​http://www.btedj.com

What do i do to fix?

Thank you.

[jstensved]

This is not a support forum but recreating the index will allow WordPress to remove it and then recreate it:

TL;DR: Run "ALTER TABLE wp_terms ADD INDEX slug(slug(191))".

Replying to joeldfairbank:

My client's website is down due to this error:

WordPress database error Can't DROP 'slug'; check that column/key exists for query ALTER TABLE wp_terms DROP INDEX slug, ADD INDEX slug(slug(191)) made by wp_upgrade, pre_schema_upgrade

​http://www.btedj.com

What do i do to fix?

Thank you.

[joeldfairbank]

OK, sorry for asking here. I will seek a support forum because I don't understand what you are suggesting.

Replying to jstensved:

This is not a support forum but recreating the index will allow WordPress to remove it and then recreate it:

TL;DR: Run "ALTER TABLE wp_terms ADD INDEX slug(slug(191))".

Replying to joeldfairbank:

My client's website is down due to this error:

WordPress database error Can't DROP 'slug'; check that column/key exists for query ALTER TABLE wp_terms DROP INDEX slug, ADD INDEX slug(slug(191)) made by wp_upgrade, pre_schema_upgrade

​http://www.btedj.com

What do i do to fix?

Thank you.

[joeldfairbank]

Actually I figured out how to do an ALTER TABLE in SQL but it didn't fix things. Thanks anyway!

Replying to jstensved:

This is not a support forum but recreating the index will allow WordPress to remove it and then recreate it:

TL;DR: Run "ALTER TABLE wp_terms ADD INDEX slug(slug(191))".

Replying to joeldfairbank:

My client's website is down due to this error:

WordPress database error Can't DROP 'slug'; check that column/key exists for query ALTER TABLE wp_terms DROP INDEX slug, ADD INDEX slug(slug(191)) made by wp_upgrade, pre_schema_upgrade

​http://www.btedj.com

What do i do to fix?

Thank you.

[Alex Fax]

when upgrade to version 4.2.2 WP I was confronted with the same error

Can't DROP 'slug'; check that column/key exists ALTER TABLE wp_terms DROP INDEX slug, ADD INDEX slug(slug(191))

[dd32]

If anyone still has a table without the slug index, I'd be interested in seeing what indexes it actually does have defined.

[dd32]

Replying to 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.

[dd32]

In 32852:

Remove a redundant index drop, it'll be dropped and re-created a few lines further down.
Fixes a warning during updating from 4.0 or earlier.
Fixes #31388 for trunk

[dd32]

In 32853:

Remove a redundant index drop, it'll be dropped and re-created a few lines further down.
Fixes a warning during updating from 4.0 or earlier.
Merges [32852] to the 4.2 branch. Fixes #31388 for 4.2

[jstensved]

Replying to dd32:

Replying to 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 wp_terms DROP INDEX slug, ADD INDEX slug(slug(191)); -- <-- FAILS!

[dd32]

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.

@jstensved Correct :) It's a MySQL Error, but only a warning in the context of the upgrade routine (Bad choice of words really on my behalf there). The reason I explained it as a warning only, was that in this scenario, as mentioned above, that specific query failing has no impact on the site (As the order of queries is effectively DROP INDEX, DROP INDEX + ADD INDEX, ADD INDEX).

So, as a result of that failed query, no site would've been left without an index (unless the index creation query failed), as any site that hit this error, had already dropped the index, and would've been on it's way to re-create it shortly thereafter in the upgrade routine.