| Bug #68148 | drop index on a foreign key column leads to missing table | ||
|---|---|---|---|
| Submitted: | 22 Jan 2013 21:10 | Modified: | 22 Apr 2013 12:48 |
| Reporter: | Danil Zburivsky | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 5.5.27, 5.6.7 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | drop index, foreign key, foreign_key_cheks=0 | ||
[22 Jan 2013 21:10]
Danil Zburivsky
[23 Jan 2013 7:59]
Valeriy Kravchuk
Same problem with 5.6.9-rc:
[openxs@chief 5.6]$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.9-rc MySQL Community Server (GPL)
Copyright (c) 2000, 2012, 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> CREATE TABLE ref_table1 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.33 sec)
mysql> CREATE TABLE ref_table2 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.30 sec)
mysql> 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.93 sec)
mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP INDEX `idx_1` ON `main`;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> exit
Bye
[openxs@chief 5.6]$ bin/mysqladmin -uroot shutdown
130123 09:57:27 mysqld_safe mysqld from pid file /home/openxs/dbs/5.6/data/chief.pid ended
[1]+ Done bin/mysqld_safe --no-defaults
[openxs@chief 5.6]$ bin/mysqld_safe --no-defaults &
[1] 2242
[openxs@chief 5.6]$ 130123 09:57:36 mysqld_safe Logging to '/home/openxs/dbs/5.6/data/chief.err'.
130123 09:57:36 mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/5.6/data
[openxs@chief 5.6]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.9-rc MySQL Community Server (GPL)
Copyright (c) 2000, 2012, 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> show create table main;
ERROR 1146 (42S02): Table 'test.main' doesn't exist
mysql> exit
Bye
[openxs@chief 5.6]$ tail -50 data/chief.err
2013-01-23 09:57:26 2209 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
...
2013-01-23 09:57:27 2209 [Note] /home/openxs/dbs/5.6/bin/mysqld: Shutdown complete
130123 09:57:27 mysqld_safe mysqld from pid file /home/openxs/dbs/5.6/data/chief.pid ended
130123 09:57:36 mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/5.6/data
2013-01-23 09:57:36 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-01-23 09:57:36 0 [Warning] Changed limits: max_open_files: 1024 max_connections: 151 table_cache: 431
2013-01-23 09:57:36 2306 [Note] Plugin 'FEDERATED' is disabled.
2013-01-23 09:57:36 2306 [Note] InnoDB: The InnoDB memory heap is disabled
2013-01-23 09:57:36 2306 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-01-23 09:57:36 2306 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-01-23 09:57:36 2306 [Note] InnoDB: CPU does not support crc32 instructions
2013-01-23 09:57:36 2306 [Note] InnoDB: Using Linux native AIO
2013-01-23 09:57:36 2306 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-01-23 09:57:36 2306 [Note] InnoDB: Completed initialization of buffer pool
2013-01-23 09:57:36 2306 [Note] InnoDB: Highest supported file format is Barracuda.
2013-01-23 09:57:37 2306 [Note] InnoDB: 128 rollback segment(s) are active.
2013-01-23 09:57:37 2306 [Note] InnoDB: Waiting for purge to start
2013-01-23 09:57:37 2306 [Note] InnoDB: 1.2.9 started; log sequence number 568282401
2013-01-23 09:57:37 2306 [Note] Server hostname (bind-address): '*'; port: 3306
2013-01-23 09:57:37 2306 [Note] IPv6 is available.
2013-01-23 09:57:37 2306 [Note] - '::' resolves to '::';
2013-01-23 09:57:37 2306 [Note] Server socket created on IP: '::'.
2013-01-23 09:57:37 2306 [Note] Event Scheduler: Loaded 0 events
2013-01-23 09:57:37 2306 [Note] /home/openxs/dbs/5.6/bin/mysqld: ready for connections.
Version: '5.6.9-rc' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
2013-01-23 09:57:39 2306 [Warning] InnoDB: Cannot open table test/main from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2013-01-23 09:57:47 2306 [Warning] InnoDB: Cannot open table test/main from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
[23 Jan 2013 10:13]
Erlend Dahl
Thank you for the bug report. Reproducible on latest 5.6 sources.
[22 Apr 2013 12:48]
Bugs System
Added changelog entry for 5.6.12 and 5.7.2: "After disabling foreign key checks with "SET FOREIGN_KEY_CHECKS=0" and performing a "DROP INDEX", the table would no longer be accessible after restarting the server. This fix disallows deleting an index on a foreign key column, even when "SET FOREIGN_KEY_CHECKS=0".
[27 May 2013 10:37]
Oli Sennhauser
Will this bug also be fixed in 5.5? It is pretty ugly and can affect most of MySQL users using FK! A lot of MySQL users just upgraded to 5.5 or are not confident in 5.6 yet...
[17 Mar 2014 12:52]
Przemyslaw Malkowski
I cannot confirm this bug is fixed.
mysql [localhost] {msandbox} ((none)) > select @@version_comment, @@version;
+------------------------------+-----------+
| @@version_comment | @@version |
+------------------------------+-----------+
| MySQL Community Server (GPL) | 5.6.16 |
+------------------------------+-----------+
mysql [localhost] {msandbox} (test) > CREATE TABLE ref_table1 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql [localhost] {msandbox} (test) > CREATE TABLE ref_table2 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql [localhost] {msandbox} (test) > 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.23 sec)
mysql [localhost] {msandbox} (test) > alter table main drop key idx_1;
ERROR 1553 (HY000): Cannot drop index 'idx_1': needed in a foreign key constraint
mysql [localhost] {msandbox} (test) > SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > alter table main drop key idx_1;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- mysql restart
mysql [localhost] {msandbox} (test) > show tables;
+----------------+
| Tables_in_test |
+----------------+
| main |
| ref_table1 |
| ref_table2 |
+----------------+
3 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > show create table main\G
ERROR 1146 (42S02): Table 'test.main' doesn't exist
-- err log:
2014-03-17 12:42:23 21975 [Warning] InnoDB: Load table 'test/main' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2014-03-17 12:42:23 21975 [Warning] InnoDB: Cannot open table test/main from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
mysql [localhost] {msandbox} (test) > SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > show create table main\G
*************************** 1. row ***************************
Table: main
Create Table: 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`),
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 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > check table main;
+-----------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.main | check | status | OK |
+-----------+-------+----------+----------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > optimize table main;
+-----------+----------+----------+----------------------------------------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+----------+----------+----------------------------------------------------------------------------------------------------------------------+
| test.main | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.main | optimize | error | Error on rename of './test/#sql-55d7_2' to './test/main' (errno: 150 - Foreign key constraint is incorrectly formed) |
| test.main | optimize | status | Operation failed |
+-----------+----------+----------+----------------------------------------------------------------------------------------------------------------------+
3 rows in set, 2 warnings (0.08 sec)
-- err log:
2014-03-17 12:51:39 7f6eecb93700 InnoDB: Error: in ALTER TABLE `test`.`main`
InnoDB: has or is referenced in foreign key constraints
InnoDB: which are not compatible with the new table definition.
2014-03-17 12:51:39 7f6eecb93700 InnoDB: Error: in ALTER TABLE `test`.`main`
InnoDB: has or is referenced in foreign key constraints
InnoDB: which are not compatible with the new table definition.
mysql [localhost] {msandbox} (test) > show tables;
+----------------+
| Tables_in_test |
+----------------+
| ref_table1 |
| ref_table2 |
+----------------+
2 rows in set (0.00 sec)
[przemek@lap-prz sandboxes]$ ls -lh oracle5.6.16/data/test/
total 360K
-rw-rw----. 1 przemek przemek 65 Mar 17 12:39 db.opt
-rw-rw----. 1 przemek przemek 8.4K Mar 17 12:39 ref_table1.frm
-rw-rw----. 1 przemek przemek 96K Mar 17 12:39 ref_table1.ibd
-rw-rw----. 1 przemek przemek 8.4K Mar 17 12:40 ref_table2.frm
-rw-rw----. 1 przemek przemek 96K Mar 17 12:40 ref_table2.ibd
-rw-rw----. 1 przemek przemek 8.5K Mar 17 12:40 #sql2-55d7-2.frm
-rw-rw----. 1 przemek przemek 128K Mar 17 12:40 #sql2-55d7-2.ibd
mysql [localhost] {msandbox} (test) > 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;
ERROR 1022 (23000): Can't write; duplicate key in table 'main'
The MySQL 5.6 has only this advantage over 5.5 in that case, that after disabling foreign checks, you can fix the table by adding the needed index back. (But that works before someone does optimize table.) So in case you also break a table in 5.5 or older, you can try use the datadir with 5.6, disable FK checks and re-add the index. It worked for me.
[17 Mar 2014 13:04]
Hartmut Holzgraefe
The actual ChangeLog entry reads different to what was posted in this bug: InnoDB: After disabling foreign key checks with SET foreign_key_checks=0 and performing a DROP INDEX, the table was no longer accessible after restarting the server. This fix allows the table with missing foreign key indexes to be accessed when SET foreign_key_checks=0. When the table is accessible, the user must recreate the missing indexes to fulfill the foreign key constraints. (Bug #16208542, Bug #68148) So the new behavior is actually documented the same way you found it working (or not working) ...
[17 Mar 2014 13:23]
Przemyslaw Malkowski
Thank you Hartmut, yes, the sentence "This fix disallows deleting an index on a foreign key column, even when "SET FOREIGN_KEY_CHECKS=0"" is very misleading here. Even if the current behavior is the expected one, I would at least prefer to see a warning when dropping such index. Also the "check table" should tell something about FK error, no?
[26 Nov 2014 23:03]
Chehai WU
The following scenario makes MySQL 5.6.21 crash. It looks like re-adding index does not re-populate foreign key constraint index. Is this bug supposed to fix this crash? CREATE DATABASE test_fk; USE test_fk; CREATE TABLE a(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b_id INT, col1 INT); CREATE TABLE b(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY); ALTER TABLE a ADD CONSTRAINT fk_a_b_id_b FOREIGN KEY (b_id) REFERENCES b(id); CREATE INDEX index_a_on_b_id_col1 ON a(b_id, col1); SET FOREIGN_KEY_CHECKS = 0; DROP INDEX index_a_on_b_id_col1 ON a; CREATE INDEX index_a_on_b_id ON a(b_id); SET FOREIGN_KEY_CHECKS = 1; ALTER TABLE a CHANGE col1 col2 INT;
[30 Aug 2017 16:43]
dsjknd kjnkjn
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)
