Auto Random Primary Key Conflict

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: auto random 主键冲突

| username: Holland

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.1
[Reproduction Path]
[Encountered Problem: Problem Phenomenon and Impact]
Batch inserting data into a table with auto random results in primary key conflicts.
[Resource Configuration]*
[Attachments: Screenshots/Logs/Monitoring]

| username: songxuecheng | Original post link

  1. During batch writing, has TiDB ever restarted?
| username: Holland | Original post link

No restart

| username: tidb菜鸟一只 | Original post link

Your duplicate ID value is very small, it doesn’t look like it was automatically generated…

| username: Holland | Original post link

However, look at the insert, it doesn’t show the inserted ID at all :joy:
And the auto_random_base is 53223112.

| username: tidb菜鸟一只 | Original post link

Is there a record with id 52844394 in the table? How was it generated…

| username: Holland | Original post link

Yes, it can be found. It should be the data previously restored by BR.

| username: huhaifeng | Original post link

Select the maximum id and then modify the base to this value.

| username: Holland | Original post link

How to modify the base? I couldn’t find the statement in the official documentation.

| username: huhaifeng | Original post link

I checked, and there is no such change.
You can consider a workaround: the general idea is to create a new table, insert the columns except for the id, and then rename it back.

For example:

  1. If the data volume is small, you can directly select.
  2. If the data volume is large, you can consider using dumpling + lightning;
    When exporting with dumpling, you can consider not including the id value (of course, it doesn’t matter if you do).
| username: Holland | Original post link

Okay, but I feel this is a bug.

| username: redgame | Original post link

There might really be a conflict.