Bug #18942 DROP DATABASE does not drop an orphan FOREIGN KEY constraint
Submitted: 10 Apr 2006 13:03 Modified: 19 Jun 2010 18:03
Reporter: Heikki Tuuri Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:4.1.14, 5.0, 5.1, 6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: fixed in pre-v6, v6
Triage: D3 (Medium)

[10 Apr 2006 13:03] Heikki Tuuri
Description:
In this Forum posting, a user has somehow managed to create an orphan foreign key constraint:

http://forums.mysql.com/read.php?22,81537

There are two bugs here:

1) how did InnoDB manage to create an orphan constraint;

2) InnoDB should drop the orphan constraint in DROP DATABASE.

How to repeat:
Do not know.

Suggested fix:
Write an InnoDB stored procedure that drops constraints with name:

databasename/...

in row_drop_database_for_mysql() in row0mysql.c
[12 Jun 2006 13:00] Heikki Tuuri
This forum posting may be assoaciated with this bug:

http://forums.mysql.com/read.php?22,95361
[29 Oct 2006 0:06] [ name withheld ]
I have experienced this same problem with MySQL 5.0.22. I was using MySQL Administrator to edit some tables and ended up with an orphan foreign key that doesn't go away when I drop and recreate the database.

I was using only InnoDB tables as far as I know but shortly before I discovered the orphan relationship I did rename the table in question to something else and then changed its name back to the original name.
[4 Jan 2007 14:51] Heikki Tuuri
There exists now a patch by Vasil. It makes DROP DATABASE to drop also orphan constraints. The mechanism how some users have been able to create orphan constraints is still a mystery.
[3 Apr 2007 17:34] Randall Roberts
As a corollary to this, I can't get the ALTER TABLE ... DROP CONSTRAINT to work.  Also, I haven't found anywhere in the documentation how to query a list of constraints.

I have code that can replicate this, but it is for my training materials and I cannot not put it up because of the disclaimer below that says all postings become the property of MySQL Inc.

I can be contacted by email for further information.
[15 Jan 2008 5:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41009

ChangeSet@1.2649, 2008-01-14 22:55:50-07:00, tsmith@ramayana.hindu.god +7 -0
  Apply innodb-5.1-ss2146 and innodb-5.1-ss2178 snapshots.
  
  Fixes:
  
  Bug #18942: DROP DATABASE does not drop an orphan FOREIGN KEY constraint
    Fix Bug#18942 by dropping all foreign key constraints at the end of
    DROP DATABASE. Usually, by then, there are no foreign constraints
    left because all of them are dropped when the relevant tables are
    dropped. This code is to ensure that any orphaned FKs are wiped too.
  
  Bug #29157: UPDATE, changed rows incorrect
    Return HA_ERR_RECORD_IS_THE_SAME from ha_innobase::update_row() if no
    columns were updated.
  
  Bug #32440: InnoDB free space info does not appear in SHOW TABLE STATUS or I_S
    Put information about the free space in a tablespace in
    INFORMATION_SCHEMA.TABLES.DATA_FREE. This information was previously
    available in INFORMATION_SCHEMA.TABLES.TABLE_COMMENT, but MySQL has
    removed it from there recently.
    The stored value is in kilobytes.
    This can be considered as a permanent workaround to
    http://bugs.mysql.com/32440. "Workaround" becasue that bug is about the
    data missing from TABLE_COMMENT and this is actually not solved.
[24 Jan 2008 8:52] Timothy Smith
Patch queued to 5.1-build.  NOT yet in 6.0 (5.1-snapshot is null merged into 6.0).  Please return to "Patch approved" after documenting until 6.0 snapshot is available.
[25 Feb 2008 16:00] Bugs System
Pushed into 5.1.24-rc
[25 Feb 2008 16:06] Bugs System
Pushed into 6.0.5-alpha
[25 Feb 2008 18:18] Paul Dubois
Noted in 5.1.24, 6.0.5 changelogs.

DROP DATABASE did not drop orphaned FOREIGN KEY constraints.
[26 Feb 2008 2:03] Paul Dubois
Correction: Not yet fixed in 6.0.5.

Resetting to Patch Pending waiting for push of fix to 6.0.x.
[27 Feb 2008 15:27] Pascal Alberty
>The mechanism how some users have been able to create orphan constraints is still a mystery.

It seems that create a FK defined with a wrong reference table name (casse error) create this kind of orphan constraints.

I had the problem:

ex:
ALTER TABLE `table_1` ADD CONSTRAINT `FK123` FOREIGN KEY ( `column_1` ) REFERENCES `tableName` ( `column_2` );
If tablename is used instead tableName, this give you a message "can't create table". Then if you correct the sql statement, the FK is created. Drop the all database and try to create it again and you have the error message describe is this issue.

Is there any workaround to this problem (other then waiting a release). I have the problem on a 5.0.32-Debian_7etch1-log version. All my MySQL installation (in fact, all my InnoDB database) are corrupted and I'm not able to create any other tables.

Thanks
[4 Mar 2008 15:46] Heikki Tuuri
Tim, have you now pushed this to 6.0.x?
Regards, Heikki
[4 Mar 2008 15:51] Heikki Tuuri
I retract the question: we have not shipped any InnoDB-6.0 snapshot yet!
--Heikki
[4 Mar 2008 16:01] Pascal Alberty
Please forget also my comment (27th feb).
My problem was due to a specific bug http://bugs.mysql.com/bug.php?id=25596
[29 Mar 2008 19:47] Jon Stephens
Fix also noted for 5.1.23-ndb-6.3.11.
[24 Jun 2008 21:02] Calvin Sun
Merged into 6.0.6-alpha, according to Tim.
[25 Jun 2008 1:28] Paul Dubois
Noted in 6.0.6 changelog.
[5 May 2010 15:25] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 2:03] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:05] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:33] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 7:01] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 22:47] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:09] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:56] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:36] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)