Mysql error: Duplicateentry & amp; #39; 127 & amp; #39; forkey & am

Sometimes I am lucky. When I think that I have mastered a certain knowledge point, another problem occurs immediately, so that I can know that my previous cognition is incomplete.

As described in my previous blog post, if an auto-increment field reaches the upper limit and data is still inserted into it, a prompt will appear for Failed to read auto-increment value from storage engine. However, I encountered another error message: Duplicate entry '000000' for key 'Primary '. After troubleshooting, it is also because the auto-increment field has reached the upper limit. Why are there different prompts for the same problem?

The test result is as follows:

1. If no data exists in the data table at this time, and we use statements such as insert into values to INSERT data, the words Duplicate entry '000000' for key 'Primary 'will be displayed. (Because the test field I set is tinyint, It is 127)

2. If no data exists in the data table at this time, and we use statements such as insertinto select from, and the storage engine is INNODB, A message such as Failed to read auto-increment value from storage engine is displayed;

3. If there is data in the data table at this time, there will always be errors such as Duplicate entry '000000' for key 'Primary.

Therefore, it is easy to understand the Duplicate entry '000000' for key 'primary. Another prompt is caused by the unique level-2 cache of the INNODB engine. The data is not inserted into the data table first, but stored in the cache first, only to increase the auto-increment in the table. Therefore, when the auto increment reaches the limit, when InnoDB needs to obtain the auto increment value and store it to the cache, it finds that no higher number is found.

Zookeeper