MySQL Bugs: #105092: AUTO_INCREMENT can be set to less than MAX + 1 and not forced to MAX + 1

Bug #105092	AUTO_INCREMENT can be set to less than MAX + 1 and not forced to MAX + 1
Submitted:	30 Sep 2021 21:34	Modified:	19 Apr 2023 11:25
Reporter:	Domen Kermc	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.26, 8.0.11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution, regression

Description:
AUTO_INCREMENT can be set to less than MAX + 1 and not forced to MAX + 1 if AUTO_INCREMENT property was added with ALTER TABLE.

Tested only on InnoDB tables.

How to repeat:
We create table but add AUTO_INCREMENT property later with ALTER TABLE:

CREATE TABLE `test` (`id` INT NOT NULL PRIMARY KEY);
INSERT INTO `test` (`id`) VALUES (1), (2), (3);
ALTER TABLE `test` MODIFY `id` INT NOT NULL AUTO_INCREMENT;
ALTER TABLE `test` AUTO_INCREMENT=1; -- should be forced to 4 but it's not

When trying to add new rows, server starts incrementing `id` from 1 which leads to duplication errors (but AUTO_INCREMENT increments despite errors):

INSERT INTO `test` (`id`) VALUES (NULL); -- error: #1062 - Duplicate entry '1' for key 'test.PRIMARY'
INSERT INTO `test` (`id`) VALUES (NULL); -- error: #1062 - Duplicate entry '2' for key 'test.PRIMARY'
INSERT INTO `test` (`id`) VALUES (NULL); -- error: #1062 - Duplicate entry '3' for key 'test.PRIMARY'
INSERT INTO `test` (`id`) VALUES (NULL); -- success

If AUTO_INCREMENT is moved from ALTER TABLE to CREATE TABLE this does not happen.

Hello Domen Kermc,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
The code in commit_get_autoinc() tries to assign the correct AUTO_INCREMENT value in case it is explicitly specified like below:

CREATE TABLE `test` (`id` INT NOT NULL PRIMARY KEY);
INSERT INTO  `test` (`id`) VALUES (1), (2), (3);
ALTER TABLE  `test` MODIFY `id` INT NOT NULL AUTO_INCREMENT; -- Statement1
ALTER TABLE  `test` AUTO_INCREMENT = 1;                      -- Statement2

Statement2 relies on `dict_table_t::autoinc_persisted` rather than `dict_table_t::autoinc`. This persisted value may not be up-to-date when executing Statement2 right after Statement1, and hence chooses the value 1 instead of 4.

This could also be because the persisted value should have been updated by Statement1.

This behaviour was introduced by "WL#6204 InnoDB persistent max value for autoinc columns", hence transferring to InnoDB team.

Hello!

I am attaching a simple patch against MySQL Server 8.0.32 which addresses this issue as a contribution.

A simple patch for bug 105092 against 8.0.32 source tree. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: ps-8577-bug105092-contrib.patch (text/x-patch), 24.85 KiB.

Thank you,  Dmitry for the contribution.

Sincerely,
Umesh

Posted by developer:
 
8.0.33 release note:
ALTER TABLE ... AUTO_INCREMENT</literal> could be set to less than MAX + 1 and not forced to MAX + 1.
Thank you Domen Kermc for reporting this bug.