Bug #82946 drop foreign key with FOREIGN_KEY_CHECKS=0 lead table lost when restart

• Submitted: 12 Sep 2016 8:51
• Modified: 12 Sep 2016 9:08
• Reporter: zhang simon (OCA)
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 5.5,5.6
• OS: Any
• CPU Architecture: Any

[12 Sep 2016 8:51] zhang simon

Description:
Drop foreign key with FOREIGN_KEY_CHECKS=0 lead table lost when restart

How to repeat:
testcase as follows:

SET FOREIGN_KEY_CHECKS = 0;

create table t1(c1 int primary key, c2 int) engine=innodb;
create table t2(c1 int primary key, c2 int , foreign  key (c2) references t1(c1)) engine=innodb;

alter table t2 drop key c2;

--echo server restart
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--shutdown_server 10
--source include/wait_until_disconnected.inc
--enable_reconnect
--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--source include/wait_until_connected_again.inc

select * from t2;

-------------------- result fail -----------

mysqltest: At line 25: query 'select * from t2' failed: 1146: Table 'test.t2' doesn't exist

Suggested fix:
drop foreign key should check constraint aways even if FOREIGN_KEY_CHECKS=0

[12 Sep 2016 9:02] zhang simon

5.7  have fixed

commit c1ed0eafcf3de23635e18f6d1f306c8fe97fa287
Author: Thirunarayanan B <[e-mail]>
Date:   Mon May 5 16:47:13 2014 +0530

    Bug #17449901        TABLE DISAPPEARS WHEN ALTERING WITH
                        FOREIGN KEY CHECKS OFF
    
    Analysis:
    By removing the FK index with out removing the foreign key constraint,
    we are moving the table into inconsistent state. FK index lookup happens
    during load table process. So it fails to open the table when FK index
    is missing.
    
    Fix:
    Don't allow to drop an index if it is needed for an FK constraint,
    even if foreign_key_checks is disabled.
    
                Approved by Jimmy (rb-5274)

[12 Sep 2016 9:08] MySQL Verification Team

Hello zhang,

Thank you for the report and test case.
As you already found, this is duplicate of Bug #70260

Thanks,
Umesh