Bug #102777 A replace statement with more than 2 unique values cause breaking replication

• Submitted: 2 Mar 2021 8:36
• Modified: 2 Mar 2021 10:38
• Reporter: wang qiang
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.22
• OS: Linux
• CPU Architecture: Any

[2 Mar 2021 8:36] wang qiang

Description:
my environment is as follows:
verison: 5.7.22-log MySQL Community Server (GPL)
tx_isolation: REPEATABLE-READ 
binlog_format: ROW

table structure:
CREATE TABLE `unique_test` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  UNIQUE KEY `a` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SQL case 1：
replace into unique_test values(1,1,1);
result: replication is ok

SQL case 2：
replace into unique_test values(1,1,1),(1,1,2);
result: replication is ok

SQL case 3：
replace into unique_test values(1,1,1),(1,1,2),(1,1,3);
Query OK, 5 rows affected (0.00 sec)
Records: 3  Duplicates: 2  Warnings: 0

result: replication is bad
Last_SQL_Error: Could not execute Update_rows event on table dba_workspace.unique_test; Can't find record in 'unique_test', Error_code: 1032; Can't find record in 'unique_test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000027, end_log_pos 33177

SQL case 4:
replace into unique_test values(1,1,1),(1,1,2),(1,1,3),(1,1,4);
result: same as case 4.

How to repeat:
an replace statement with unique key values that has more than 2, it will always cause breaking replication.

[2 Mar 2021 10:38] MySQL Verification Team

Hi,

Thanks for the report. Verified as stated on 5.7 and 8.0 too

kind regards
Bogdan

                   Last_Errno: 1032
                   Last_Error: Could not execute Update_rows event on table test.unique_test; Can't find record in 'unique_test', Error_code: 1032; Can't find record in 'unique_test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000006, end_log_pos 1423
...
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows event on table test.unique_test; Can't find record in 'unique_test', Error_code: 1032; Can't find record in 'unique_test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000006, end_log_pos 1423