MySQL Bugs: #107961: AUTOINC counter resets to 1 after ALTER TABLE following by restart

Bug #107961	AUTOINC counter resets to 1 after ALTER TABLE following by restart
Submitted:	25 Jul 2022 9:59	Modified:	29 Jul 2022 8:12
Reporter:	Rahul Sisondia	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto increment

Description:
If a column of a table is changed to auto inc using alter table statement followed by the server restart then counter is reset to 1 as a result subsequent insert statements fail.  This was not the problem in 5.7. I speculate it could be regression due to https://dev.mysql.com/worklog/task/?id=6204 

Please refer the steps to reproduce. 

How to repeat:

1- 
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

2- Create an empty table (no auto-inc)

CREATE TABLE test(
id INT(11) NOT NULL,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
);

3- Insert dummy data with unique key

insert into test(id, name) values (1, "A");
insert into test(id, name) values (2, "B");
insert into test(id, name) values (3, "C");
insert into test(id, name) values (4, "D");

4- Force auto-inc on the ID column

ALTER TABLE test CHANGE COLUMN id id int NOT NULL AUTO_INCREMENT;
Check the AUTO_INCREMENT counter by using show create table:

show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Additionally, check the data dictionary/information schema metadata.

select * from information_schema.tables where table_name='test'\G;
*************************** 1. row ***************************
 TABLE_NAME: test
 ...
 AUTO_INCREMENT: 5
 CREATE_TIME: 2022-06-30 20:48:31
 ...

5- restart the server either by killing the server or safe shutdown. 

6- Show create table again, AUTO_INCREMENT counter should be gone.

show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

7- Insert new value into table which should rely on auto-increment.

insert into test(name) values ("E");
ERROR 1062 (23000): Duplicate entry '1' for key 'test.PRIMARY'

8- Check table again and see counter has reset back to 1 (2 since we did an insert):
show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------

Suggested fix:
This is not a fix but a mitigation step. For some reason if we execute the alter statement twice then changes are persisted.

>>> ALTER TABLE test CHANGE COLUMN id id int NOT NULL AUTO_INCREMENT;
>>> ALTER TABLE test CHANGE COLUMN id id int NOT NULL AUTO_INCREMENT;

Changing the ALGORITHM to COPY in the alter table statement does not make any difference.

Hi Mr. Sisondia,

Thank you very much for your bug report.

We have managed to repeat it fully. We did a proper shutdown and INSERT that should have worked properly, now returns the same error on the duplicate key.

Hence, this is truly a bug.

Verified as reported.

The innodb does not persistent dynamic metadata for autoinc counter by writing MLOG_TABLE_DYNAMIC_META redo log. Your ddl is required for copying data and skip the undo log for insertion of row in the table during copy alter operation.

Thanks @huahua xu, 

I am not clear though. Are you explaining why does this happen ? or Are you saying that it is by design so will not be fixed ?

Hi Rahul Sisondia,

I just explained the cause of the bug.

Posted by developer:
 
Some of the findings so far:

It seems this bug was introduced by the commit 6ff88b3430fb1ae91d9e5175b1e95d5079675c56
"WL#9536: Add a 'version' field to innodb_dynamic_metadata"

in 2017

The strange behaviour seen, where issuing the alter command twice in a row "fixes" the problem, seems to be pointing to the
fact that the first alter command makes a copy of the table, then replaces the original table by a rename -- while the second alter command results in a an inplace alter, which then seems to persist the autoinc counter.

Thank you, Mr. Buella.

Posted by developer:
 
I've reported the issue I've described above as a separate
Bug #34752625 Persistent Table Metadata from various versions is not aggregated properly

Thank you, Mr. Lopuszanski .....