Description:
Affects 5.6, 5.7
Testcase fails like this:
ERROR 1062 (23000): Duplicate entry '127489' for key 'id'
mysql> select max(id) from t1;
+---------+
| max(id) |
+---------+
| 127496 |
+---------+
1 row in set (0.00 sec)
mysql> show table status like 't1'\G
*************************** 1. row ***************************
...
Rows: 127846
...
Auto_increment: 127489
...
How to repeat:
#you have have to adjust the sleep time as needed.
drop procedure if exists `p1`;
drop procedure if exists `p2`;
drop table if exists `t1`;
create table `t1` (`id` serial,`a` int) engine=innodb partition by range (`id`) (
partition `p0` values less than (0),partition `p1` values less than (2000),
partition `p2` values less than (4000),partition `p3` values less than (6000),
partition `p4` values less than (8000),partition `pn` values less than (18446744073709551615));
delimiter $
create procedure `p2`()
begin
declare cnt bigint unsigned default 0;
repeat
set cnt:=cnt+1;
insert into `t1`(`a`) values (0),(1),(2),(3),(4),(5),(6),(7);
if cnt mod 1000 = 0 then select cnt; end if;
until 1=2 end repeat;
end $
create procedure `p1`()
begin
declare v_num int default 4;
declare v_range bigint unsigned default 8000;
repeat
set v_num:=v_num+1;
set v_range:=v_range+2000;
set @sql:=concat("alter table `t1` reorganize partition pn into (partition `p",v_num,"` values less than (",v_range,"), partition `pn` values less than (18446744073709551615));");
prepare stmt from @sql;
execute stmt;
select sleep(1),v_range,v_num;
until v_num > 400 end repeat;
end $
delimiter ;
call p1(); #connection 1
call p2(); #connection 2