Bug #71932 reorganize + multirow insert into partition table leads to wrong auto_increment
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
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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