MySQL Bugs: #68975: DROP on innodb table does not work with foreign

Bug #68975	DROP on innodb table does not work with foreign_keys
Submitted:	16 Apr 2013 17:55	Modified:	17 May 2013 11:11
Reporter:	Michael Froehlich	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.5.29	OS:	Linux (debian squeeze)
Assigned to:		CPU Architecture:	Any
Tags:	deleted ibd files, Drop, foreign keys

Description:
Hi,

i had a problem with a corrupt innodb table on my mysql server.
i had to delete the files on the filesystem (production server), because every access on the table caused the server to crash.

So i deleted the file ibd and frm file for one table. After that i had no possibility either to drop/remove it completely from mysql or to create a new one.

i think, this is because of the foreign key constraints.

Regards, Michael

How to repeat:
1. create 4 tables
CREATE TABLE `const_iso_country` (
  `iso_countrycode` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`iso_countrycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `const_iso_state` (
  `iso_state_id` int(11) NOT NULL DEFAULT '0',
  `iso_countrycode` char(2) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`iso_state_id`),
  KEY `const_iso_state_ibfk1` (`iso_countrycode`),
  CONSTRAINT `const_iso_state_ibfk1` FOREIGN KEY (`iso_countrycode`) REFERENCES `const_iso_country` (`iso_countrycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `shop_salutation_option` (
  `salutation_option_id` int(11) NOT NULL DEFAULT '0',
  `salutation_option_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`salutation_option_id`),
  UNIQUE KEY `index_1` (`salutation_option_name`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `customer_address` (
  `address_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL DEFAULT '0',
  `type` enum('main','delivery') COLLATE utf8_unicode_ci NOT NULL,
  `salutation_option_id` int(11) DEFAULT NULL,
  `iso_state_id` int(11) DEFAULT NULL,
  `country` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`address_id`),
  UNIQUE KEY `customer_id` (`customer_id`,`type`),
  KEY `ref_1724_fk` (`customer_id`),
  KEY `reference_195_fk` (`salutation_option_id`),
  KEY `reference_218_fk` (`country`),
  KEY `iso_state_id` (`iso_state_id`),
  CONSTRAINT `0_18096` FOREIGN KEY (`salutation_option_id`) REFERENCES `shop_salutation_option` (`salutation_option_id`),
  CONSTRAINT `0_18097` FOREIGN KEY (`country`) REFERENCES `const_iso_country` (`iso_countrycode`),
  CONSTRAINT `0_18098` FOREIGN KEY (`iso_state_id`) REFERENCES `const_iso_state` (`iso_state_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

2. delete frm and ibd file on disk
mysql@dba-testing[dev] (devinstance) > ll
-rw-r----- 1 mysql dba   8582 Apr 16 19:31 const_iso_country.frm
-rw-r----- 1 mysql dba  98304 Apr 16 19:31 const_iso_country.ibd
-rw-r----- 1 mysql dba   8628 Apr 16 19:31 const_iso_state.frm
-rw-r----- 1 mysql dba 114688 Apr 16 19:31 const_iso_state.ibd
-rw-r----- 1 mysql dba   8806 Apr 16 19:31 customer_address.frm
-rw-r----- 1 mysql dba 180224 Apr 16 19:31 customer_address.ibd
-rw-r----- 1 mysql dba   8658 Apr 16 19:31 shop_salutation_option.frm
-rw-r----- 1 mysql dba 114688 Apr 16 19:31 shop_salutation_option.ibd
mysql@dba-testing[dev] (devinstance) > rm customer_address.*

3. try to drop / create the TABLE
mysql> use dev
Database changed
mysql> show tables;
+------------------------+
| Tables_in_dev          |
+------------------------+
| const_iso_country      |
| const_iso_state        |
| shop_salutation_option |
+------------------------+
3 rows in set (0.00 sec)
mysql>
mysql> drop table customer_address;
ERROR 1051 (42S02): Unknown table 'customer_address'
mysql>
mysql> CREATE TABLE `customer_address` (
  `address_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL DEFAULT '0',
  `type` enum('main','delivery') COLLATE utf8_unicode_ci NOT NULL,
  `salutation_option_id` int(11) DEFAULT NULL,
  `iso_state_id` int(11) DEFAULT NULL,
  `country` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`address_id`),
  UNIQUE KEY `customer_id` (`customer_id`,`type`),
  KEY `ref_1724_fk` (`customer_id`),
  KEY `reference_195_fk` (`salutation_option_id`),
  KEY `reference_218_fk` (`country`),
  KEY `iso_state_id` (`iso_state_id`),
  CONSTRAINT `0_18096` FOREIGN KEY (`salutation_option_id`) REFERENCES `shop_salutation_option` (`salutation_option_id`),
  CONSTRAINT `0_18097` FOREIGN KEY (`country`) REFERENCES `const_iso_country` (`iso_countrycode`),
  CONSTRAINT `0_18098` FOREIGN KEY (`iso_state_id`) REFERENCES `const_iso_state` (`iso_state_id`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ERROR 1050 (42S01): Table '"dev"."customer_address"' already exists

mysql> alter table customer_address DISCARD TABLESPACE;
ERROR 1146 (42S02): Table 'dev.customer_address' doesn't exist

mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------+
| Warning | 155 | Table 'dev.customer_address' doesn't exist |
| Error | 1051 | Unknown table 'customer_address' |
+---------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

In the log:
130416 11:20:51 InnoDB: error: space object of table
'db244340540/customer_address',
InnoDB: space id 7835 did not exist in memory. Retrying an open.
130416 11:20:51 InnoDB: Error: tablespace id and flags in file
'./db244340540/customer_address.ibd' are 139966 and 0, but in the InnoDB
InnoDB: data dictionary they are 7835 and 0.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB:
http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Error: trying to load index PRIMARY for table
dev/customer_address
InnoDB: but the index tree has been freed!
InnoDB: Index is corrupt but forcing load into data dictionary
InnoDB: Error: trying to load index customer_id for table
dev/customer_address
InnoDB: but the index tree has been freed!
InnoDB: Index is corrupt but forcing load into data dictionary
InnoDB: Error: trying to load index ref_1724_fk for table
dev/customer_address
InnoDB: but the index tree has been freed!
InnoDB: Index is corrupt but forcing load into data dictionary
InnoDB: Error: trying to load index "reference_195_fk" for table
"dev"."customer_address"
InnoDB: but the first index is not clustered!
130416 11:20:51 InnoDB: Error: table `dev`.`customer_address`
does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html

Suggested fix:
-

error log output db names have been change by hand, so please do not get confused.
It`s all in "dev" database.

Thank you for the bug report. Why do you think it's a bug when you deleted the *.frm/ibd files leaving the data dictionary with orphaned tables?. Please see:

http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html

->Issues relating to the InnoDB data dictionary........

Thanks.

>>Thank you for the bug report. Why do you think it's a bug when you
>>deleted the *.frm/ibd files leaving the data dictionary with orphaned
>>tables?. 

First of all, i had to delete the files, cause every access to it, crashed the entire mysql server. (show table status, drop, select etc.)

I just wanted to make you aware of, that you can not add the table again, if you deleted data files that contain foreign keys.

You have to dump all data and create a new instance.
There is NO way to repair that situation.
If the table has no FKs, it is possible to repair the data dictionary, but if the table contains FKs, it is not.

Regards, Michael

And the below instructions doesn't works for you?

http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html

Thanks;

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".