Running parallel TRUNCATE causing foreign key constraint fails on INSERT

mysql-8-bug.sh

#!/bin/bash
###################################################################
#Script Name	: mysql-8-bug.sh
#Description	: A bug with MySQL 8.0.1 - 8.0.29. Running parallel TRUNCATE causing foreign key constraint fails. Filed with MySQL team at https://bugs.mysql.com/bug.php?id=107532
#Author       : Dimitry Kudryavtsev
#Email        : [email protected]
###################################################################

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 FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE $TABLE_B; SET FOREIGN_KEY_CHECKS = 1;" &
  mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME --execute="SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE $TABLE_A; SET 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