Help Center/ Relational Database Service/ Troubleshooting/ RDS for MySQL/ Other Issues/ AUTO_INCREMENT Value Exceeding the Maximum Value of This Field plus 1
Updated on 2023-11-03 GMT+08:00

AUTO_INCREMENT Value Exceeding the Maximum Value of This Field plus 1

If the value of AUTO_INCREMENT is not equal to the maximum value of this field plus 1 in a data table, the possible causes are as follows:

  • If the increment is not 1, the value of AUTO_INCREMENT is equal to the maximum value of this field plus the increment. For details, see Starting Value and Increment of AUTO_INCREMENT.
    mysql> show variables like 'auto_inc%'; 
    +--------------------------+-------+ 
    | Variable_name            | Value | 
    +--------------------------+-------+ 
    | auto_increment_increment | 2     | 
    | auto_increment_offset    | 1     | 
    +--------------------------+-------+ 
    mysql> select * from auto_test1; 
    +----+ 
    | id | 
    +----+ 
    |  2 | 
    |  4 | 
    |  6 | 
    |  8 | 
    +----+ 
    mysql> show create table auto_test1; 
    +------------+-----------------------------------------+ 
    | Table      | Create Table                            | 
    +------------+-----------------------------------------+ 
    | auto_test1 | CREATE TABLE `auto_test1` (   
    `id` int NOT NULL AUTO_INCREMENT,   
    PRIMARY KEY (`id`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 | 
    +------------+-----------------------------------------+
  • The value of AUTO_INCREMENT was changed.
    mysql> select * from animals; 
    +----+-----------+ 
    | id | name      | 
    +----+-----------+ 
    |  1 | fish       | 
    |  2 | cat       | 
    |  3 | penguin   | 
    +----+-----------+ 
    mysql> show create table animals; 
    +---------+-----------------------------------------------------+ 
    | Table   | Create Table                                        | 
    +---------+-----------------------------------------------------+ 
    | animals | CREATE TABLE `animals` (   
    `id` mediumint NOT NULL AUTO_INCREMENT,   
    `name` char(30) NOT NULL,   
    PRIMARY KEY (`id`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8           | 
    +---------+-----------------------------------------------------+ 
    mysql> alter table animals AUTO_INCREMENT=100; 
    Query OK, 0 rows affected (0.04 sec) 
    Records: 0  Duplicates: 0  Warnings: 0 
    mysql> show create table animals; 
    +---------+-----------------------------------------------------+ 
    | Table   | Create Table                                        | 
    +---------+-----------------------------------------------------+ 
    | animals | CREATE TABLE `animals` (   
    `id` mediumint NOT NULL AUTO_INCREMENT,   
    `name` char(30) NOT NULL,   
    PRIMARY KEY (`id`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8         | 
    +---------+-----------------------------------------------------+
  • A transaction was not committed or was rolled back, so the value of AUTO_INCREMENT increased but did not go back down after the transaction was rolled back.
    mysql> show create table auto_test1; 
    +------------+----------------------------------------+ 
    | Table      | Create Table                           | 
    +------------+----------------------------------------+ 
    | auto_test1 | CREATE TABLE `auto_test1` (   
    `id` int NOT NULL AUTO_INCREMENT,   
    PRIMARY KEY (`id`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | 
    +------------+----------------------------------------+ 
    1 row in set (0.00 sec)  
    mysql> select * from auto_test1; 
    +----+ 
    | id | 
    +----+ 
    |  1 | 
    |  2 | 
    |  3 | 
    +----+ 
    mysql> begin; 
    Query OK, 0 rows affected (0.02 sec)  
    mysql> insert into auto_test1 values (0),(0),(0); 
    Query OK, 3 rows affected (0.00 sec) 
    Records: 3  Duplicates: 0  Warnings: 0  
    mysql> select * from auto_test1; 
    +----+ 
    | id | 
    +----+ 
    |  1 | 
    |  2 | 
    |  3 | 
    |  4 | 
    |  5 | 
    |  6 | 
    +----+ 
    6 rows in set (0.00 sec)  
    mysql> show create table auto_test1; 
    +------------+----------------------------------------+ 
    | Table      | Create Table                           | 
    +------------+----------------------------------------+ 
    | auto_test1 | CREATE TABLE `auto_test1` (   
    `id` int NOT NULL AUTO_INCREMENT,   
    PRIMARY KEY (`id`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 | 
    +------------+----------------------------------------+ 
    1 row in set (0.00 sec)  
    mysql> rollback; 
    Query OK, 0 rows affected (0.05 sec)  
    mysql> select * from auto_test1; 
    +----+ 
    | id | 
    +----+ 
    |  1 | 
    |  2 | 
    |  3 | 
    +----+ 
    3 rows in set (0.00 sec)  
    mysql> show create table auto_test1; 
    +------------+----------------------------------------+ 
    | Table      | Create Table                           | 
    +------------+----------------------------------------+ 
    | auto_test1 | CREATE TABLE `auto_test1` (   
    `id` int NOT NULL AUTO_INCREMENT,   
    PRIMARY KEY (`id`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 | 
    +------------+----------------------------------------+
  • After data is inserted, the value of AUTO_INCREMENT changed, but when the corresponding data row was deleted, the value of AUTO_INCREMENT did not decrease.
    mysql> show create table auto_test1; 
    +------------+----------------------------------------+ 
    | Table      | Create Table                           | 
    +------------+----------------------------------------+ 
    | auto_test1 | CREATE TABLE `auto_test1` (   
    `id` int NOT NULL AUTO_INCREMENT,   
    PRIMARY KEY (`id`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | 
    +------------+----------------------------------------+ 
    1 row in set (0.00 sec)  
    mysql> select * from auto_test1; 
    +----+ 
    | id | 
    +----+ 
    |  1 | 
    |  2 | 
    |  3 | 
    +----+ 
    mysql> insert into auto_test1 values (0),(0),(0); 
    Query OK, 3 rows affected (0.00 sec) 
    Records: 3  Duplicates: 0  Warnings: 0  
    mysql> select * from auto_test1; 
    +----+ 
    | id | 
    +----+ 
    |  1 | 
    |  2 | 
    |  3 | 
    |  4 | 
    |  5 | 
    |  6 | 
    +----+ 
    6 rows in set (0.00 sec)  
    mysql> show create table auto_test1; 
    +------------+----------------------------------------+ 
    | Table      | Create Table                           | 
    +------------+----------------------------------------+ 
    | auto_test1 | CREATE TABLE `auto_test1` (   
    `id` int NOT NULL AUTO_INCREMENT,   
    PRIMARY KEY (`id`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 | 
    +------------+----------------------------------------+ 
    1 row in set (0.00 sec)  
    mysql> delete from auto_test1 where id>3;  
    mysql> select * from auto_test1; 
    +----+ 
    | id | 
    +----+ 
    |  1 | 
    |  2 | 
    |  3 | 
    +----+ 
    3 rows in set (0.00 sec)  mysql> show create table auto_test1; 
    +------------+----------------------------------------+ 
    | Table      | Create Table                           | 
    +------------+----------------------------------------+ 
    | auto_test1 | CREATE TABLE `auto_test1` (   
    `id` int NOT NULL AUTO_INCREMENT,   
    PRIMARY KEY (`id`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 | 
    +------------+----------------------------------------+