Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 生产出现failed to read auto_random value from storage engine
[TiDB Usage Environment] Production Environment
[TiDB Version] v6.5.3
[Reproduction Path] What operations were performed when the issue occurred
In the production environment, a table encountered “failed to read auto_random value from storage engine”. The table uses auto_random(5), and the current maximum value of the id in the table is approaching the upper limit allowed by bigint.
[Encountered Issue: Problem Phenomenon and Impact]
The table can no longer insert values.
[Resource Configuration]
I looked at the official documentation’s explanation of auto_random, but I still don’t quite understand the meaning of the shard bit count S. Does it mean that the id is allocated based on a 5-digit value each time? Now that the table can’t insert data, if I set the value of R to 54, will I encounter this issue again in the future?
This expert has summarized it very well. You can take a look.
I noticed that the official documentation mentions that when inserting data, it is not recommended to explicitly specify the value of a column containing AUTO_RANDOM. Improper explicit assignment may lead to the table prematurely exhausting the values used for automatic allocation.
The original poster’s database probably has manually inserted data. I wonder how they handle it when the random numbers are exhausted.
AUTO_RANDOM(S, R)
Generally, S + R = 64, but it can also be explicitly specified, such as AUTO_RANDOM(5, 54).
S is the shard bit. If the number of shards is 2 to the power of S, for example, setting it to 5 means 2 to the power of 5, which is 32 shards. These will be roughly evenly distributed across your TiKV nodes. For example, if there are 4 TiKV nodes, each node will store 8 shards, balancing the load across all TiKV nodes.
R is the length bit, which combined with the shard bit determines the length of the field. For example, if you set S to 5, R defaults to 59, making the field length 64 (but if you explicitly set AUTO_RANDOM(5, 54), the field length is 59). This value increases monotonically but does not guarantee continuity. Theoretically, a 59-bit number is more than enough for a lifetime, but there are exceptions. If you explicitly insert a number, for example, 99999999999999999999999999999999 into this field, which is the maximum value for 59 bits, the next automatically allocated number must be larger than this field, resulting in an error. Therefore, it is not recommended to explicitly insert values into an AUTO_RANDOM field.
The number of different values that can be formed by the first 5 bits of binary in auto_random(5) is the number of shards.
So, I’m encountering this issue now, and it might indeed be because I explicitly inserted the ID value before. How can I find this ID value to modify it? Now, the available length R is set to 54, which means the auto-increment bit is 54-1-5=48. How can I find the current maximum value of this auto-increment bit of this length?
Run SHOW CREATE TABLE table_name to check the value of AUTO_RANDOM_BASE. This value can only be increased, not decreased.
Currently, there are only about a hundred thousand records in the table, and it is already unable to insert more. Is there something unreasonable in the implementation mechanism? Moreover, it seems a bit difficult to ensure that users absolutely do not explicitly insert IDs. Can this be made more flexible?
You can choose to generate the ID data yourself, which is more suitable for your scenario, but there will be hotspot issues. You can’t have your cake and eat it too, deal with the bottleneck when you encounter it.
This is the value you inserted yourself. Create a new table and import it over.
With only tens of thousands of data, a single explicit Insert caused the database to make the business inoperable. Isn’t the robustness of this database a bit poor? Shouldn’t it be handled automatically at the database level?
What if data is explicitly inserted? We can’t just stop doing business, right?
It depends on the perspective: freedom also has rules. For example, a primary key must be unique and non-null, but if I want to insert two identical values into the primary key, why doesn’t the database allow it?
Set the allow_auto_random_explicit_insert parameter to 0 to prohibit explicit insertion of IDs.
The current value of the id field is used in other tables, so it cannot be auto-generated.
The problem now is that there are already explicitly inserted values, but we don’t know which one. Turning this off is of no use either. Looking at the error now, the engine can no longer allocate ID values.
Is there any command that can change this individually?
From a database logic perspective, it’s not feasible. Think about it, how does TiDB’s autorandom ensure that the random value is not repeated each time? It’s impossible to check the table every time, right? It’s simply composed of sharding plus an incrementing column. Now, if you explicitly specify a value and insert it, to ensure that subsequent values do not duplicate the value you just inserted, the corresponding auto-increment column can only be greater than the value you explicitly inserted. It won’t allow you to reduce it, otherwise, the random values generated later might be duplicated again, right?
Could you please tell me where the relevant source code is? How is it calculated how many IDs are still available for my current table?
Execute SHOW CREATE TABLE tt on the table corresponding to the error;
AUTO_RANDOM_BASE is its current maximum value.
|
username: TiGod
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: