Bug #102274 SLAVE stops with HA_ERR_KEY_NOT_FOUND when slave_preserve_commit_order=ON.
Submitted: 18 Jan 12:48 Modified: 19 Jan 9:01
Reporter: zhijun long Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:8.0.17/8.0.20/8.0.*, 8.0.22, 5.7.32 OS:Any
Assigned to: CPU Architecture:Any

[18 Jan 12:48] zhijun long
Description:
Description:

SLAVE stops with HA_ERR_KEY_NOT_FOUND when slave_preserve_commit_order=ON.

Error:

2021-01-18T12:48:42.538494Z 16 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 4 failed executing transaction '5b9b7dae-598b-11eb-a91d-3c15fb5cc47b:2007' at master log master-bin.000001, end_log_pos 601804; Could not execute Update_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 601804, Error_code: MY-001032
2021-01-18T12:48:42.538510Z 14 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 2 failed executing transaction '5b9b7dae-598b-11eb-a91d-3c15fb5cc47b:2005' at master log master-bin.000001, end_log_pos 600902; Could not execute Update_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 600902, Error_code: MY-001032
2021-01-18T12:48:42.543109Z 12 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 600031

How to repeat:
test_bug.test

--source include/master-slave.inc
--connection master
CREATE TABLE `t1` (
  `sys_doc` decimal(12,0) NOT NULL,
  `sys_del` decimal(1,0) NOT NULL DEFAULT '0',
  `a_s` decimal(2,0) DEFAULT '0',
  `a_p` datetime DEFAULT NULL,
  `a_h` decimal(3,0) DEFAULT '0',
  `class_1` decimal(38,0) DEFAULT NULL,
  KEY `IDX01_T` (`class_1`) USING BTREE,
  KEY `IDX02_T` (`class_1`,`sys_del`) USING BTREE,
  KEY `IDX03_T` (`sys_doc`) USING BTREE,
  KEY `IDX04_T` (`a_s`) USING BTREE
) ENGINE=InnoDB;

CREATE TABLE `t2` (
  `sys_doc` decimal(12,0) NOT NULL,
  `sys_del` decimal(1,0) NOT NULL DEFAULT '0',
  `a_s` decimal(2,0) DEFAULT '0',
  `a_p` datetime DEFAULT NULL,
  `a_c` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `a_h` decimal(3,0) DEFAULT '0',
  PRIMARY KEY (`sys_doc`) USING BTREE,
  KEY `IDX01_T` (`a_p`) USING BTREE,
  KEY `IDX02_T` (`sys_del`) USING BTREE
) ENGINE=InnoDB;

DELIMITER ||;
CREATE TRIGGER auto_syn_update1 AFTER update ON t2 FOR EACH ROW
begin
  if (Old.a_c = New.a_c) then
    if (Old.a_s <> New.a_s or Old.a_p <> New.a_p
      or Old.sys_del <> New.sys_del or Old.a_h <> New.a_h) then
      update t1 set
        a_s=New.a_s,
        a_p=New.a_p,
        sys_del=New.sys_del,
        a_h=New.a_h
        where sys_doc=New.sys_doc;
    end if;
  end if;
end||
DELIMITER ;||

#---- insert -----
delimiter $$;
create procedure insert_data(in num int)
begin
declare i int;
set i = 1;
while i < num do
insert into test.t1(sys_doc) values(i);
insert into test.t2(sys_doc, a_c) values(i, 'aaa');
set i = i +1;
end while;
end $$
delimiter ;$$
call insert_data(1000);

#---- update -----
delimiter $$;
create procedure update_data(in start int,in end int)
begin
declare i int;
set i = start;
while i < end do
update test.t2 set a_s=6 where sys_doc=i;
set i = i +1;
end while;
end $$
delimiter ;$$

--sync_slave_with_master
connection slave;
show variables like "slave_parallel_workers";
show variables like "slave_preserve_commit_order";

connection master;
connect(con1,localhost,root,,);
connect(con2,localhost,root,,);
connect(con3,localhost,root,,);
connect(con4,localhost,root,,);
connection con1;
send call update_data(1,250);
connection con2;
send call update_data(251,500);
connection con3;
send call update_data(501,750);
connection con4;
send call update_data(751,1000);

connection con1;
reap;
connection con2;
reap;
connection con3;
reap;
connection con4;
reap;

drop table t1,t2;
drop procedure update_data;
drop procedure insert_data;
--source include/rpl_end.inc

test_bug-master.opt

--gtid-mode=on
--enforce-gtid-consistency=1
--log-slave-updates=1

test_bug-slave.opt

--slave_parallel_workers=8
--slave_preserve_commit_order=on
--slave_parallel_type=LOGICAL_CLOCK
--gtid-mode=on
--enforce-gtid-consistency=1
--log-slave-updates=1
[19 Jan 9:01] MySQL Verification Team
Hello zhijun long,

Thank you for the report and feedback.

regards,
Umesh