[#MDEV-29757] Table AUTO_INCREMENT set to higher value than it should be for no apparent reason

--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.

[MDEV-29757] Table AUTO_INCREMENT set to higher value than it should be for no apparent reason Created: 2022-10-10 Updated: 2023-11-28
Status:	Open
Project:	MariaDB Server
Component/s:	Storage Engine - InnoDB
Affects Version/s:	10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s:	10.4, 10.5, 10.6

[MDEV-29757] Table AUTO_INCREMENT set to higher value than it should be for no apparent reason Created: 2022-10-10 Updated: 2023-11-28