| Bug #81595 | FOREIGN_KEY_CHECKS disabling doesn't work | ||
|---|---|---|---|
| Submitted: | 25 May 2016 15:10 | Modified: | 4 Jul 2016 4:33 |
| Reporter: | Anton Ohorodnyk | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.7.12 | OS: | Linux (Ubuntu 14.04, CentOS 7) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | foreign_key, foreign_key_checks | ||
[25 May 2016 15:10]
Anton Ohorodnyk
[25 May 2016 15:46]
MySQL Verification Team
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.32 Source distribution PULL: 2016-MAY-09
Copyright (c) 2000, 2016, 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 test
Database changed
mysql 5.6 > DROP TABLE IF EXISTS `test1`;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql 5.6 > CREATE TABLE `test1` (
-> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> `fkey1` INT NOT NULL,
-> `key1` INT NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `TEST1_FKEY1` (`fkey1`)
-> );
ERROR 1050 (42S01): Table 'test1' already exists
mysql 5.6 >
mysql 5.6 > DROP TABLE IF EXISTS `test2`;
Query OK, 0 rows affected (0.17 sec)
mysql 5.6 > CREATE TABLE `test2` (
-> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> `fkey2` INT NOT NULL,
-> `key2` INT NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `TEST2_FKEY2` (`fkey2`),
-> CONSTRAINT `TEST12_FKEY12` FOREIGN KEY (`fkey2`) REFERENCES `test1` (`fkey1`) ON DELETE CASCADE
-> );
Query OK, 0 rows affected (0.45 sec)
mysql 5.6 >
mysql 5.6 > SET SQL_MODE='';
Query OK, 0 rows affected (0.00 sec)
mysql 5.6 > SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
mysql 5.6 > SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.00 sec)
mysql 5.6 >
mysql 5.6 > alter table test2 drop key `TEST2_FKEY2`;
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql 5.6 > alter table test2 ADD INDEX `TEST2_FKEY2_KEY2` (`fkey2`, `key2`);
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql 5.6 > exit
Bye
C:\dbs>57
C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.14 Source distribution PULL: 2016-MAY-09
Copyright (c) 2000, 2016, 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 > DROP TABLE IF EXISTS `test1`;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql 5.7 > CREATE TABLE `test1` (
-> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> `fkey1` INT NOT NULL,
-> `key1` INT NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `TEST1_FKEY1` (`fkey1`)
-> );
ERROR 1050 (42S01): Table 'test1' already exists
mysql 5.7 >
mysql 5.7 > DROP TABLE IF EXISTS `test2`;
Query OK, 0 rows affected (0.19 sec)
mysql 5.7 > CREATE TABLE `test2` (
-> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> `fkey2` INT NOT NULL,
-> `key2` INT NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `TEST2_FKEY2` (`fkey2`),
-> CONSTRAINT `TEST12_FKEY12` FOREIGN KEY (`fkey2`) REFERENCES `test1` (`fkey1`) ON DELETE CASCADE
-> );
Query OK, 0 rows affected (0.43 sec)
mysql 5.7 >
mysql 5.7 > SET SQL_MODE='';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.7 > SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
mysql 5.7 > SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.00 sec)
mysql 5.7 >
mysql 5.7 > alter table test2 drop key `TEST2_FKEY2`;
ERROR 1553 (HY000): Cannot drop index 'TEST2_FKEY2': needed in a foreign key constraint
mysql 5.7 > alter table test2 ADD INDEX `TEST2_FKEY2_KEY2` (`fkey2`, `key2`);
Query OK, 0 rows affected (0.87 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql 5.7 >
[25 May 2016 15:53]
MySQL Verification Team
Thank you for the bug report.
c:\dbs\mysql-5.7.9>bin\mysql -uroot -p test
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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> DROP TABLE IF EXISTS `test1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `test1` (
-> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> `fkey1` INT NOT NULL,
-> `key1` INT NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `TEST1_FKEY1` (`fkey1`)
-> );
Query OK, 0 rows affected (0.33 sec)
mysql>
mysql> DROP TABLE IF EXISTS `test2`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `test2` (
-> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> `fkey2` INT NOT NULL,
-> `key2` INT NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `TEST2_FKEY2` (`fkey2`),
-> CONSTRAINT `TEST12_FKEY12` FOREIGN KEY (`fkey2`) REFERENCES `test1` (`fkey1`) ON DELETE CASCADE
-> );
Query OK, 0 rows affected (0.38 sec)
mysql>
mysql> SET SQL_MODE='';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> alter table test2 drop key `TEST2_FKEY2`;
ERROR 1553 (HY000): Cannot drop index 'TEST2_FKEY2': needed in a foreign key constraint
mysql> alter table test2 ADD INDEX `TEST2_FKEY2_KEY2` (`fkey2`, `key2`);
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
[20 Jun 2016 4:04]
Aakanksha Verma
Posted by developer: This is regarding BPS bug : http://clustra.no.oracle.com/orabugs/bug.php?id=23347100, its basically a regression due to the following commit by Thirunarayanan B: Author: Thirunarayanan B <thirunarayanan.balathandayuth@oracle.com> 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) The Fix by Thirunarayanan B is a documented behavior added to mysql-5.7.5 which is " Don't allow to drop an index if it is needed for an FK constraint even if foreign_key_checks is disabled." So I would like to suggest the reporter to try this workaround test script for his upgrade scheme and i.e DROP TABLE IF EXISTS `test1`; CREATE TABLE `test1` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `fkey1` INT NOT NULL, `key1` INT NOT NULL, PRIMARY KEY (`id`), KEY `TEST1_FKEY1` (`fkey1`) ); DROP TABLE IF EXISTS `test2`; CREATE TABLE `test2` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `fkey2` INT NOT NULL, `key2` INT NOT NULL, PRIMARY KEY (`id`), KEY `TEST2_FKEY2` (`fkey2`), CONSTRAINT `TEST12_FKEY12` FOREIGN KEY (`fkey2`) REFERENCES `test1` (`fkey1`) ON DELETE CASCADE ); SET SQL_MODE=''; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; alter table test2 ADD INDEX `TEST2_FKEY2_KEY2` (`fkey2`, `key2`); SHOW CREATE TABLE test2; SHOW CREATE TABLE test1; SET SQL_MODE=IFNULL(@OLD_SQL_MODE,''); SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS=0, 0, 1); DROP TABLE test2; DROP TABLE test1;
[20 Jun 2016 4:04]
Aakanksha Verma
Posted by developer: This is regarding BPS bug : http://clustra.no.oracle.com/orabugs/bug.php?id=23347100, its basically a regression due to the following commit by Thirunarayanan B: Author: Thirunarayanan B <thirunarayanan.balathandayuth@oracle.com> 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) The Fix by Thirunarayanan B is a documented behavior added to mysql-5.7.5 which is " Don't allow to drop an index if it is needed for an FK constraint even if foreign_key_checks is disabled." So I would like to suggest the reporter to try this workaround test script for his upgrade scheme and i.e DROP TABLE IF EXISTS `test1`; CREATE TABLE `test1` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `fkey1` INT NOT NULL, `key1` INT NOT NULL, PRIMARY KEY (`id`), KEY `TEST1_FKEY1` (`fkey1`) ); DROP TABLE IF EXISTS `test2`; CREATE TABLE `test2` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `fkey2` INT NOT NULL, `key2` INT NOT NULL, PRIMARY KEY (`id`), KEY `TEST2_FKEY2` (`fkey2`), CONSTRAINT `TEST12_FKEY12` FOREIGN KEY (`fkey2`) REFERENCES `test1` (`fkey1`) ON DELETE CASCADE ); SET SQL_MODE=''; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; alter table test2 ADD INDEX `TEST2_FKEY2_KEY2` (`fkey2`, `key2`); SHOW CREATE TABLE test2; SHOW CREATE TABLE test1; SET SQL_MODE=IFNULL(@OLD_SQL_MODE,''); SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS=0, 0, 1); DROP TABLE test2; DROP TABLE test1;
[24 Jun 2016 7:47]
Aakanksha Verma
Verified that bug#81595 is not a regression. It caused by Fix of bug#70260 (external bug of bug#17449901) Fix of bug#70260 is a documented behaviour "As of 5.7.5, dropping an index required by a foreign key constraint is not permitted, even when foreign_key_checks=0. The foreign key constraint must be removed before dropping the index" So Would like to suggest to try this new workaround script and i.e mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> CREATE TABLE `test1` ( -> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, -> `fkey1` INT NOT NULL, -> `key1` INT NOT NULL, -> PRIMARY KEY (`id`), -> KEY `TEST1_FKEY1` (`fkey1`) -> ); Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE `test2` ( -> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, -> `fkey2` INT NOT NULL, -> `key2` INT NOT NULL, -> PRIMARY KEY (`id`), -> KEY `TEST2_FKEY2` (`fkey2`), -> CONSTRAINT `TEST12_FKEY12` FOREIGN KEY (`fkey2`) REFERENCES `test1` -> (`fkey1`) ON DELETE CASCADE -> ); Query OK, 0 rows affected (0.06 sec) mysql> SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; Query OK, 0 rows affected (0.00 sec) mysql> SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> alter table test2 ADD INDEX `TEST2_FKEY2_KEY2` (`fkey2`, `key2`), algorithm=inplace; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test2; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test2 | CREATE TABLE `test2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `fkey2` int(11) NOT NULL, `key2` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `TEST2_FKEY2` (`fkey2`), KEY `TEST2_FKEY2_KEY2` (`fkey2`,`key2`), CONSTRAINT `TEST12_FKEY12` FOREIGN KEY (`fkey2`) REFERENCES `test1` (`fkey1`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table test2 drop key `TEST2_FKEY2`; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test2; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test2 | CREATE TABLE `test2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `fkey2` int(11) NOT NULL, `key2` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `TEST2_FKEY2_KEY2` (`fkey2`,`key2`), CONSTRAINT `TEST12_FKEY12` FOREIGN KEY (`fkey2`) REFERENCES `test1` (`fkey1`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) **NOTE It will achieve the same whatever user wants to achieve..
