Description:
When performing data operations on the foreign key table, the secondary node may update in an inconsistent order. The foreign key conflict detection fails.
2022-08-26T02:22:17.203126-00:00 15 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_applier': Worker 2 failed executing transaction '555ebdfe-24e3-11ed-b7ae-02000aba3c2a:4443'; Could not execute Delete_rows event on table zhouqch.a; Cannot delete or update a parent row: a foreign key constraint fails (`zhouqch`.`b`, CONSTRAINT `foreign_key_b2a` FOREIGN KEY (`a`) REFERENCES `a` (`a`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED, Error_code: MY-001451
Node centos7-3 was removed from the cluster;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 5c6cbb55-245c-11ed-8bf6-02000aba3c2a | centos7-1 | 3306 | ONLINE | PRIMARY | 8.0.30 | MySQL |
| group_replication_applier | 7fa4b1ea-245c-11ed-a215-02000aba3c1d | centos7-2 | 3306 | ONLINE | SECONDARY | 8.0.30 | MySQL |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.01 sec)
How to repeat:
Table structure;
create table zhouqch.a
(
a int,
b char(10),
c char(10),
primary key (a,b,c)
);
create table zhouqch.b
(
a int,
b char(10),
c char(10),
primary key (a,b,c),
CONSTRAINT foreign_key_b2a FOREIGN KEY (a) REFERENCES a (a)
);
Script contents:
[root@centos7-1 opt]# cat 2.sh
#bin/sh
count=$1
i=0
while true
do
let i=i+1
mysql -uzhouqch -pZhouqch@123 zhouqch -e"insert into a (a,b,c) values ($i,2,2);insert into b (a,b,c) values ($i,2,2);"
mysql -uzhouqch -pZhouqch@123 zhouqch -e"delete from b where a=$i;delete from a where a=$i;"
echo $i
if [ $i -gt $count ]
then
exit 0;
fi
done
[root@centos7-1 opt]#
Perform 1000 insert and delete operations:
[root@centos7-1 opt]# sh 2.sh 1000