WordPress.org

Make WordPress Core

Opened 3 years ago

Closed 2 years ago

Last modified 2 years ago

#31388 closed defect (bug) (fixed)

Database Error During Upgrade

Reported by: miqrogroove Owned by: dd32
Milestone: 4.2.3 Priority: normal
Severity: normal Version: 4.2
Component: Database Keywords:
Focuses: Cc:

Description

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

Change History (22)

#1 @miqrogroove
3 years ago

  • Severity changed from normal to blocker

#2 @boonebgorges
3 years ago

  • Milestone changed from Awaiting Review to 4.2

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.

#3 @pento
3 years ago

  • Component changed from Taxonomy to Upgrade/Install
  • Severity changed from blocker to normal

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.

This ticket was mentioned in Slack in #core by drew. View the logs.


3 years ago

#5 @pento
3 years ago

  • Milestone 4.2 deleted
  • Resolution set to wontfix
  • Status changed from new to closed
  • Version trunk deleted

#6 follow-up: @jstensved
3 years ago

  • Resolution wontfix deleted
  • Severity changed from normal to blocker
  • Status changed from closed to reopened

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

This ticket was mentioned in Slack in #core by miqrogroove. View the logs.


3 years ago

#8 @SergeyBiryukov
3 years ago

  • Milestone set to 4.2.3

#9 @miqrogroove
3 years ago

  • Version set to 4.2

#10 @lazab
3 years ago

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

#11 follow-up: @joeldfairbank
2 years ago

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.

#12 in reply to: ↑ 11 ; follow-ups: @jstensved
2 years ago

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.

#13 in reply to: ↑ 12 @joeldfairbank
2 years ago

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.

#14 in reply to: ↑ 12 @joeldfairbank
2 years ago

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.

#15 @Alex Fax
2 years ago

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

#16 @dd32
2 years ago

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

#17 @dd32
2 years ago

  • Component changed from Upgrade/Install to Database

#18 in reply to: ↑ 6 ; follow-up: @dd32
2 years ago

  • Severity changed from blocker to normal

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.

#19 @dd32
2 years ago

  • Owner set to dd32
  • Resolution set to fixed
  • Status changed from reopened to closed

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

#20 @dd32
2 years ago

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

#21 in reply to: ↑ 18 @jstensved
2 years ago

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!
Last edited 2 years ago by jstensved (previous) (diff)

#22 @dd32
2 years ago

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.

Note: See TracTickets for help on using tickets.