Bug #109446 alter table... auto_increment=1 does not work as expected
Submitted: 21 Dec 2022 5:26 Modified: 5 Jan 2023 12:49
Reporter: Phong Dinh Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[21 Dec 2022 5:26] Phong Dinh
Description:
alter table... auto_increment=1 does not work as expected in case the auto_increment column is modified after table creation.

When the column is modified to auto_increment, the alter table... auto_increment=1 command does not reset the auto_increment value to the current maximum AUTO_INCREMENT column value plus one.

How to repeat:
Create a table without auto_increment 

mysql> CREATE TABLE `test` (
    -> `id` int NOT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.01 sec)

Insert a record to this table

mysql> insert into test set id=3;
Query OK, 1 row affected (0.00 sec)

Modified the column to make it auto_incrment with ALTER

mysql> alter table test modify id int not null auto_increment;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

Then reset the auto_incrment with ALTER TABLE..AUTO_INCREMENT=1

mysql> alter table test modify id int not null auto_increment;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

Then insert data into this table and duplicate entry error occur

mysql>  insert into test set id=null;
Query OK, 1 row affected (0.01 sec)

mysql>  insert into test set id=null;
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql>  insert into test set id=null;
ERROR 1062 (23000): Duplicate entry '3' for key 'test.PRIMARY'

Suggested fix:
While using ALTER TABLE..AUTO_INCREMENT=x, and if the AUTO_INCREMENT column is not set in the table creation phase and the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.
[21 Dec 2022 13:00] MySQL Verification Team
Hi Mr. Dinh,

Thank you for your bug report.

What you described is exactly a designed behaviour , which is documented in our Reference Manual.

Not a bug.
[4 Jan 2023 6:29] Aristotle Po
Hi MySQL Verification Team,

> What you described is exactly a designed behaviour , which is documented in our Reference Manual
As quoted from https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html
>> Note
>> ALTER TABLE ... AUTO_INCREMENT = N can only change the auto-increment counter >> value to a value larger than the current maximum.

The note above does not seem to apply when AUTO_INCREMENT is defined during ALTER TABLE. 

Please check again if it is a bug or the documentation should be updated to included such behavior.

I have performed two test cases when AUTO_INCREMENT is defined during CREATE(t1) and ALTER(t2) table.
With both table t1 and t2 current value of AUTO_INCREMENT=4, the statement "ALTER TABLE <TABLE_NAME> AUTO_INCREMENT=2;" behaves differently.
Table t1 simply ignores it while table t2 implemented the change.

############################################################################
# 1) When AUTO_INCREMENT is defined during CREATE TABLE
############################################################################
mysql> CREATE TABLE `t1` (
    ->  `id` INT NOT NULL AUTO_INCREMENT,
    ->  PRIMARY KEY (`id`)
    ->  );
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.01 sec)

mysql> INSERT INTO t1 SET id = 3;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE t1\G            -- We can confirm AUTO_INCREMENT = 4
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 AUTO_INCREMENT=2;  -- Since value 2 is less than current(4), this should not changed AUTO_INCREMENT value
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t1\G            -- We can confirm AUTO_INCREMENT = 4 (it did not changed)
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

############################################################################
# 2) When AUTO_INCREMENT is defined during ALTER TABLE
############################################################################
mysql> CREATE TABLE `t2` (
    ->  `id` INT NOT NULL,
    ->  PRIMARY KEY (`id`)
    ->  );
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> INSERT INTO t2 SET id=3;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t2;
+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

mysql> ALTER TABLE t2 MODIFY id INT NOT NULL AUTO_INCREMENT;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t2\G            -- We can confirm AUTO_INCREMENT = 4
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> ALTER TABLE t2 AUTO_INCREMENT=2;  -- Since value 2 is less than current(4), this should not changed AUTO_INCREMENT value but it did
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t2\G            -- We can confirm AUTO_INCREMENT = 2. It did change which is not a usual behavior. if AUTO_INCREMENT was defined during CREATE TABLE it will simply ignore and will retain value AUTO_INCREMENT = 4.
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
[4 Jan 2023 16:18] Trey Raymond
The link you just pasted states, quoting directly:
" You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one. "

This directly contradicts with what you are claiming is documented there.  We do expect that anyone from the verification team has read the documentation before changing the status of any bug.
[5 Jan 2023 12:49] MySQL Verification Team
Hi,

You are actually correct.

That part of the manual has to be changed.

Verified as a documentation bug.
[16 Jan 2023 20:32] Sveta Smirnova
I believe this is a code bug. Since we see some value of the AUTO_INCREMENT in the SHOW CREATE TABLE output, it should not matter how initially this column was defined.
[17 Jan 2023 12:38] Dmitry Lenev
Hello!

I concur with Sveta and I would like to ask you to reconsider your decision about declaring this as Documentation bug.

The fact that two tables for which ALTER TABLE ... AUTO_INCREMENT=.. behavior differs are described identically in SHOW CREATE TABLE output means scenarios in which user rely on SQL-dumps + binlog for backup and restore might be broken. For example, imagine what will happen if user takes SQL-dump for table t2 in the above test case right before the ALTER TABLE t2 AUTO_INCREMENT=1 is executed, and then later restores this dump and tries to apply ALTER TABLE t2 AUTO_INCREMENT=1 from the binary log (the behavior of ALTER TABLE will differ which means that backup doesn't restore correctly).

Also, I would like to note that behavior of not adjusting auto-increment value to be greater than already-used value in column for table t2, is observed only for ALTER TABLE t2 AUTO_INCREMENT=1 when it is executed using ALGORITHM=INPLACE (which is default). The behavior of ALTER TABLE t2 AUTO_INCREMENT=1 ALGORITHM=COPY is the same as for table t1/in line with documentation.

So by documenting buggy behavior you will create discrepancy between INPLACE and COPY algorithm which will add to confusion.
[27 Jan 2023 12:58] Gopal Shankar
Posted by developer:
 
Agree with the analysis from Dmitry Lenev above.
We see that this is a duplicate of
https://bugs.mysql.com/bug.php?id=105092, which is being worked on.
Hence, closing the bug.