Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
5.7.32-35, 8.0.22-13
-
None
-
None
Description
Replication crashes with the following error when executing an UPDATE statement in the source(where the table does not have a PK) and the replica has a PK.
Last_SQL_Errno: 1032 Last_SQL_Error: Could not execute Update_rows event on table percona.test_table; Can't find record in 'test_table', Error_code: 1032; Can't find record in 'test_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000003, end_log_pos 29043405
The same does not occur with INSERT statements.
The binlog_format is set to ROW and using the FULL image.
Test case:
1-) create table in the source
CREATE TABLE `test_table` (
`id` int(10) NOT NULL DEFAULT '0',
`field1` varchar(100),
KEY `idx` (`id`,`field1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2-) Insert some rows, check the source and replica
insert into test_table values (1,'11/19'),(1,'11/19'),(1,'11/2019') on duplicate key update field1=values(field1), id=values(id);
master [localhost:48008] {msandbox} (percona) > select * from test_table;
-----------+
id | field1 |
-----------+
1 | 11/19 |
1 | 11/19 |
1 | 11/2019 |
-----------+
3 rows in set (0.00 sec)
slave1 [localhost:48009] {msandbox} (percona) > select * from test_table;
-----------+
id | field1 |
-----------+
1 | 11/19 |
1 | 11/19 |
1 | 11/2019 |
-----------+
3 rows in set (0.00 sec)
3-) Add a PK on the replica
slave1 [localhost:48009] {msandbox} (percona)> ALTER TABLE `test_table` ADD COLUMN `id_pk` BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY ;
4-) Insert more rows, check both servers:
insert into test_table values (1,'11/19'),(1,'11/19'),(1,'11/2019') on duplicate key update field1=values(field1), id=values(id);
master [localhost:48008] {msandbox} (percona) > select * from test_table;
-----------+
id | field1 |
-----------+
1 | 11/19 |
1 | 11/19 |
1 | 11/19 |
1 | 11/19 |
1 | 11/2019 |
1 | 11/2019 |
-----------+
6 rows in set (0.00 sec)
slave1 [localhost:48009] {msandbox} (percona) > select * from test_table;
----------------
id | field1 | id_pk |
----------------
1 | 11/19 | 1 |
1 | 11/19 | 2 |
1 | 11/19 | 4 |
1 | 11/19 | 5 |
1 | 11/2019 | 3 |
1 | 11/2019 | 6 |
----------------
6 rows in set (0.01 sec)
5-) Run update on the source server
master [localhost:48008] {msandbox} (percona) > update test_table set id=1,field1='11/2019';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 6 Changed: 4 Warnings: 0
6-) Check replication and its broken
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table percona.test_table; Can't find record in 'test_table', Error_code: 1032; Can't find record in 'test_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000003, end_log_pos 29043405