Failed to Insert Data Because Values for the Auto-increment Primary Key Field Reach the Upper Limit

Scenario

The error message "ERROR 1062 (23000): Duplicate entry 'xxx' for key 'xxx'" was displayed when data was inserted into a table.

Possible Causes

The values for the auto-increment primary key field reach the upper limit and cannot be increased. As a result, the auto-increment primary key value generated for the newly inserted data is the same as that of the previous data record in the table. Since the auto-increment primary key values cannot be duplicate, an error is reported.

mysql> create table auto_test5(id tinyint not null AUTO_INCREMENT, name varchar(8), PRIMARY KEY (`id`)); 
Query OK, 0 rows affected (0.06 sec) mysql> insert into auto_test5(name) values('A'),('B'),('C'); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0  
mysql> select * from auto_test5; 
+----+------+ 
| id | name | 
+----+------+ 
|  1 | A    | 
|  2 | B    | 
|  3 | C    | 
+----+------+ 
3 rows in set (0.00 sec)  
mysql> alter table auto_test5 AUTO_INCREMENT=125; 
Query OK, 0 rows affected (0.05 sec) 
Records: 0  Duplicates: 0  Warnings: 0  
mysql> show create table auto_test5; 
+------------+-------------------------------------------------------------+ 
| Table      | Create Table                                                | 
+------------+-------------------------------------------------------------+ 
| auto_test5 | CREATE TABLE `auto_test5` (   
`id` tinyint NOT NULL AUTO_INCREMENT,   `name` varchar(8) DEFAULT NULL,   
PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8 | 
+------------+-------------------------------------------------------------+ 
mysql> insert into auto_test5(name) values('X'),('Y'),('Z'); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0  
mysql> select * from auto_test5; 
+-----+------+ 
| id  | name | 
+-----+------+ 
|   1 | A    | 
|   2 | B    | 
|   3 | C    | 
| 125 | X    | 
| 126 | Y    | 
| 127 | Z    | 
+-----+------+ 
6 rows in set (0.00 sec) 
mysql> show create table auto_test5; 
+------------+-------------------------------------------------------------+ 
| Table      | Create Table                                                | 
+------------+-------------------------------------------------------------+ 
| auto_test5 | CREATE TABLE `auto_test5` (   `id` tinyint NOT NULL AUTO_INCREMENT,   `name` varchar(8) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8 | 
+------------+-------------------------------------------------------------+ 
mysql> insert into auto_test5(name) values('D'); 
ERROR 1062 (23000): Duplicate entry '127' for key 'auto_test5.PRIMARY'

Solution

• If there are many data changes and the actual data volume in the table is far less than the capacity of the auto-increment primary key, import all data in the table to a new table, delete the original table, and change the name of the new table to the original table name. (There are multiple methods for importing and exporting data. The following is only an example.)

  mysql>  create table auto_test5_tmp(id tinyint not null AUTO_INCREMENT, name varchar(8), PRIMARY KEY (`id`)); 
  Query OK, 0 rows affected (0.07 sec) 
  mysql> insert into auto_test5_tmp select 0,name from auto_test5; 
  Query OK, 6 rows affected (0.01 sec) 
  Records: 6  Duplicates: 0  Warnings: 0 
  mysql> select * from auto_test5_tmp; 
  +----+------+ 
  | id | name | 
  +----+------+ 
  |  1 | A    | 
  |  2 | B    | 
  |  3 | C    | 
  |  4 | X    | 
  |  5 | Y    | 
  |  6 | Z    | 
  +----+------+ 
  mysql> drop table auto_test5; 
  mysql> rename table auto_test5_tmp to auto_test5; 
  Query OK, 0 rows affected (0.12 sec)  
  mysql> select * from auto_test5; 
  +----+------+ 
  | id | name | 
  +----+------+ 
  |  1 | A    | 
  |  2 | B    | 
  |  3 | C    | 
  |  4 | X    | 
  |  5 | Y    | 
  |  6 | Z    | 
  +----+------+ 
  6 rows in set (0.01 sec) 
  mysql> show create table auto_test5; 
  +------------+-----------------------------------------------------------+ 
  | Table      | Create Table                                              | 
  +------------+-----------------------------------------------------------+ 
  | auto_test5 | CREATE TABLE `auto_test5` (   
  `id` tinyint NOT NULL AUTO_INCREMENT,   `name` varchar(8) DEFAULT NULL,   
  PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 | 
  +------------+-----------------------------------------------------------+

• If the values for the auto-increment primary key are not enough, change the field type of the auto-increment primary key to store more data.

  mysql> select * from auto_test6; 
  +-----+------+ 
  | id  | name | 
  +-----+------+ 
  |   1 | A    | 
  |   2 | B    | 
  |   3 | C    | 
  | 125 | X    | 
  | 126 | Y    | 
  | 127 | Z    | 
  +-----+------+ 
  6 rows in set (0.00 sec)  
  mysql> show create table auto_test6; 
  +------------+-------------------------------------------------------------+ 
  | Table      | Create Table                                                | 
  +------------+-------------------------------------------------------------+ 
  | auto_test6 | CREATE TABLE `auto_test6` (   
  `id` tinyint NOT NULL AUTO_INCREMENT,   `name` varchar(8) DEFAULT NULL,   
  PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8 | 
  +------------+-------------------------------------------------------------+ 
  mysql> alter table auto_test6  modify column id int NOT NULL AUTO_INCREMENT; 
  Query OK, 6 rows affected (0.15 sec) 
  Records: 6  Duplicates: 0  Warnings: 0  
  mysql> show create table auto_test6; 
  +------------+-------------------------------------------------------------+ 
  | Table      | Create Table                                                | 
  +------------+-------------------------------------------------------------+ 
  | auto_test6 | CREATE TABLE `auto_test6` (   
  `id` int NOT NULL AUTO_INCREMENT,   `name` varchar(8) DEFAULT NULL,   
  PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8 | 
  +------------+-------------------------------------------------------------+ 
  1 row in set (0.00 sec)  
  mysql> insert into auto_test6(name) values('D'); 
  Query OK, 1 row affected (0.01 sec)  
  mysql> select * from auto_test6; 
  +-----+------+ 
  | id  | name | 
  +-----+------+ 
  |   1 | A    | 
  |   2 | B    | 
  |   3 | C    | 
  | 125 | X    | 
  | 126 | Y    | 
  | 127 | Z    | 
  | 128 | D    | 
  +-----+------+ 
  7 rows in set (0.00 sec)  
  mysql> show create table auto_test6; 
  +------------+-------------------------------------------------------------+ 
  | Table      | Create Table                                                | 
  +------------+-------------------------------------------------------------+ 
  | auto_test6 | CREATE TABLE `auto_test6` (   
  `id` int NOT NULL AUTO_INCREMENT,   `name` varchar(8) DEFAULT NULL,   
  PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=129 DEFAULT CHARSET=utf8 | 
  +------------+-------------------------------------------------------------+ 
  1 row in set (0.01 sec)