Bug #90903 ALTER TABLE doesn't prevent AUTO_INCREMENT out of range in strict mode
Submitted: 17 May 2018 6:13 Modified: 23 May 2018 14:50
Reporter: monty solomon Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6/5.7/8.0 OS:Any
Assigned to: CPU Architecture:Any

[17 May 2018 6:13] monty solomon 
Description:
ALTER TABLE does not fail if the AUTO_INCREMENT value would be too large for the new type. Any INSERTs to the table will then fail.

How to repeat:
SET innodb_strict_mode=1;

SET sql_mode="ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

DROP TABLE IF EXISTS v;

CREATE TABLE `v` (
  `x` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `y` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`x`)
) ENGINE=InnoDB;

INSERT INTO v SET y=2147483648;

ALTER TABLE v MODIFY y INT NOT NULL;
ERROR 1264 (22003): Out of range value for column 'y' at row 1

ALTER TABLE v AUTO_INCREMENT=2147483648;

ALTER TABLE v MODIFY x INT NOT NULL AUTO_INCREMENT;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

INSERT INTO v SET y=1;
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

SHOW CREATE TABLE v\G
*************************** 1. row ***************************
       Table: v
Create Table: CREATE TABLE `v` (
  `x` int(11) NOT NULL AUTO_INCREMENT,
  `y` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`x`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483648

Suggested fix:
ALTER TABLE should fail.
[17 May 2018 6:18] monty solomon 
In this example, the ALTER TABLE statement permits the AUTO_INCREMENT value to be set to a value larger than can fit in the column.

DROP TABLE IF EXISTS w;

CREATE TABLE `w` (
  `x` int NOT NULL AUTO_INCREMENT,
  `y` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`x`)
) ENGINE=InnoDB;

ALTER TABLE w AUTO_INCREMENT=2147483648;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

INSERT INTO w SET y=1;
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

SHOW CREATE TABLE w\G
*************************** 1. row ***************************
       Table: w
Create Table: CREATE TABLE `w` (
  `x` int(11) NOT NULL AUTO_INCREMENT,
  `y` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`x`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483648
[17 May 2018 6:30] monty solomon 
Operations that produce undefined behavior should be prevented in strict mode

14.8.1.5 AUTO_INCREMENT Handling in InnoDB
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

* Assigning a negative value to the AUTO_INCREMENT column

In all lock modes (0, 1, and 2), the behavior of the auto-increment mechanism is not defined if you assign a negative value to the AUTO_INCREMENT column.

* If the AUTO_INCREMENT value becomes larger than the maximum integer for the specified integer type

In all lock modes (0, 1, and 2), the behavior of the auto-increment mechanism is not defined if the value becomes larger than the maximum integer that can be stored in the specified integer type.
[23 May 2018 14:50] MySQL Verification Team 
Thank you for the bug report. Verified as described.