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) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.6.18, 5.7.5 OS:Any
Assigned to: 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

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.