Bug #39413 Incorrect AUTO_INCREMENT value allowed
Submitted: 11 Sep 2008 23:26 Modified: 12 Sep 2008 6:52
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.0, 5.1, 6.0 bzr OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[11 Sep 2008 23:26] Peter Zaitsev 
Description:
Auto_Increment in CREATE TABLE or ALTER TABLE allows values larger than type defined for the column:

Note the type for column below is INT while the value is outside of this range. 

How to repeat:
mysql> alter table a1 auto_increment=3147483648;
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table a1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| a1    | CREATE TABLE `a1` (
  `i` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`i`)
) ENGINE=MyISAM AUTO_INCREMENT=3147483648 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[12 Sep 2008 6:52] Sveta Smirnova 
Thank you for the report.

Verified as described.
[12 Sep 2008 6:55] Sveta Smirnova 
Category changed, because problem is repeatable with InnoDB as well:

CREATE TABLE `a1` (
`i` int(11) NOT NULL auto_increment,
PRIMARY KEY  (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=3147483648 DEFAULT CHARSET=latin1;
show create table a1;
Table   Create Table
a1      CREATE TABLE `a1` (
  `i` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=3147483648 DEFAULT CHARSET=latin1
insert into a1 values();
Warnings:
Warning 1264    Out of range value adjusted for column 'i' at row 1
select * from a1;
i
2147483647