Bug #119445 ALTER TABLE ... AUTO_INCREMENT allows values outside column datatype range → later INSERT fails

• Submitted: 23 Nov 4:32
• Reporter: Prasad A
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.7
• OS: Ubuntu
• CPU Architecture: Any

[23 Nov 4:32] Prasad A

Description:
MySQL allows setting AUTO_INCREMENT to a value that exceeds the maximum storable value of the column’s datatype.
The ALTER TABLE statement succeeds, but subsequent INSERT operations fail because the generated value cannot be stored in the INT column.

This causes MySQL to enter an inconsistent internal state where:

The AUTO_INCREMENT counter is outside the legal INT range

The storage engine cannot generate the next value

INSERTs fail with:
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

The server validates only that AUTO_INCREMENT >= MAX(id)+1,
but it does not validate that AUTO_INCREMENT is within the datatype’s legal range.

This gap allows operators and automation tools to unknowingly place the table into a permanently broken state.

Request:
Please add datatype-range validation for AUTO_INCREMENT values.

How to repeat:
CREATE TABLE tableA (
    id INT NOT NULL AUTO_INCREMENT,
    data VARCHAR(255),
    PRIMARY KEY (id)
);

-- Set AUTO_INCREMENT beyond signed INT max (2147483647)
ALTER TABLE tableA AUTO_INCREMENT = 2147483648;

-- Now try to insert
INSERT INTO tableA (data) VALUES ('ok');

Suggested fix:
MySQL should reject the ALTER or validate the value against the column’s datatype limit.