MySQL Bugs: #87612: drop index on a foreign key column leads to missing table

Bug #87612	drop index on a foreign key column leads to missing table
Submitted:	30 Aug 2017 16:51	Modified:	20 Apr 2019 11:40
Reporter:	dsjknd kjnkjn	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.6.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	drop index, foreign key, foreign_key_cheks=0

Description:
See closed bug #68148

Issues still exists in 5.6.27:

alter table `Accounts` drop primary key ;

Error Code: 1025. 
Error on rename of './quickbookscache/#sql-2407_90aa2' to './quickbookscache/accounts' 
(errno: 150 - Foreign key constraint is incorrectly formed)

How to repeat:
alter table `Accounts` drop primary key ;

Confirmed that issue also exists in 5.7.17

c:\dbs>c:\dbs\5.6\bin\mysql -uroot --local-infile --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.39-log Source distribution 2017-SEP-01

Copyright (c) 2000, 2017, 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 > CREATE DATABASE d;
Query OK, 1 row affected (0.00 sec)

mysql 5.6 > USE d
Database changed
mysql 5.6 > CREATE TABLE ref_table1 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql 5.6 > CREATE TABLE ref_table2 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql 5.6 > CREATE TABLE `main` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `ref_id1` int(11) NOT NULL,
    ->   `ref_id2` int(11) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `idx_1` (`ref_id1`,`ref_id2`),
    ->   KEY `FK_set_out_analysis_route_id` (`ref_id2`),
    ->   CONSTRAINT `FK_1` FOREIGN KEY (`ref_id1`) REFERENCES `ref_table1` (`id`) ,
    ->   CONSTRAINT `FK_2` FOREIGN KEY (`ref_id2`) REFERENCES `ref_table2` (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

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

mysql 5.6 > DROP INDEX `idx_1` ON `main`;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.6 > exit
Bye

c:\dbs>56q

c:\dbs>c:\dbs\5.6\bin\mysqladmin -uroot --port=3560 -p shutdown
Enter password:

c:\dbs>56c

c:\dbs>c:\dbs\5.6\bin\mysql -uroot --local-infile --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.39-log Source distribution 2017-SEP-01

Copyright (c) 2000, 2017, 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 d
Database changed
mysql 5.6 > SHOW CREATE TABLE main;
ERROR 1146 (42S02): Table 'd.main' doesn't exist

Thank you for the bug report. Repeatable on 5.6 and not anymore on 5.7:

c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --local-infile  --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log Source distribution 2017-SEP-01

Copyright (c) 2000, 2017, 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 > CREATE DATABASE d;
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > USE d
Database changed
mysql 5.7 > CREATE TABLE ref_table1 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE ref_table2 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE `main` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `ref_id1` int(11) NOT NULL,
    ->   `ref_id2` int(11) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `idx_1` (`ref_id1`,`ref_id2`),
    ->   KEY `FK_set_out_analysis_route_id` (`ref_id2`),
    ->   CONSTRAINT `FK_1` FOREIGN KEY (`ref_id1`) REFERENCES `ref_table1` (`id`) ,
    ->   CONSTRAINT `FK_2` FOREIGN KEY (`ref_id2`) REFERENCES `ref_table2` (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql 5.7 > SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

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

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

Posted by developer:
 
Hello!

As mentioned above the problem is not repeatable with 5.7.
This is because it was fixed in version 5.7.5 by the patch for
Bug #17449901 "TABLE DISAPPEARS WHEN ALTERING WITH FOREIGN KEY
CHECKS OFF".

So I am marking this bug report as a duplicate of the latter.

Posted by developer:
 
Alternative ID for the base bug report is bug#70260
"TABLE DISAPPEARS WHEN ALTERING WITH FOREIGN KEY CHECKS OFF".