Start the server with --transaction-isolation="read-committed" and/or
--innodb-locks-unsafe-for-binlog=1
Create the following table and store procedure:
---
drop table if exists t1;
create table t1(a int not null, b int not null, who int, primary key(b), unique key(a)) engine=innodb;
drop procedure if exists p1;
delimiter $
create procedure p1(me int)
l1:
begin
declare continue handler for 1062 begin end;
declare continue handler for 1213 begin end;
set @cnt_a:=null;
repeat
select count(*) cnt_a into
@cnt_a from t1 group by a having cnt_a > 1 limit 1;
if @cnt_a is not null then
select * from t1;
leave l1;
end if;
replace into t1(a,b,who) values(floor(3*rand()), floor(3*rand()), me);
until 1=2 end repeat;
end$
delimiter ;
Copying steps to reproduce from the upstream bug:
Start the server with --transaction- isolation= "read-committed " and/or locks-unsafe- for-binlog= 1
--innodb-
Create the following table and store procedure:
---
drop table if exists t1;
create table t1(a int not null, b int not null, who int, primary key(b), unique key(a)) engine=innodb;
drop procedure if exists p1; floor(3* rand()) , floor(3*rand()), me);
delimiter $
create procedure p1(me int)
l1:
begin
declare continue handler for 1062 begin end;
declare continue handler for 1213 begin end;
set @cnt_a:=null;
repeat
select count(*) cnt_a into
@cnt_a from t1 group by a having cnt_a > 1 limit 1;
if @cnt_a is not null then
select * from t1;
leave l1;
end if;
replace into t1(a,b,who) values(
until 1=2 end repeat;
end$
delimiter ;
---
Then execute in 3 different sessions:
session1> call p1(1); check table t1;
session2> call p1(2); check table t1;
session3> call p1(3); check table t1;