Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 出现自增值小于表中数据最大值问题
[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] 7.5
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
A table test reported that data could not be inserted, citing a primary key conflict. The primary key used the default auto-increment value. Upon inspection, the maximum id value in the table was 122131, while the auto-increment value was 83853, resulting in a primary key conflict.
Under what circumstances would this situation occur? Normally, the auto-increment value should be greater than the maximum id value in the table.
It’s also normal to be smaller, possibly because a specific large value was specified during insertion. Additionally, the auto-increment value can also be modified.
When a specific large value is specified during insertion, I tested it and found no impact. For example, if the auto-increment value was originally 100 and you insert a specific value of 1000, the next auto-increment value will be 1001.
Did you perform any operations on the table, like recovery or something?
How about explicitly inserting a primary key?
It is estimated that some operations, such as bulk inserts followed by database table restoration, caused some bug.
It’s not normal. When you specify a large value during insertion, the table’s auto_increment value will default to this large value +1. Additionally, the auto_increment value can indeed be changed, but it can only be increased, not decreased, to prevent this issue.
It should be the scenario of explicitly and implicitly cross-inserting auto-increment column values, right? Executing an alter table xx auto_increment = 0 can trigger the tidbserver to reload the ID.
Also, check the PD, as it maintains the global auto-increment.
Auto-increment is maintained by the TiDB server itself, right? Here, I am using auto_id_cache=1.
The command “ALTER TABLE xx AUTO_INCREMENT = 0” in TiDB is useless.
Have you tried restarting? I remember that after restarting MySQL, it has an impact and won’t use the previous auto-increment ID value but instead uses max ID + 1 because the auto-increment ID value is stored in memory and needs to be recalculated after a restart! I’m not sure if TiDB has the same issue.
I haven’t restarted it. Theoretically, it should be normal after a restart.
Check the source of the maximum value.
The auto-increment value is 83854, and the current maximum value in the table is 122131. Then, I manually inserted 122132 successfully. After that, I tested inserting without specifying the primary key, and this time the insertion was successful. The auto-increment value was correctly set to the maximum value +1.
Still having the same issue after further operations? Following…
Although I don’t know why this problem occurs, I know how to solve it, which is not bad either 
I don’t know how this bug came about. If it happens in production, it will be a big headache.
Have you tried creating a table for analysis?
No, theoretically it shouldn’t have any impact.
|
username: zhanggame1
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: