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

Thank you for your help! If the status of the bug report you submitted changes, you will be notified. You may return here and check on the status or update your report at any time. That URL for your bug report is: http://bugs.mysql.com/117563.

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	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	8.4.4, 8.0, 9.2.0	OS:	Any
Assigned to:		CPU Architecture:	Any

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

Hello Hongyu Yang,

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

regards,
Umesh