Description:
Replication failing with following error.
Note : Tested with & without GTID getting same error.
Last_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table testReplication.table_1; Can't find record in 'table_1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000003, end_log_pos 2171
With MTS error will look like this
Last_Errno: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000003, end_log_pos 2181. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
with system variable "binlog_row_image" causing replication break when set to "minimal" & there is no replication break seen when using these full/noblob options.
How to repeat:
please find reproducible steps below:
1. configuration to source & replica.
binlog_row_image = minimal
binlog_format = ROW
slave_parallel_type = DATABASE
slave_parallel_workers = 0
create database test_db CHARACTER SET utf8mb4;
use test_db;
CREATE TABLE `table_1` (
`ownerId` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`pageId` tinyint unsigned NOT NULL DEFAULT '1',
`param1` int unsigned DEFAULT NULL,
`param2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`needToRemove` tinyint unsigned GENERATED ALWAYS AS ((`param1` is null) and (`param2` is null)) STORED NOT NULL,
PRIMARY KEY (`ownerId`,`pageId`,`needToRemove`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='test table'
/*!50100 PARTITION BY LIST (`needToRemove`)
(PARTITION forRemove VALUES IN (1) ENGINE = InnoDB,
PARTITION main VALUES IN (0) ENGINE = InnoDB) */;
INSERT INTO `table_1` (`ownerId`, `pageId`, `param1`, `param2`) VALUES (0x93f213c750078811a5be227e832b715c, '1', 1, NULL), (0xfe218d10b6a0f75ad272ec6f76ef1e07, '1', 1, NULL) ON DUPLICATE KEY UPDATE `ownerId` = VALUES(`ownerId`), `pageId` = VALUES(`pageId`), `param1` = VALUES(`param1`), `param2` = VALUES(`param2`);
check the replica status on replica before running below cmd.
update rows by running following cmd -> This is the step where replication start failing.
INSERT INTO `table_1` (`ownerId`, `pageId`, `param1`, `param2`) VALUES (0xfe218d10b6a0f75ad272ec6f76ef1e07, '1', 1, 'test update') ON DUPLICATE KEY UPDATE `ownerId` = VALUES(`ownerId`), `pageId` = VALUES(`pageId`), `param1` = VALUES(`param1`), `param2` = VALUES(`param2`);
check replica status now you should see the mentioned error.
show replica status\G
Last_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table testReplication.table_1; Can't find record in 'table_1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000003, end_log_pos 2171
Suggested fix:
replication should not break.