Bug #50384 InnoDB should check whether a foreign key exists before attempting to drop it

• Submitted: 15 Jan 2010 21:59
• Modified: 18 Jan 2010 17:21
• Reporter: Ben Krug
• Status: Duplicate
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.0.90-bzr, 5.1.43-bzr
• OS: Any
• CPU Architecture: Any

[15 Jan 2010 21:59] Ben Krug

Description:
InnoDB, in 5.0, will attempt to drop a non-existent foreign key rather than checking for existence first.  On large tables, this can lead to the table being unavailable for a long time, as it is copied over, etc.
In 5.1, this is not so (probably due to fast alter and the plugin.

How to repeat:
Create a large InnoDB table, try to drop a non-existent foreign key.

Suggested fix:
check for existence first

[16 Jan 2010 15:27] Valeriy Kravchuk

Verified just as described with both recent 5.0.90 and 5.1.43 from bzr. For any large enough table server spends many seconds (if not minutes or hours) making temporary copy of the table before returning error message like:

mysql> alter table ti drop foreign key fk1;
ERROR 1025 (HY000): Error on rename of './test/ti' to './test/#sql2-1822f-1' (errno: 152)

if you try to drop non-existing foreign key.

[18 Jan 2010 12:27] MySQL Verification Team

i had a similar request in bug #46281

[18 Jan 2010 17:06] Mikhail Izioumtchenko

this is better done in MySQL, not SE

[18 Jan 2010 17:21] Valeriy Kravchuk

So, this is a duplicate of bug #46281.

[26 May 2011 17:31] Valeriy Kravchuk

Bug #61310 looks like a duplicate of this one (but for 5.5.x).