MySQL Bugs: #70260: Table disappears when ALTERing with foreign key checks off

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

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

Thank you for the bug report. Please provide the output of show create table User_Role_Customer? . Thanks.

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

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

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.

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

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`;

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)

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.

http://bugs.mysql.com/bug.php?id=73697 marked as duplicate of this one.

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

Bug #82946 marked as duplicate of this one

Posted by developer:
 
Bug #26746175 "DROP INDEX ON A FOREIGN KEY COLUMN LEADS TO
MISSING TABLE" was marked as duplicate bug.