drop table if exists t1;
create table t1(a tinyint not null, b tinyint not null, who int, rep_count int, trx_count int, trx_started int, primary key(b), unique key(a)) engine=innodb;
drop procedure if exists p1;
delimiter $
create procedure p1(me int)
begin
declare continue handler for 1062 begin end;
declare continue handler for 1213 begin end;
set transaction isolation level repeatable read;
set @trx_count:=0;
set @trx_count:=0;
set @replace_count:=0;
set @cnt_a:=null,@cnt_b:=null,@a:=null;
repeat
if rand() > 0.5 then
if @trx_started = 0 then
set @trx_count:=(@trx_count + 1);
end if;
set @trx_started:=@replace_count;
start transaction;
end if;
if rand() > 0.5 then
set @replace_count:=(@replace_count + 1);
replace into t1(a,b,who,rep_count,trx_count,trx_started) values(floor(3*rand()), floor(3*rand()),
me, @replace_count, @trx_count, @trx_started);
end if;
select count(*) cnt_a,a into
@cnt_a,@a from t1 group by a having cnt_a > 1 limit 1;
select count(*) cnt_b,b into
@cnt_b,@b from t1 group by a having cnt_b > 1 limit 1;
if (@cnt_a is not null) || (@cnt_b is not null) then
select * from t1;
end if;
set @cnt_a:=null,@cnt_b:=null,@a:=null;
if rand() > 0.5 then
set @trx_started:=0;
commit;
end if;
until 1=2 end repeat;
end $
delimiter ;
The test case I used from upstream bug report:
drop table if exists t1;
create table t1(a tinyint not null, b tinyint not null, who int, rep_count int, trx_count int, trx_started int, primary key(b), unique key(a)) engine=innodb;
drop procedure if exists p1; =null,@ cnt_b:= null,@a: =null; =(@trx_ count + 1); =@replace_ count; count:= (@replace_ count + 1); who,rep_ count,trx_ count,trx_ started)
values( floor(3* rand()) , floor(3*rand()), =null,@ cnt_b:= null,@a: =null;
delimiter $
create procedure p1(me int)
begin
declare continue handler for 1062 begin end;
declare continue handler for 1213 begin end;
set transaction isolation level repeatable read;
set @trx_count:=0;
set @trx_count:=0;
set @replace_count:=0;
set @cnt_a:
repeat
if rand() > 0.5 then
if @trx_started = 0 then
set @trx_count:
end if;
set @trx_started:
start transaction;
end if;
if rand() > 0.5 then
set @replace_
replace into t1(a,b,
me, @replace_count, @trx_count, @trx_started);
end if;
select count(*) cnt_a,a into
@cnt_a,@a from t1 group by a having cnt_a > 1 limit 1;
select count(*) cnt_b,b into
@cnt_b,@b from t1 group by a having cnt_b > 1 limit 1;
if (@cnt_a is not null) || (@cnt_b is not null) then
select * from t1;
end if;
set @cnt_a:
if rand() > 0.5 then
set @trx_started:=0;
commit;
end if;
until 1=2 end repeat;
end $
delimiter ;
session1 > call p1(77);
session2 > call p1(33);
session3 > call p1(55);
etc.