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 value for the auto-increment primary key field has reached 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 value cannot be duplicate, an error is reported.

Solution

1. If there are too 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 back to the original table. (There are multiple methods for importing and exporting data. The following is only an example.
   1. Create the table auto_test5_tmp.

      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) 

   2. Insert records into the table auto_test5_tmp.

      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 

   3. Query the data in auto_test5_tmp.

      select * from auto_test5_tmp; 
      +----+------+ 
      | id | name | 
      +----+------+ 
      |  1 | A    | 
      |  2 | B    | 
      |  3 | C    | 
      |  4 | X    | 
      |  5 | Y    | 
      |  6 | Z    | 
      +----+------+ 

   4. Deletes the original table auto_test5.

      drop table auto_test5; 

   5. Rename the table auto_test5_tmp to auto_test5.

      rename table auto_test5_tmp to auto_test5;
      Query OK, 0 rows affected (0.12 sec)  

2. If the value for the auto-increment primary key is too small, change the field type of the auto-increment primary key to store more data.

   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