Bug #108291 There is a problem for foreign key in Single-Primary Mode MGR environment
Submitted: 26 Aug 2022 2:55 Modified: 6 Sep 2022 14:20
Reporter: qichao zhou Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Group Replication Severity:S3 (Non-critical)
Version:8.0.30 OS:Any
Assigned to: CPU Architecture:Any

[26 Aug 2022 2:55] qichao zhou
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
[6 Sep 2022 12:22] MySQL Verification Team
Hello qichao zhou,

Thank you for the report.

regards,
Umesh
[6 Sep 2022 14:20] Nuno Carvalho
Duplicate of Bug#97836: In group replication foreign key will break slave aplier