Bug #97836 In group replication foreign key will break slave aplier
Submitted: 30 Nov 2019 12:54 Modified: 5 Dec 2019 7:49
Reporter: zhang chen Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Group Replication Severity:S2 (Serious)
Version:8.0.18 OS:Linux (CentOS Linux release 7.6.1810)
Assigned to: CPU Architecture:Any (Intel(R) Xeon(R) CPU E5-2620 v4 @ 2.10GHz)
Tags: 1452;slave down

[30 Nov 2019 12:54] zhang chen
In group replication, a table has foreign keys that depend on another table. If the main database is executed successfully, the slave may execute abnormally and exit the group.

I have tried to commit_order and other configurations, but they are still unsolvable

Currently, it can only be solved by setting slave_parallel_workers to 0, but it seriously affects efficiency.

How to repeat:
create table zxc.a
	a int,
	b char(10),
	c char(10),
	primary key (a,b,c)

create table zxc.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 a shell run these:

while true
        let i=i+1
        mysql zxc -e"insert into a (a,b,c) values ($i,1,1);insert into b (a,b,c) values ($i,1,1);"

        mysql zxc -e"delete from b where a=$i;delete from a where a=$i;"
        echo $i
        if [ $i -gt $count ]
                exit 0;

It works normally on the master, but you can see mysql.err in slave like these:
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 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:12492217'; Could not execute Write_rows event on table zxc.b; Cannot add or update a child row: a foreign key constraint fails (`zxc`.`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
[3 Dec 2019 7:12] Umesh Shastry
Hello Zhang chen,

Thank you for the report and test case.
Verified as described.

[5 Dec 2019 7:49] zhang chen
Is there any way to solve this problem now?