Bug #109857 Truncate table with foreign key crash replication.
Submitted: 31 Jan 2023 8:55 Modified: 3 Feb 2023 4:54
Reporter: Fengchun Hua Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.7.38 OS:Any
Assigned to: CPU Architecture:Any
Tags: foreign key, Replicaiton

[31 Jan 2023 8:55] Fengchun Hua
Description:
Here's two example tables, which has foreign key.

CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  KEY `fk_1` (`id`),
  CONSTRAINT `fk_1` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  KEY `t2_key` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 
execute the following sql by two sessions in parallel.

1. truncate test.t1
2. insert into test.t2 values(1); insert into test.t1 values(1); delete from test.t2;

Slave may report error:
 Could not execute Delete_rows event on table test.t2; Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `fk_1` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)), Error_code: 1451; handle
r error HA_ERR_ROW_IS_REFERENCED; the event's master log binlog.000001, end_log_pos 175211

slave stop here:
Executed_Gtid_Set: 166a2a40-a119-11ed-b2f3-fa163e207810:1-782

I read the binlog, and see:

SET @@SESSION.GTID_NEXT= '166a2a40-a119-11ed-b2f3-fa163e207810:781'/*!*/;
### INSERT INTO `test`.`t2`

SET @@SESSION.GTID_NEXT= '166a2a40-a119-11ed-b2f3-fa163e207810:782'/*!*/;
### INSERT INTO `test`.`t1`

SET @@SESSION.GTID_NEXT= '166a2a40-a119-11ed-b2f3-fa163e207810:783'/*!*/;
### DELETE FROM `test`.`t2`

SET @@SESSION.GTID_NEXT= '166a2a40-a119-11ed-b2f3-fa163e207810:784'/*!*/;
truncate test.t1 

Master can execute these transactions, that means truncate is executed before delete, but binlog record them in wrong order.

How to repeat:
See above.

Suggested fix:
I know transaction 783 and 784 are executed in different tables, and the order is not guaranteed. 

Can we add some row locks for t1 records which reference (t2) is being deleting, and truncate wait if there exists such row locks.
[31 Jan 2023 9:05] Fengchun Hua
Or truncate should lock both t1 and t2;
[31 Jan 2023 9:35] Fengchun Hua
Can't reproduce in 8.0.25
[3 Feb 2023 4:54] MySQL Verification Team
Hello Fengchun Hua,

Thank you for the report and test case.
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