MySQL Bugs: #73697: Table removed without explicit drop table

Bug #73697	Table removed without explicit drop table
Submitted:	24 Aug 2014 12:25	Modified:	24 Aug 2014 21:55
Reporter:	Bugs Grrla	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
See below.

How to repeat:
CREATE TABLE `res` (
 `id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `foo` (
 `id` int(11) NOT NULL,
 `id_res` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `id_res` (`id_res`),
 CONSTRAINT `foo_ibfk_2` FOREIGN KEY (`id_res`) REFERENCES `res` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| foo            |
| res            |
+----------------+
2 rows in set (0.00 sec)
mysql> set foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table foo drop index id_res;
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table foo drop id_res;
ERROR 1025 (HY000): Error on rename of './test/#sql-3e4_34f7' to './test/foo' (errno: 150)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| res            |
+----------------+
1 row in set (0.00 sec)

There is no workaround available.

On 5.6.20 I get something else:

SELECT VERSION(); -- 5.6.20-log  

DROP TABLE IF EXISTS `res`;

DROP TABLE IF EXISTS `foo`;

CREATE TABLE `res` (
 `id` INT(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `foo` (
 `id` INT(11) NOT NULL,
 `id_res` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `id_res` (`id_res`),
 CONSTRAINT `foo_ibfk_2` FOREIGN KEY (`id_res`) REFERENCES `res` (`id`) ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE foo DROP INDEX id_res;

ALTER TABLE foo DROP id_res;
-- Error Code: 1828
-- Cannot drop column 'id_res': needed in a foreign key constraint 'test/foo_ibfk_2'

.. but FK_CHECKS is OFF ???

-- Peter
-- not a MySQL/Oracle person

On 5.5.39 however I experience the same as reporter here: 

SELECT VERSION(); -- 5.5.39   

DROP TABLE IF EXISTS `res`;

DROP TABLE IF EXISTS `foo`;

CREATE TABLE `res` (
 `id` INT(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `foo` (
 `id` INT(11) NOT NULL,
 `id_res` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `id_res` (`id_res`),
 CONSTRAINT `foo_ibfk_2` FOREIGN KEY (`id_res`) REFERENCES `res` (`id`) ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE foo DROP INDEX id_res;

ALTER TABLE foo DROP id_res;
-- Error Code: 1025
-- Error on rename of '.\test\#sql-c1c_1' to '.\test\foo' (errno: 150)

SHOW TABLES; -- shows that `foo` table is gone

Forgot: both servers tested are Windows 64 bit servers.

Thank you for the bug report. Duplicate of: http://bugs.mysql.com/bug.php?id=70260

C:\dbs>net start mysqld57
The mysqld57 service is starting...
The mysqld57 service was started successfully.

C:\dbs>57

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

Copyright (c) 2000, 2014, 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.7 > USE test
Database changed
mysql 5.7 > CREATE TABLE `res` (
    ->  `id` int(11) NOT NULL,
    ->  PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.28 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE `foo` (
    ->  `id` int(11) NOT NULL,
    ->  `id_res` int(11) DEFAULT NULL,
    ->  PRIMARY KEY (`id`),
    ->  KEY `id_res` (`id_res`),
    ->  CONSTRAINT `foo_ibfk_2` FOREIGN KEY (`id_res`) REFERENCES `res` (`id`) ON UPDATE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.33 sec)

mysql 5.7 >  show tables;
+----------------+
| Tables_in_test |
+----------------+
| foo            |
| res            |
| t1             |
| x              |
+----------------+
4 rows in set (0.00 sec)

mysql 5.7 > set foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 >  alter table foo drop index id_res;
ERROR 1553 (HY000): Cannot drop index 'id_res': needed in a foreign key constraint
mysql 5.7 > exit