Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
-
None
Description
--source include/have_innodb.inc
|
--source include/have_sequence.inc
|
|
|
create table t (a int auto_increment key) engine=InnoDB; |
insert into t select null as a from seq_1_to_100; |
show create table t; |
select min(a), max(a) from t; |
|
|
# Cleanup
|
drop table t; |
|
10.3 602124bb |
show create table t; |
Table Create Table |
t CREATE TABLE `t` ( |
`a` int(11) NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`a`) |
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
select min(a), max(a) from t; |
min(a) max(a) |
1 100
|
So, while all 100 rows are inserted without gaps, the table AUTO_INCREMENT is set to a much higher value, and it is indeed used on the next INSERT, it will start from 128. Modifying the test case to continue inserting:
select min(a), max(a) from t; |
min(a) max(a) |
1 100
|
delete from t; |
insert into t select null as a from seq_1_to_100; |
show create table t; |
Table Create Table |
t CREATE TABLE `t` ( |
`a` int(11) NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`a`) |
) ENGINE=InnoDB AUTO_INCREMENT=255 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
select min(a), max(a) from t; |
min(a) max(a) |
128 227
|
So again, it lost 27 values while setting the new AUTO_INCREMENT.
Over 25% is a fairly big loss especially if it happens repeatedly.
Even stranger, seemingly unrelated tables can affect it:
--source include/have_innodb.inc
|
--source include/have_sequence.inc
|
|
|
create table t2 (a int auto_increment key) engine=InnoDB select null as a from seq_1_to_100; |
show create table t2; |
Here it is the same 128
t2 CREATE TABLE `t2` ( |
`a` int(11) NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`a`) |
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
But
--source include/have_innodb.inc
|
--source include/have_sequence.inc
|
|
|
create table t1 (a int); |
insert into t1 values (1),(2),(3),(4),(5); |
|
|
create table t2 (a int auto_increment key) engine=InnoDB select null as a from seq_1_to_100; |
show create table t2; |
here it is already 132:
t2 CREATE TABLE `t2` ( |
`a` int(11) NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`a`) |
) ENGINE=InnoDB AUTO_INCREMENT=132 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
(and the unrelated table isn't even necessarily InnoDB).
Reproducible with innodb-autoinc-lock-mode=1|2 (default is 1). Not reproducible with innodb-autoinc-lock-mode=0. Not reproducible with MyISAM.