[MDEV-21154] AUTO_INCREMENT value too high after certain insert - Jira

XML

Word

Printable

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:

And the auto_increment of the table is 15.

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.