#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)
#3
@
10 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.
10 years ago
#5
@
10 years ago
- Milestone 4.2 deleted
- Resolution set to wontfix
- Status changed from new to closed
- Version trunk deleted
#6
follow-up:
↓ 18
@
9 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.
9 years ago
#11
follow-up:
↓ 12
@
9 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
What do i do to fix?
Thank you.
#12
in reply to:
↑ 11
;
follow-ups:
↓ 13
↓ 14
@
9 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
What do i do to fix?
Thank you.
#13
in reply to:
↑ 12
@
9 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
What do i do to fix?
Thank you.
#14
in reply to:
↑ 12
@
9 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
What do i do to fix?
Thank you.
#15
@
9 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
@
9 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.
#18
in reply to:
↑ 6
;
follow-up:
↓ 21
@
9 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 warningdbDelta()
createsslug(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))" );
- SucceedsdbDelta()
see's thatslug(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
@
9 years ago
- Owner set to dd32
- Resolution set to fixed
- Status changed from reopened to closed
In 32852:
#21
in reply to:
↑ 18
@
9 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 warningdbDelta()
createsslug(slug(191))
- SucceedsWhen updating from 4.1 to 4.2+ the operations are:
$wpdb->query( "ALTER TABLE $wpdb->terms DROP INDEX slug, ADD INDEX slug(slug(191))" );
- SucceedsdbDelta()
see's thatslug(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!
#22
@
9 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.
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 duringmake_db_current_silent()
. But something about the waydbDelta()
interpretsKEY 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 duringmake_db_current()
). I'd like @pento to chime in first, though, to make sure that this ain't crazy talk.