Description:
MySQL 5.6 should block users dropping an index from a table which contains an FK constraint.
In the below example if you try to remove the index when foreign key checks have enabled an error will be thrown and you will be unable to drop the index. (expected)
If you disable F_K_C you can drop the index and the table will still be accessible.
However, upon reboot, the table will become inaccessible.
Attempting to drop the table fails
Dropping the database will also fail due to the orphaned FK constraint.
The above behavior was observed in the latest version of 5.6.
Note that in 5.7, an error is also thrown when FK checks are disabled which I believe is the correct/safest behavior.
How to repeat:
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.38 |
+-----------+
1 row in set (0.00 sec)
mysql> use fkissue;
Database changed
mysql> show tables;
Empty set (0.00 sec)
##### Create two tables with a constraint
mysql> create table test1(id decimal(38,0) NOT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> create table test2(id decimal(38,0) NOT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> create index idx1 on test2(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test1 add CONSTRAINT `test_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test2` (`id`);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#### As expected an error is thrown when trying to drop the index on the ref column as it is being used in the constraint.
mysql> alter table test1 drop index test_ibfk_1;
ERROR 1553 (HY000): Cannot drop index 'test_ibfk_1': needed in a foreign key constraint
#### Disable FK checks and you can see we can successfully drop the index. 5.7. throws an error at this point and blocks the operation.
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table test1 drop index test_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)
#### After re-enabling F_K_C you can see the table which had the index removed is still accessible.
mysql> show tables;
+-------------------+
| Tables_in_fkissue |
+-------------------+
| test1 |
| test2 |
+-------------------+
2 rows in set (0.00 sec)
mysql> select * from test1;
Empty set (0.01 sec)
mysql> select * from test2;
Empty set (0.00 sec)
#### After restarting MySQL you will see table access is blocked and the DB/Table is unable to be dropped. At this point, we have dictionary corruption and the index is unable to be readded or constraint dropped due to the table being inaccessible.
mysql> \! service mysql restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
mysql> select * from test1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: fkissue
ERROR 1146 (42S02): Table 'fkissue.test1' doesn't exist
mysql> select * from test2;
Empty set (0.00 sec)
mysql> drop table test1;
ERROR 1051 (42S02): Unknown table 'fkissue.test1'
mysql> show engine innodb status;
.
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2017-10-20 01:29:13 7fb787502700 Error in foreign key constraint of table fkissue/test1:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
CONSTRAINT "test_ibfk_1" FOREIGN KEY ("id") REFERENCES "test2" ("id")
.
mysql> drop database fkissue;
ERROR 1010 (HY000): Error dropping database (can't rmdir './fkissue', errno: 39)
Description: MySQL 5.6 should block users dropping an index from a table which contains an FK constraint. In the below example if you try to remove the index when foreign key checks have enabled an error will be thrown and you will be unable to drop the index. (expected) If you disable F_K_C you can drop the index and the table will still be accessible. However, upon reboot, the table will become inaccessible. Attempting to drop the table fails Dropping the database will also fail due to the orphaned FK constraint. The above behavior was observed in the latest version of 5.6. Note that in 5.7, an error is also thrown when FK checks are disabled which I believe is the correct/safest behavior. How to repeat: mysql> select @@version; +-----------+ | @@version | +-----------+ | 5.6.38 | +-----------+ 1 row in set (0.00 sec) mysql> use fkissue; Database changed mysql> show tables; Empty set (0.00 sec) ##### Create two tables with a constraint mysql> create table test1(id decimal(38,0) NOT NULL); Query OK, 0 rows affected (0.01 sec) mysql> create table test2(id decimal(38,0) NOT NULL); Query OK, 0 rows affected (0.01 sec) mysql> create index idx1 on test2(id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table test1 add CONSTRAINT `test_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test2` (`id`); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 #### As expected an error is thrown when trying to drop the index on the ref column as it is being used in the constraint. mysql> alter table test1 drop index test_ibfk_1; ERROR 1553 (HY000): Cannot drop index 'test_ibfk_1': needed in a foreign key constraint #### Disable FK checks and you can see we can successfully drop the index. 5.7. throws an error at this point and blocks the operation. mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> alter table test1 drop index test_ibfk_1; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> set foreign_key_checks=1; Query OK, 0 rows affected (0.00 sec) #### After re-enabling F_K_C you can see the table which had the index removed is still accessible. mysql> show tables; +-------------------+ | Tables_in_fkissue | +-------------------+ | test1 | | test2 | +-------------------+ 2 rows in set (0.00 sec) mysql> select * from test1; Empty set (0.01 sec) mysql> select * from test2; Empty set (0.00 sec) #### After restarting MySQL you will see table access is blocked and the DB/Table is unable to be dropped. At this point, we have dictionary corruption and the index is unable to be readded or constraint dropped due to the table being inaccessible. mysql> \! service mysql restart Shutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS! mysql> select * from test1; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: fkissue ERROR 1146 (42S02): Table 'fkissue.test1' doesn't exist mysql> select * from test2; Empty set (0.00 sec) mysql> drop table test1; ERROR 1051 (42S02): Unknown table 'fkissue.test1' mysql> show engine innodb status; . ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2017-10-20 01:29:13 7fb787502700 Error in foreign key constraint of table fkissue/test1: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint: , CONSTRAINT "test_ibfk_1" FOREIGN KEY ("id") REFERENCES "test2" ("id") . mysql> drop database fkissue; ERROR 1010 (HY000): Error dropping database (can't rmdir './fkissue', errno: 39)