Mysql error #1062 Duplicate entry '123456' for key 'primary'

In fact, the material for this fault comes from a friend of mine. Although I am not the real person who experienced the fault, even if I am just a bystander, I still feel a little bit rewarding. As a result, I recorded it to feed readers.

The root cause of a failure is roughly as follows: on a dark evening, a hard-pressed programmer was awakened by a burst of warning text messages, which turned out to be a problem with a table in the database, although the query operation is normal, the creation operation fails. After debugging, it is found that the table is inserted with a row of problematic data, the value of the auto-increment field is explicitly set to the maximum value of the integer type. As a result, the data inserted by default cannot obtain a valid primary key value.

Create a Test table to illustrate the problem:

Create table if not exists 'test '(
'Id' int (11) not null AUTO_INCREMENT,
'Name' varchar (100) not null,
Primary key ('id ')
) ENGINE = InnoDB;

Insert a row of problematic data:

Insert into test (id, name) VALUES (2147483647, 'x ');

As a result, the normal insert statement cannot be executed:

Insert into test (name) VALUES ('Y ');

In this case, the database reports an error:

#1062