| Bug #107254 | MySQL Group Replication slave applier breaks due to parallel replication | ||
|---|---|---|---|
| Submitted: | 10 May 2022 12:29 | Modified: | 10 May 2022 12:45 |
| Reporter: | Shubham Jaiswal | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Group Replication | Severity: | S1 (Critical) |
| Version: | MySQL 8.0.xx | OS: | Linux (Centos 7/8) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | replication foreign_keys foreign_key 8.0 slave applier break | ||
[10 May 2022 12:45]
MySQL Verification Team
Hello Shubham Jaiswal, Thank you for the report and feedback. Imho this is duplicate of Bug #97836, please see Bug #97836. Even if you feel that your issue is somewhat different, the resolution is likely to be the same. Because of this, we hope you add your comments to the original bug instead. Thank you for your interest in MySQL. regards, Umesh
Description: In a child parent relationship, If you are going to execute too many frequent trx on both of child, parent tables. Then, due to parallel execution of slave replication worker threads, sometime they got executed in an unordered way. And trx will fail on secondary nodes but that trx is successfully executed on Master Node. This is coming in all version of MySQL 8 (8.0.18 to 8.0.19, tested in all these versions). Tested in InnoDB Cluster(Single Primary) with 3 nodes in docker container and k8s PODs well. How to repeat: create table db1.a ( a int, b char(10), c char(10), primary key (a,b,c) ); create table db1.b ( a int, b char(10), c char(10), primary key (a,b,c), CONSTRAINT foreign_key_b2a FOREIGN KEY (a) REFERENCES a (a) ); use shell to run these: #bin/sh count=$1 i=0 while true do let i=i+1 mysql db1 -e"insert into a (a,b,c) values ($i,1,1);insert into b (a,b,c) values ($i,1,1);" mysql db1 -e"delete from b where a=$i;delete from a where a=$i;" echo $i if [ $i -gt $count ] then exit 0; fi done After some time you will recieve an error from one of the secondary node 2019-11-30T20:40:52.064868+08:00 335242 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_applier': Worker 2 failed executing transaction 'x-x-x-x-x:12492217'; Could not execute Write_rows event on table db1.b; Cannot add or update a child row: a foreign key constraint fails (`db1`.`b`, CONSTRAINT `foreign_key_b2a` FOREIGN KEY (`a`) REFERENCES `a` (`a`)), Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW, Error_code: MY-001452 Suggested fix: Didn't find.