| Bug #70531 | Can not drop InnoDB database with foreign keys without turning off fk checking | ||
|---|---|---|---|
| Submitted: | 5 Oct 2013 11:08 | Modified: | 10 Apr 2019 22:20 |
| Reporter: | Justin Swanhart | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S1 (Critical) |
| Version: | 5.5.32,5.5.34, 5.6.14, 5.7.2 | OS: | Any (5.5.32-ubuntu) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | constraints, DDL, innodb | ||
[5 Oct 2013 16:44]
Valeriy Kravchuk
I can not reproduce this with simple test case:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.33 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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 database db1;
Query OK, 1 row affected (0.09 sec)
mysql> use db1;
Database changed
mysql> create table t1(id int primary key) engine=InnoDB;
Query OK, 0 rows affected (0.81 sec)
mysql> create table t2(id int primary key, t1_id int, constraint c1 foreign key(
t1_id) references t1(id) on delete cascade) engine=InnoDB;
Query OK, 0 rows affected (0.34 sec)
mysql> alter table t1 add column t2_id int;
Query OK, 0 rows affected (0.91 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t1 values(1,1), (2,2);
Query OK, 2 rows affected (0.14 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t2 values(1,1), (2,2);
Query OK, 2 rows affected (0.40 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> alter table t1 add constraint c2 foreign key(t2_id) references t2(id) on
delete cascade;
Query OK, 2 rows affected (0.78 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`t2_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c2` (`t2_id`),
CONSTRAINT `c2` FOREIGN KEY (`t2_id`) REFERENCES `t2` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.15 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`t1_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c1` (`t1_id`),
CONSTRAINT `c1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
mysql> drop table t1;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> drop table t2;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> use test
Database changed
mysql> drop database db1;
Query OK, 2 rows affected (0.29 sec)
So it seems drop database is smart enough already to drop tables without foreign key checks, even when foreign key references are circular.
It seems something else also happened in that real case you refer to.
[6 Oct 2013 6:33]
MySQL Verification Team
Hello Justin, Thank you for the bug report. Verified as described. Thanks, Umesh
[6 Oct 2013 6:36]
MySQL Verification Team
This behavior can be reproduced by creating a cross database foreign key relation.
// Simplified test case
create database d1;
create database d2;
use d1
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`int_id` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `int_id` (`int_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
use d2
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ext_id` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `ext_id` (`ext_id`),
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`ext_id`) REFERENCES `d1`.`t1` (`int_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
select count(*) from information_schema.tables where table_schema='d1';
drop database d1;
// Can be reproduced on 5.7.2-m12-enterprise-commercial-advanced / 5.6.14 / 5.5.34 / 5..7.2-m12
mysql>
mysql> use d1
Database changed
mysql>
mysql> CREATE TABLE `t1` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `int_id` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `int_id` (`int_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.04 sec)
mysql> use d2
Database changed
mysql>
mysql> CREATE TABLE `t2` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `ext_id` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `ext_id` (`ext_id`),
-> CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`ext_id`) REFERENCES `d1`.`t1` (`int_id`) ON DELETE CASCADE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> select count(*) from information_schema.tables where table_schema='d1';
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> drop database d1;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
[7 Apr 2019 19:48]
Dmitry Lenev
Posted by developer:
Hello!
The most likely scenario for such problem to occur is when you have
an InnoDB table which references/is a child for other tables in the
same database through the foreign keys, but in its turn is referenced by/
is a parent for some table outside this database.
In this case dropping the database can drop parent tables for our
table first (because we allow dropping of parent tables as part of
DROP DATABASE if their child table belongs to the same database) and
then fail to drop child table because it is also serves as parent
for table outside of database being dropped. As result we will get
orphan table after attempt to DROP DATABASE.
The good news are that this issue was solved in 8.0.12 release by the
following fix:
===
Date: Tue Apr 10 13:07:52 2018 +0300
Bug#27821060 "NEWDD FK: DROP TABLES/DATABASE SHOULD CHECK FOR FKS".
After we have started to store information about foreign keys in the
New Data Dictionary it became possible to move checks of foreign key
definition validity from the storage engine to SQL-layer and thus
reduce code duplication and simplify SE implementation.
This patch moves check that disallows dropping of parent table in
a foreign key without prior dropping of child table from InnoDB
SE to SQL-layer code implementing DROP TABLES/DROP DATABASE.
Such check now happens before trying to delete any tables mentioned
in DROP TABLES statement/belonging to schema to be dropped. So DROP
TABLES/DROP DATABASE no longer have any side-effect even on tables
in SEs which don't support atomic DDL when they fail due to this check.
...
===
So now the error about attempt to drop database one of which tables is referenced
by a table outside of this database will be reported before trying to delete any
tables.
Moreover, the problem should not be repeatable in earlier releases in 8.0 branch
as well. Starting from 8.0.3 we support atomic DROP DATABASE for InnoDB-only
databases which means that attempt of DROP DATABASE to delete InnoDB table which
is referenced by table from other database will fail and cause rollback of the
whole statement.
I am moving this bug to Documenting to let Documentation Team to decide if
they want to mention this bug in release notes for 8.0.12.
[10 Apr 2019 22:20]
Paul DuBois
Posted by developer: Fixed in 8.0.12. Checking for foreign key relationships by DROP TABLE and DROP DATABASE was improved. Parent and child tables now can be dropped in arbitrary order, as long as they are dropped by the same DROP TABLE statement. In addition, error reporting was improved for attempts to drop a parent table without dropping a child table.
Description: When attempting to drop a database populated with data, InnoDB fails to drop one of the tables with an FK error. It is not possible to drop the database without turning off FK checks. An orphaned table with constraints that can not be satisfied remains in the schema. I consider this corruption as the table can not be dropped without setting foreign_key_checks=0 and inconsistent data is now in the database (children without parents). How to repeat: I haven't narrowed down an exact case yet, and I can't give you the table structures of all the tables as they are private. Here are reproduce steps though and a sanitized version of the table that is orphaned. mysql> select count(*) from information_schema.tables where table_schema='XXX'; +----------+ | count(*) | +----------+ | 17 | +----------+ 1 row in set (0.00 sec) mysql> drop database XXX; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails mysql> select count(*) from information_schema.tables where table_schema='XXX'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> drop database XXX; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails mysql> use XXX; Database changed mysql> show tables; +---------------+ | Tables_in_pct | +---------------+ | XXXXXX | +---------------+ 1 row in set (0.00 sec) mysql> show create table XXXXXX\G *************************** 1. row *************************** Table: XXXXXX Create Table: CREATE TABLE `XXXXXX` ( `XXXXXX_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `XXX_XX` int(10) unsigned NOT NULL, `XXXXX_config_id` int(10) unsigned NOT NULL, `XXXX` char(36) NOT NULL, `XXXXXXXh` varchar(50) NOT NULL, `XXXXXXXXXXXX` varchar(50) DEFAULT NULL, `XXX` varchar(50) DEFAULT NULL, `XXXXXX` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `XXXXX_XXXXXX_XX` timestamp NULL DEFAULT NULL, `XXX_XXXXX` timestamp NULL DEFAULT NULL, `XX` timestamp NULL DEFAULT NULL, PRIMARY KEY (`XXXXX`), KEY `xxx_xx` (`XXX_XX`), KEY `xxxxx_xxxxxx_xx` (`XXXXX_XXXXXX_XX`), CONSTRAINT `xxxxxx_ibfk_1` FOREIGN KEY (`XXX_XX`) REFERENCES `XXXX` (`XXX_XX`) ON DELETE CASCADE, CONSTRAINT `xxxxxx_ibfk_2` FOREIGN KEY (`XXXXX_XXXXX_XX`) REFERENCES `XXXXX_XXXXXXX` (`XXXXX_XXXXXX_XX`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) Suggested fix: When dropping databases, drop tables in FK dependency order or turn off FK checks during drop operations.