Bug #70260 Table disappears when ALTERing with foreign key checks off
Submitted: 6 Sep 2013 12:03 Modified: 6 May 2014 15:27
Reporter: Denis Simonet Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.5.32 OS:Linux (Ubuntu 12.04 LTS)
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[6 Sep 2013 12:03] Denis Simonet
Description:
Heya,

today, I encountered a weird behaviour with foreign_key_checks set to 0 (using InnoDB).

In short: When dropping an index which shouldn't be dropped, followed by a failed ALTER statement with the same table, the table in question is gone. The foreign keys still exist, though.

While I agree that it is normal for the situation to fail, i think that a DBMS should never lose tables, no matter what you do - except when it is a DROP statement, of course.

Kind regards,
Denis

How to repeat:
mysql> DESCRIBE `User_Role_Customer`;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| userId     | int(11) | NO   | MUL | NULL    |                |
| customerId | int(11) | YES  | MUL | NULL    |                |
| roleId     | int(11) | NO   | MUL | NULL    |                |
+------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> DROP INDEX `IDX_User_Role_Customer_1` ON `User_Role_Customer`;
ERROR 1553 (HY000): Cannot drop index 'IDX_User_Role_Customer_1': needed in a foreign key constraint

mysql> DESCRIBE `User_Role_Customer`;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| userId     | int(11) | NO   | MUL | NULL    |                |
| customerId | int(11) | YES  | MUL | NULL    |                |
| roleId     | int(11) | NO   | MUL | NULL    |                |
+------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> SET foreign_key_checks = 0;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP INDEX `IDX_User_Role_Customer_1` ON `User_Role_Customer`;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE `User_Role_Customer`;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| userId     | int(11) | NO   |     | NULL    |                |
| customerId | int(11) | YES  | MUL | NULL    |                |
| roleId     | int(11) | NO   | MUL | NULL    |                |
+------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE `User_Role_Customer` CHANGE `customerId` `userCustomerId` INTEGER NOT NULL;
ERROR 1025 (HY000): Error on rename of './symonitoring/#sql-3e5_1148' to './symonitoring/User_Role_Customer' (errno: 150)

mysql> DESCRIBE `User_Role_Customer`;
ERROR 1146 (42S02): Table 'symonitoring.User_Role_Customer' doesn't exist
[11 Sep 2013 13:15] Miguel Solorzano
Thank you for the bug report. Please provide the output of show create table User_Role_Customer? . Thanks.
[11 Sep 2013 13:18] Denis Simonet
Sure:

CREATE TABLE `User_Role_Customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roleId` int(11) NOT NULL,
  `userCustomerId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_User_Role_Customer_1` (`roleId`,`userCustomerId`),
  KEY `Role_User_Role_Customer` (`roleId`),
  KEY `User_Customer_User_Role_Customer` (`userCustomerId`),
  CONSTRAINT `User_Customer_User_Role_Customer` FOREIGN KEY (`userCustomerId`) REFERENCES `User_Customer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `Role_User_Role_Customer` FOREIGN KEY (`roleId`) REFERENCES `Role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=193 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
[11 Sep 2013 13:21] Denis Simonet
Oops, you need the old structure, of course - I'm sorry. Here we go:

CREATE TABLE `User_Role_Customer` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `userId` int(11) NOT NULL,
 `customerId` int(11) DEFAULT NULL,
 `roleId` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `IDX_User_Role_Customer_1` (`userId`,`customerId`,`roleId`),
 KEY `Role_User_Role_Customer` (`roleId`),
 KEY `Customer_User_Role_Customer` (`customerId`),
 CONSTRAINT `Customer_User_Role_Customer` FOREIGN KEY (`customerId`) REFERENCES `Customer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `Role_User_Role_Customer` FOREIGN KEY (`roleId`) REFERENCES `Role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `User_Role_Customer_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `User` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `User_Role_Customer_ibfk_2` FOREIGN KEY (`customerId`) REFERENCES `Customer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `User_User_Role_Customer` FOREIGN KEY (`userId`) REFERENCES `User` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=172 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
[11 Sep 2013 18:46] Miguel Solorzano
Then please re-write the whole sequence of command to make the bug repeatable please use an empty database so begin with create database command. Thanks.
[12 Sep 2013 7:13] Denis Simonet
Here we go:

CREATE TABLE `Customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `Role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `User` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `User_Role_Customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `customerId` int(11) DEFAULT NULL,
  `roleId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_User_Role_Customer_1` (`userId`,`customerId`,`roleId`),
  KEY `Role_User_Role_Customer` (`roleId`),
  KEY `Customer_User_Role_Customer` (`customerId`),
  CONSTRAINT `Customer_User_Role_Customer` FOREIGN KEY (`customerId`) REFERENCES `Customer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `Role_User_Role_Customer` FOREIGN KEY (`roleId`) REFERENCES `Role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `User_Role_Customer_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `User` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `User_Role_Customer_ibfk_2` FOREIGN KEY (`customerId`) REFERENCES `Customer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `User_User_Role_Customer` FOREIGN KEY (`userId`) REFERENCES `User` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=172 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Then type the following commands:
SET foreign_key_checks = 0;
DROP INDEX `IDX_User_Role_Customer_1` ON `User_Role_Customer`;
ALTER TABLE `User_Role_Customer` CHANGE `customerId` `userCustomerId` INTEGER NOT NULL;

And voilĂ :
mysql> DESCRIBE `User_Role_Customer`;
ERROR 1146 (42S02): Table 'magicDrop.User_Role_Customer' doesn't exist
[12 Sep 2013 7:38] Denis Simonet
I just worked on it and could simplify it to a much simpler scenario:

CREATE TABLE `Customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `User_Role_Customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customerId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_Customer` (`customerId`),
  CONSTRAINT `Customer_Customer` FOREIGN KEY (`customerId`) REFERENCES `Customer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

SET foreign_key_checks = 0;
DROP INDEX `IDX_Customer` ON `User_Role_Customer`;
ALTER TABLE `User_Role_Customer` CHANGE `customerId` `userCustomerId` INTEGER NOT NULL;
DESCRIBE `User_Role_Customer`;
[12 Sep 2013 16:37] Miguel Solorzano
This was already reported and fixed on 5.6 version (see below) the fix however wasn't ported to 5.5 so now verified. Thank you for feedback.

c:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.34 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > use test
Database changed
mysql 5.5 > CREATE TABLE `Customer` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.14 sec)

mysql 5.5 >
mysql 5.5 > CREATE TABLE `User_Role_Customer` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `customerId` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `IDX_Customer` (`customerId`),
    ->   CONSTRAINT `Customer_Customer` FOREIGN KEY (`customerId`) REFERENCES `Customer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.09 sec)

mysql 5.5 >
mysql 5.5 > SET foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 > DROP INDEX `IDX_Customer` ON `User_Role_Customer`;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.5 > ALTER TABLE `User_Role_Customer` CHANGE `customerId` `userCustomerId` INTEGER NOT NULL;
ERROR 1025 (HY000): Error on rename of '.\test\#sql-148c_1' to '.\test\user_role_customer' (errno: 150)
mysql 5.5 > DESCRIBE `User_Role_Customer`;
ERROR 1146 (42S02): Table 'test.user_role_customer' doesn't exist
-------------------------------------------------------------------------------
c:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.14 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use test
Database changed
mysql 5.6 > CREATE TABLE `Customer` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.31 sec)

mysql 5.6 >
mysql 5.6 > CREATE TABLE `User_Role_Customer` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `customerId` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `IDX_Customer` (`customerId`),
    ->   CONSTRAINT `Customer_Customer` FOREIGN KEY (`customerId`) REFERENCES `Customer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.53 sec)

mysql 5.6 >
mysql 5.6 > SET foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 > DROP INDEX `IDX_Customer` ON `User_Role_Customer`;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.6 > ALTER TABLE `User_Role_Customer` CHANGE `customerId` `userCustomerId` INTEGER NOT NULL;
ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
mysql 5.6 > DESCRIBE `User_Role_Customer`;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| customerId | int(11) | YES  |     | NULL    |                |
+------------+---------+------+-----+---------+----------------+
2 rows in set (0.06 sec)
[6 May 2014 15:27] Daniel Price
Fixed as of 5.7.5, and here's the changelog entry:

When "foreign_key_checks" is disabled, "InnoDB" would allow an index
required by a foreign key constraint to be dropped, thereby placing the
table into an inconsistent state. Dropping an index required by a foreign
key constraint should not be permitted. 

Thank you for the bug report.
[24 Aug 2014 21:56] Miguel Solorzano
http://bugs.mysql.com/bug.php?id=73697 marked as duplicate of this one.
[21 Jun 2016 13:21] Daniel Price
Posted by developer:
 
The changelog entry was revised as follows:

With foreign_key_checks=0, InnoDB permitted an index required by a
foreign key constraint to be dropped, placing the table into an
inconsistent and causing the foreign key check that occurs at table load
to fail. InnoDB now prevents dropping an index required by a foreign key
constraint, even with foreign_key_checks=0. The foreign key constraint
must be removed before dropping the foreign key index. 

The following documentation pages were also updated:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_che...
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_foreign_key_che...
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_foreign_key_che...
[12 Sep 2016 9:08] Umesh Shastry
Bug #82946 marked as duplicate of this one
[20 Apr 11:40] Dmitry Lenev
Posted by developer:
 
Bug #26746175 "DROP INDEX ON A FOREIGN KEY COLUMN LEADS TO
MISSING TABLE" was marked as duplicate bug.