Bug #111146 Write set conflict two parallel-able transaction on table with foreign key

• Submitted: 25 May 2023 8:45
• Modified: 26 May 2023 9:28
• Reporter: genze wu (OCA)
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 8.0, 8.0.36
• OS: Any
• CPU Architecture: Any

[25 May 2023 8:45] genze wu

Description:
Write set conflict two parallel-able transaction on table with foreign key.

How to repeat:
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, KEY(c2));
CREATE TABLE t2 (x1 INT PRIMARY KEY, x2 INT, FOREIGN KEY (x2) REFERENCES t1(c2));
insert into t1 values(1, 1);

set global binlog_transaction_dependency_tracking = "writeset";
insert into t2(1, 1); # This is trx1
insert into t2(2, 1); # This is trx2

# Parse the binlog, last_committed of trx2 equal to the sequence_number of trx1, but in fact they can be parallel apply on replica.

Suggested fix:
Do not record foreign key when calculate writeset in add_pke().

[26 May 2023 9:28] MySQL Verification Team

Hello genze wu,

Thank you for the report and feedback.

regards,
Umesh