Bug #99232 Cross schema foreign key blocks schema drop while queries are running
Submitted: 11 Apr 2020 6:57 Modified: 13 Apr 2020 13:17
Reporter: Andre Scheidtmann Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:8.0.17 OS:Debian (Debian 18.04 and Windows 10)
Assigned to: CPU Architecture:Any

[11 Apr 2020 6:57] Andre Scheidtmann 
Description:
Hi,

in case of a cross schema foreign key I can´t drop the schema where the foreign key points to while running a long query on the schema where the foreign key exists.

I get this behaviour on debian 18.04. and Windows 10 running MySQL Version 8.0.17. On MySQL 5.7.27 I can drop the schema instantly.

Greetings
Andre

How to repeat:
SET FOREIGN_KEY_CHECKS=0;
DROP DATABASE IF EXISTS db1;
DROP DATABASE IF EXISTS db2;
SET FOREIGN_KEY_CHECKS=1;

CREATE DATABASE db1;
CREATE DATABASE db2;

CREATE TABLE db1.t1 (ID INT PRIMARY KEY);
CREATE TABLE db2.t2 (ID INT PRIMARY KEY);

ALTER TABLE db2.t2 ADD CONSTRAINT TestForeignKey FOREIGN KEY (ID) REFERENCES db1.t1 (ID) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO db1.t1 VALUES (1);
INSERT INTO db2.t2 VALUES (1);

SELECT ID,SLEEP(600) FROM db2.t2;

Open second console window:

SET FOREIGN_KEY_CHECKS=0;
DROP DATABASE db1;

Processlist shows "Waiting for table metadata lock" for "DROP DATABASE db1"
[13 Apr 2020 13:17] MySQL Verification Team 
Hi Mr. Scheidtmann,

Thank you for your bug report.

I have carefully analysed your report and I must inform you that this is not a bug.

Simply, referential integrity has to be enforced, which is unrelated to the setting of the foreign key checks.

When you have a foreign key in one table, pointing to the key in the parent table, then you have to drop lowest relation in the hierarchy, in your case table db2.t2, before you can drop parent table, in your case db1.t1. 

This is obligatory enforcement in the ACID engines, like InnoDB. It is explain in several chapters in our Reference Manual, including the chapter 16.11.2. Since dropping tables or schemas involves data dictionary, then metadata locking has to be involved, which are at SQL lever, not at storage level.

Not a bug.