Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: BR还原数据库后auto_increment被重置
[TiDB Usage Environment] Production Environment
[TiDB Version] V5.4.3
[Reproduction Path]
- Accidentally delete a database, for example: goods database
- Disable tikv gc: SET GLOBAL tidb_gc_enable = OFF;
- Set snapshot time point: set @@tidb_snapshot=“xxxx-xx-xx xx:xx:xx”;
- Find that the accidentally deleted goods database is still there
- Use the br tool to back up, specifying --backupts as the snapshot ts during the backup
- After the backup is successful, restore the backed-up goods database to the production environment
- Clear the snapshot time point: set @@tidb_snapshot=“”;
- Enable tikv gc: SET GLOBAL tidb_gc_enable = ON;
[Encountered Problem: Problem Phenomenon and Impact]
After the database is restored, the business starts writing data, and at this time there is a primary key conflict, and it is found that the auto_increment value starts from 1.
Due to the urgency of the situation at the time, the problem was solved using ALTER TABLE table_name AUTO_INCREMENT = xxx.
My questions are:
- Why does auto_increment start from 1 after br restoration?
- Because there are many tables in the database, although ALTER TABLE table_name AUTO_INCREMENT = xxx can solve the problem, it is too troublesome. Is there a simpler way to solve this problem? Can a smooth restart of the tidb server allow tidb to reallocate ID segments based on the max ID of the table to solve this problem?
The new version should not have this issue.
Are you referring to version 6.0 or 7.0?
Try upgrading the version.
It might be caused by metadata inconsistency. It’s not necessarily a version issue.
It is recommended to try upgrading.
I’ve never encountered this before.
Normally, there shouldn’t be this problem, right? The table structure already includes an AUTO_INCREMENT field…
Is auto_increment not cached in each TiDB and not persisted? Will BR handle this during recovery?
Theoretically, there is no need for persistence. TiDB can allocate a new segment upon startup. Some auto-increment values, although unused, are discarded and no longer used.
Upgrade to 6.5.9. I understand that restarting probably won’t solve this issue; it seems like it didn’t reset properly. Restarting just clears the current cache, and the increased size might not fit.
It is recommended to upgrade to a newer version, there shouldn’t be any issues.
The new version should not have this issue.
There seem to be a few similar bugs to note:
One is the one mentioned above: Duplicate entry when using BR to restore a NONCLUSTERED AUTO_ID_CACHE=1 table · Issue #46093 · pingcap/tidb · GitHub
- For non-clustered tables, there is a hidden auto increment column _tidb_rowid. When a non-clustered table also has an auto increment column with auto_id_cache=1, after importing with lightning/br, part of the metadata (specific value of auto_increment) for _tidb_rowid will be lost.
- After importing with lightning/br, the metadata of the user-defined auto increment column and _tidb_rowid in the table get mixed up, causing the values observed in show table to be incorrect. Additionally, because the metadata is incorrect, it cannot correctly issue IDs, leading to duplicate entry errors.
Another one is: DDL lost history jobs after batch creating tables · Issue #43725 · pingcap/tidb · GitHub
- When BR restore executes batch create table and encounters the “entry too large, split batch create table” error, it causes some tables to fail to create. Subsequently, when BR retries batch create table after splitting the batch, it finds that these tables already exist and directly returns an error, not proceeding to the subsequent logic of updating auto id. This results in primary key conflicts when reassigning auto id.
- The TiDB logs show an ErrEntryTooLarge error when executing “create tables”. Checking admin show ddl jobs, a certain create table DDL job is canceled, but it actually executed successfully (for example, you can get the table information through show create table).
Upgrading is a better way to handle this; otherwise, the workaround might be to manually reset the auto id with alter table auto_increment = x.
|
username: TiDBer_pcHqOtU9
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: