Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 如何让Tidb server每个节点的缓存的自增id失效，从而重新申请？
[TiDB Usage Environment] Production Environment
[TiDB Version] v6.5.0
[Reproduction Path]
Migrating a table from MySQL to TiDB, the table’s id field is set with the AUTO_INCREMENT attribute. During the import of historical data, the id is explicitly written, and for subsequent normal business data, the id is implicitly written, with TiDB automatically assigning the auto-increment id.
[Encountered Problem: Phenomenon and Impact]
The current issue:
After migrating historical data, business data is normally written for a period, but then a primary key conflict error occurs.
[Expected Solution]
-
If I manually execute this SQL (the value is much larger than the maximum id value in the table) alter table t AUTO_INCREMENT=527854302;, can it invalidate the auto-increment id cached by the TiDB server, thereby making it reapply according to the starting id I set?
-
Is there a way to manually control the auto-increment id cached by the TiDB server, modify it, clear it, or invalidate it?
You can use the admin reset-auto-increment command to reset the auto-increment ID cached by the TiDB Server.
First, you need to connect to the TiDB console or use the TiDB client tool, and then execute the following command:
admin reset-auto-increment table_name
Where table_name is the name of the table for which you want to reset the auto-increment ID.
After executing this command, the TiDB Server will clear the cache of the auto-increment ID and reallocate it based on the current maximum ID value in the table. This can avoid primary key conflict errors and reapply the auto-increment ID according to the starting ID you set.
It should be noted that executing the admin reset-auto-increment command will lock the table for writing, which may affect ongoing business operations. Therefore, before executing this command, make sure that there are no important business operations in progress and execute it within an appropriate time window.
Borrowing this thread to ask, how can I make TiDB clear the AUTO_RANDOM cache?
Okay, thank you very much.
Through testing, it was found that after executing this SQL, alter table test.person AUTO_ID_CACHE 0;, the automatically allocated IDs on each node of the tidbserver will have jumps, with a jump interval of 30,000. Does this SQL also clear the cached IDs in the tidbserver and then reallocate them?
TiDB does not proactively clear the AUTO_RANDOM cache, but you can clear the AUTO_RANDOM cache by restarting the TiDB instance.
When you modify the AUTO_RANDOM attribute of a table or perform some operations related to AUTO_RANDOM, TiDB stores the AUTO_RANDOM cache in memory. If you want to clear these caches, you can do so by restarting the TiDB instance.
Please note that before restarting the TiDB instance, ensure that you have backed up important data and that there will be no critical business operations during the restart.
Here are the general steps to restart a TiDB instance:
-
First, use the tidb-server status command or other methods to confirm the current status of the TiDB instance.
-
Stop the TiDB instance. You can use the systemctl command (if using systemd) or other applicable commands to stop the TiDB instance.
-
Wait for a while to ensure the TiDB instance has completely stopped.
-
Start the TiDB instance. Use the appropriate command to start the TiDB instance and wait for the instance to complete the startup.
-
Confirm the status of the TiDB instance again to ensure the instance has successfully started.
Please note that restarting the TiDB instance will cause service interruptions, so perform the operation during an appropriate time period and ensure that you have made the necessary backups and disaster recovery preparations.
The TiDB server caches 30,000 auto-increment IDs by default for each instance, ensuring uniqueness but not continuity. If you need unique and continuous IDs, add AUTO_ID_CACHE=1 when creating the table, making it behave like MySQL. To modify the auto-increment ID value, use a command like ALTER TABLE z2 FORCE AUTO_INCREMENT=100;. Without FORCE, reducing the ID value will not succeed. As for auto-increment primary key conflicts, it is unlikely to be a TiDB issue, as that would be a very serious bug. You can refer to the specific problem further.
Reference: AUTO_INCREMENT | PingCAP Documentation Center
Yes, executing the ALTER TABLE ... AUTO_ID_CACHE 0 statement will clear the auto-increment ID cached by the TiDB Server and reallocate the auto-increment ID.
In TiDB, the allocation of auto-increment IDs is done through a caching mechanism to improve performance. By default, TiDB caches a certain range of auto-increment IDs in memory to reduce the number of interactions with the storage engine. The size of this cache is controlled by the AUTO_ID_CACHE parameter, which defaults to 30000.
When you execute the ALTER TABLE ... AUTO_ID_CACHE 0 statement, setting AUTO_ID_CACHE to 0, TiDB will no longer cache auto-increment IDs and will interact with the storage engine each time an auto-increment ID is allocated. This will cause the allocation of auto-increment IDs to appear to jump, with intervals of 30000.
Therefore, if you want to clear the auto-increment ID cached by the TiDB Server and reallocate it, you can execute the ALTER TABLE ... AUTO_ID_CACHE 0 statement.
It is important to note that executing this statement will slow down the allocation of auto-increment IDs because each allocation will require interaction with the storage engine. Before executing, make sure you have backed up important data and perform the operation during an appropriate time period.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: baofengyu
|
username: 
|
username: 
|
username: 
|
username: 
|
username: