Bug #71932 reorganize + multirow insert into partition table leads to wrong auto_increment

• Submitted: 5 Mar 2014 9:42
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Verified
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.6.18, 5.7.5
• OS: Any
• CPU Architecture: Any

[5 Mar 2014 9:42] Shane Bester

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