[MDEV-21154] AUTO_INCREMENT value too high after certain insert Created: 2019-11-26  Updated: 2020-01-15  Resolved: 2020-01-15

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert, Storage Engine - InnoDB
Affects Version/s: 10.1, 10.3.20
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Markus Nägele Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS Linux release 7.6.1810 (Core)


 Description   

The auto increment of a table is not counting continuously in any case. For example create a table:

CREATE TABLE `foo` (
 
	`ID` INT(11) NOT NULL AUTO_INCREMENT,
 
	`Name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
 
	PRIMARY KEY (`ID`)
 
)
 
COLLATE='utf8mb4_unicode_ci'
 
ENGINE=InnoDB;

Then insert some rows:

insert into foo(Name)
 
SELECT '421' UNION
 
SELECT '422' UNION
 
SELECT '423' UNION
 
SELECT '424';

After that the added rows have the correct ID, but the auto_increment of the table is not 5 but 8.
Running the statement again:

insert into foo(Name)
 
SELECT '421' UNION
 
SELECT '422' UNION
 
SELECT '423' UNION
 
SELECT '424';

Leads to the following table:

ID Name
1 421
2 422
3 423
4 424
8 421
9 422
10 423
11 424

And the auto_increment of the table is 15.

 Comments   
Comment by Alice Sherepa [ 2020-01-15 ]

This case is not a bug, but expected behavior, please see https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html for more details: "Gaps in auto-increment values for “bulk inserts” ... For lock modes 1 or 2, gaps may occur between successive statements because for bulk inserts the exact number of auto-increment values required by each statement may not be known and overestimation is possible."

Generated at Thu Feb 08 09:04:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.