Bug #62894 InnoDB Data Dictionary is out of sync (FK constraint for deleted child table)
Submitted: 23 Oct 2011 15:27 Modified: 6 Jun 2013 12:12
Reporter: Анатолий Кирсанов Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.5.17 OS:Microsoft Windows (Vista SP2 Home Basic Russian)
Assigned to: CPU Architecture:Any
Tags: innodb

[23 Oct 2011 15:27] Анатолий Кирсанов
Description:
I had two tables (parent and child) with foreign key constraint. 

Now child table is absent in database directory (there is no frm file) and in internal InnoDB data dictionary (I verified it using InnoDB table monitor - http://dev.mysql.com/doc/refman/5.5/en/innodb-monitors.html#innodb-table-monitor). 

But a foreign key constraint was somehow preserved in internal dictionary. So I can't delete parent table (because foreign key constraint is still active), and can't re-create child table (because InnoDB is sure that this table still exists). 

I have standard installation with one data file for all tables.

How to repeat:
I am not sure how child table was dropped. I used synchronization with database in MySQL Workbench. There was some index for child table that was deleted during synchronization. I can't reproduce exact steps. So I have to write about the fact - child table is absent, parent table and foreign key constrain are still in database.

Suggested fix:
1.) I already done steps mentioned in 'Problem with CREATE TABLE' paragraph of 'Troubleshooting InnoDB Data Dictionary Operations' article. Nothing helps.

2.) I can delete parent table using 'set foreign_key_checks=0;'. But I cant create child table all the same.

3.) I happily have backups for all databases on my server. So, I can reinstall MySQL and import all databases. It should resolve the problem. But this is bad variant. It would be hard process to repeat every time when this error happens.

4.) I hope that there is some steps like in 'Troubleshooting InnoDB Data Dictionary Operations' (http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html) for my situation.
[23 Oct 2011 18:58] Анатолий Кирсанов
I recovered scenario resulted in child table deletion:

1.) My child table

CREATE TABLE `mydb`.`child` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `PARENT_ID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`ID`,`PARENT_ID`),
  UNIQUE KEY `ID` (`ID`),
  KEY `FK_Parent` (`PARENT_ID`),
  CONSTRAINT `FK_Parent` FOREIGN KEY (`PARENT_ID`) REFERENCES `parent` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

2.) Command executed during synchronization
ALTER TABLE `mydb`.`child` DROP INDEX `FK_Parent`;

After this command table disappeared too. 

I have another table with the same structure. But all is ok with it. So the exact reason is unknown.

I found a workaround:

Just replace column positions in primary key. Then foreign key index does not created and does not deleted during next synchronization.

CREATE TABLE `mydb`.`child` (
  `PARENT_ID` int(11) unsigned NOT NULL,
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`PARENT_ID`, `ID`),
  UNIQUE KEY `ID` (`ID`),
  CONSTRAINT `FK_Parent` FOREIGN KEY (`PARENT_ID`) REFERENCES `parent` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
[25 Oct 2011 11:55] Valeriy Kravchuk
Please, send the entire error log (usually it is <hostname>.err file in the datadir) and the results of:

show engine innodb status\G

from your MySQL server.
[25 Oct 2011 12:37] Анатолий Кирсанов
InnoDB status

Attachment: innodbstatus.txt (text/plain), 3.65 KiB.

[20 Feb 2012 19:18] Sveta Smirnova
This looks very similar to bug #62100. Need to check when bug #62100 is fixed.
[6 May 2013 12:12] MySQL Verification Team
Please check with latest version. Thanks.
[7 Jun 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".