MySQL Bugs: #105578: Foreign key check 0 is not honored when dropping parent table

Bug #105578	Foreign key check 0 is not honored when dropping parent table
Submitted:	15 Nov 2021 14:10	Modified:	22 Nov 2021 13:50
Reporter:	David Ducos	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have been investigating this issue: https://github.com/maxbube/mydumper/issues/469 and I found that `set foreign_key_checks=0;` is not honored in 8.0 as it is in 5.7. On 8.0 I get a Deadlock found error.

How to repeat:
Tables structure:
CREATE TABLE `parents` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB;

CREATE TABLE `children` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `myfk` FOREIGN KEY (`parent_id`) REFERENCES `parents` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB;

The test case is:
Session 1:

set foreign_key_checks=1;
DROP TABLE IF EXISTS `mydb`.`parents`; *1
set foreign_key_checks=0;
DROP TABLE IF EXISTS `mydb`.`parents`; *2
Session 2:

set foreign_key_checks=1;
select sleep(0.00001); DROP TABLE IF EXISTS `mydb`.`children`;  *1
set foreign_key_checks=0;
select sleep(0.00001); DROP TABLE IF EXISTS `mydb`.`children`;  *2
Statements with *1 and *2 are executed at the exact same time.
All the command in Session 2 will complete correctly.
In 5.7 you will get in Session 1:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
on *1 and will succeed on *2.
In 8 you will get on Session 1:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
on all the cases.

Hi Mr. Ducos,

Thank you for your bug report.

However, your report is unclear.

Simply , it is not physically possible to execute two statements within the same nanosecond. So , please just try dropping a parent table, before the dependent table. If it works as in 5.7, then this is not a bug.

Waiting on your feedback.

The idea is not to execute the statement in the same nanosecond, the idea is to simulate that the parent table is being drop before the children table.
The sequence of actions that I wanted to simulate is:
S1: DROP TABLE IF EXISTS `mydb`.`parents`;
1- MySQL Server starts DROP TABLE on parent table
S2: DROP TABLE IF EXISTS `mydb`.`children`;
2- MySQL Server starts DROP TABLE on children table
3- finish DROP TABLE parent table
4- finish DROP TABLE children table

this sequence fails on 5.7 only when foreign_key_checks is 1. In 8.0 is failing even with foreign_key_checks = 0.

Hi,

When there are no rows in the tables, then there is no reason not to drop a table.

Have you tried it with loaded table, with dependencies established between all rows in the child and parent tables ????

We are waiting on your feedback.

You can add this to the test case:
INSERT INTO `parents` VALUES (1);
INSERT INTO `children` VALUES (1,1);
and the outcome will be the same.
I will patch MyDumper to make this example simpler to show, but this is reproducible using 2 xterm session and cmd+a them to hit enter at the same time.
Where you able to reproduce it?

Hi Mr. Ducos,

We can not repeat the behaviour that you are reporting.

With both, latest 5.7 and 8.0, when foreign keys are enabled, parents table can not be dropped, while it can be dropped when foreign keys are disabled. We had no problems what so ever.

And it behaves the same whether you do it in one session or in N sessions.

We also noticed that you made an error when setting foreign checks. If you read our Reference Manual carefully, you will find the cause of your error.

Case closed.

Can't repeat.

Hello!

I'm the author of the original bug report. I can also reproduce it, and I think that several points need clarification, before closing this bug.

> Simply , it is not physically possible to execute two statements within the same nanosecond

I don't know if "nanosecond" is hyperbolic, but on a modern, idle, system, concurrent executions (which includes, MySQL client executions) should generally start within a window of a few milliseconds (can be tried via `SELECT CURTIME(6)`).

The program this problem was reported against (Mydumper/Myloader) triggers this behavior consistenly (I estimate ~90% of the times on my system). In order to perform a manual concurrent execution, any modern terminal with support for multiple terminals and synchronized input will do (my tests have been performed on Linux+Tilix).

If you don't have a terminal with such capabilities available, just take advantage of shell forking:

```sh
# Run `SET GLOBAL FOREIGN_KEY_CHECKS = 0` before.

# Now run this:
#
mysql tmp -e 'DROP TABLE IF EXISTS `parents`' & mysql tmp -e 'SELECT SLEEP(0.00001); DROP TABLE IF EXISTS `children`'

# Output:
#
#   [1] 223330
#   +----------------+
#   | SLEEP(0.00001) |
#   +----------------+
#   |              0 |
#   +----------------+
#   ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try restarting transaction
#   [1]  + 223330 exit 1     mysql tmp -e 'DROP TABLE IF EXISTS `parents`'
```

> When there are no rows in the tables, then there is no reason not to drop a table.

Programs like Myloader, or mysqldump/mysqlpump (which add `DROP TABLE`, if requested, in the output) drop the table(s) blindly, as they can't assume that, at restore time, the table structure is the same.

In application development, it's common to (programmatically) drop tables blindly when switching across branches that may have changed the underlying data structure.

On these grounds, the test case without rows is a real-world and valid one. It also makes the test more reproducible (in my personal tests, adding rows lowered the occurrence rate from around 80% to 30/40%, likely due to either a different codepath, or the delay introduced by deleting the rows).

> We also noticed that you made an error when setting foreign checks. If you read our Reference Manual carefully, you will find the cause of your error.

If the error is in `CONSTRAINT myfk FOREIGN KEY (parent_id) REFERENCES parents (id) ON DELETE RESTRICT ON UPDATE RESTRICT`, this is my fault, however, it doesn't affect the problem.

When creating the constraint separately, as:

```sql
ALTER TABLE `children` ADD FOREIGN KEY (`parent_id`) REFERENCES `parents`(`id`);
```

the problem will reproduce the same way.

I've also set the FK globally rather than per session (`SET GLOBAL FOREIGN_KEY_CHECKS = 0`, before initiating the two sessions), and again, the problem reproduces the same way.

========================================

With the considerations above, it should be easy to reproduce the problem, if it wasn't possible before. Super-easy version:

```sh
mysql -e '
  DROP SCHEMA IF EXISTS tmp;
  CREATE SCHEMA tmp;

  USE tmp;

  CREATE TABLE parents (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY
  );

  CREATE TABLE children (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    parent_id int NOT NULL
  );

  ALTER TABLE children ADD FOREIGN KEY (parent_id) REFERENCES parents(id);
'

mysql tmp -e 'DROP TABLE IF EXISTS parents' & mysql tmp -e 'SELECT SLEEP(0.00001); DROP TABLE IF EXISTS children'
```

Hi,

Your analysis has totally excluded the effect of MDLs , which prevents any possibility of the parallel execution of DDL on related tables.

This is all explained in our Reference Manual.

Ok, I found the reference in the Manual https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#foreign-key-locking 

What I still don't fully understand is why the foreign key check = 0 is not honored. I understand that you have added a new locking mechanism for this particular scenario, but it caused a change in how MySQL behaves between versions. Now, we should LOCK TABLES or ignore the foreign keys on table recreation to avoid deadlocks.