Bug #117563 MySQL should check the range of auto-increment values when altering a column

• Submitted: 25 Feb 3:25
• Modified: 25 Feb 7:21
• Reporter: Hongyu Yang
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 8.4.4, 8.0, 9.2.0
• OS: Any
• CPU Architecture: Any

[25 Feb 3:25] Hongyu Yang

Description:
MySQL should check the range of auto-increment values when altering a column, rather than allowing it to be modified into a column type smaller than the auto-increment value.

How to repeat:
CREATE TABLE table2 (
     pkId int DEFAULT NULL,
     pkAttr0 bigint NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (pkAttr0),
     KEY table2index_pk (pkAttr0)
 ) ;
-- ok

alter table table2 AUTO_INCREMENT = 75577597300;
-- ok

alter table table2 change pkAttr0 pkAttr0 int AUTO_INCREMENT;
-- ok

insert into table2 (pkId) values (2);
-- [HY000][1467] Failed to read auto-increment value from storage engine

MySQL should inspect for type anomalies when executing a change to the auto_increment attribute and should prohibit the alteration of the column type.

The correct state should be:
mysql> alter table table2 change pkAttr0 pkAttr0 int AUTO_INCREMENT;
-- NOT ALLOW

[25 Feb 7:21] MySQL Verification Team

Hello Hongyu Yang,

Thank you for the report and feedback.
Related - Bug #90903/ Bug #84101

regards,
Umesh