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
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