Bug #107532 running parallel TRUNCATE causing foreign key constraint fails on INSERT
Submitted: 9 Jun 20:21 Modified: 14 Jun 12:04
Reporter: DIMITRY KUDRYAVTSEV (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:8.0 OS:Ubuntu (debian docker)
Assigned to: CPU Architecture:Any
Tags: foreign key, Foreign Key Constraints, truncate

[9 Jun 20:21] DIMITRY KUDRYAVTSEV
Description:
Is there something I'm missing or does this seems like a bug with MySQL 8, tried the latest version 8.0.29 and other versions in between. This used to work in 5.7 without issues. Luckily, I was able to reproduce this in bash but we encountered it within our testing setup. It seems like you can get a state where a table has record with the correct id for the foreign key constraint but MySQL still throws an error.

Basic setup:

Create table A
Create table B, this has a foreign key constraint on id of table A
parallel TRUNCATE table A and table B (disable the session FOREIGN_KEY_CHECKS)
insert into table A
insert into table B <-- this will fail, not always but if you run it 100 times it will fail with foreign key constraint error.

How to repeat:
Here is the code to reproduce it. This will put MySQL into a bad state, you can even connect to the database and try inserting and it will fail.

#!/bin/bash
DB_USER='root';
DB_PASSWD='root';

DB_NAME='mysql_8_bug';
TABLE_A='a';
TABLE_B='b';

set -m

#setup
mysql --user=$DB_USER --password=$DB_PASSWD << EOF
CREATE DATABASE IF NOT EXISTS $DB_NAME DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
EOF

mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME << EOF
DROP TABLE IF EXISTS $TABLE_A, $TABLE_B;
EOF

mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME << EOF
CREATE TABLE $TABLE_A (id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
EOF

mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME << EOF
CREATE TABLE $TABLE_B
(id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
int_a_id BIGINT(20) UNSIGNED DEFAULT NULL,
CONSTRAINT fk_a_id FOREIGN KEY (int_a_id) REFERENCES a (id));
EOF
#end of setup

for i in {0..100}
do

#run truncate in parallel
  mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME --execute="SET @@session.FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE $TABLE_A; SET @@session.FOREIGN_KEY_CHECKS = 1;" &
  mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME --execute="SET @@session.FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE $TABLE_B; SET @@session.FOREIGN_KEY_CHECKS = 1;" &
  wait

  mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME --execute="INSERT INTO $TABLE_A (id) VALUES (1);"

  mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME --execute="INSERT INTO $TABLE_B (int_a_id) VALUES (1);"
  if [ $? -gt 0 ]
  then
  echo "It Happened!!! You can now check the database you will see that table a has value of id 1. The database will be in a bad state, running INSERT in table b will not work."
  exit 0
  fi

done
[9 Jun 22:01] DIMITRY KUDRYAVTSEV
Changing the category to InnoDB storage engine
[10 Jun 12:16] MySQL Verification Team
Hi Mr. Kudryavtsev,

Thank you for your bug report.

However we need some additional info.

You have added `wait` shell command in your script. Is it truly necessary and for which PID is it waiting for ???

We are waiting on your feedback.
[10 Jun 14:06] Bill Karwin
In bash, wait invoked with no arguments waits for all child processes to finish.

I tested the script and I confirm it does reproduce the failure. I tested on MacOS 12.3.1 with MySQL 8.0.29 compiled for the ARM processor.

The failure occurs after a variable number of iterations of the loop. Sometimes it gets through all 100 iterations with no failure. Sometimes it happens within 10 iterations.
[10 Jun 14:22] MySQL Verification Team
Thank you, Mr. Karwin.
[10 Jun 18:59] DIMITRY KUDRYAVTSEV
Thanks Bill. I'm glad that other people were able to reproduce this in different environments. When we run our tests, we truncate about 80+ tables, that have foreign key constraints, this bug happens much more frequent than the bash script might indicate. Moreover, after the database entered the bad state none of the mysqladmin commands helped (tried flush-tables, flush-threads, etc..). The only thing that helped was truncating the tables again. It seems to me that this is a S1 or S2 bug and I hope it gets fixed soon.
[14 Jun 12:04] MySQL Verification Team
Hi Mr. Kudryavtsev,

Thank you for your bug report.

We have managed to reproduce it.

This is now a fully verified bug.