Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 【关于系统变量 global重连未生效的情况】做实验的时候遇到了个神奇的现象 好像是bug
[TiDB Usage Environment] Own virtual machine
[TiDB Version] v6.0.0
[Encountered Problem]
set global auto_increment_increment temporarily ineffective
MySQL [test]> create table t1 (a int not null primary key auto_increment);
Query OK, 0 rows affected (0.13 sec)
Suddenly, the global setting became ineffective
After checking, it works fine
Reconnect and it works fine again
Repeating this operation still results in the global setting being ineffective
Inserting again makes it effective
If you modify global and then exit and reconnect
After setting global, you need to insert something. If you exit and re-enter, you will encounter this when inserting
After reconnecting like this, querying the parameters first will not appear.
TiDB can ensure the monotonicity of auto-increment values, but it cannot guarantee their continuity. The example diagram is quite confusing and not very clear. It would be better to use a text version of the commands for explanation. Does “global reconnection not taking effect” mean that after setting and reconnecting, querying “show variables like” does not take effect?
Let me briefly explain:
After setting the global variable, it is normal that it does not take effect immediately. However, there is a phenomenon where the global setting does not take effect after exiting and reconnecting.
As shown in the figure:
Step 1: I checked, and at this time, it can be seen that the increment is still by 2, which is normal (3, 5, 7 appear because I previously set the session-level auto_increment_increment = 2).
Step 2: I set the global auto_increment_increment = 3 to make the auto-increment primary key increase by 3.
Step 3: Insert and check. At this time, the global setting has not taken effect, which is normal. The increments are 5, 7, 9.
When I exit and reconnect,
Step 4: Insert and query again. Normally, it should be 5, 7, 9, 12, but in the figure, it is 5, 7, 9, 10.
I am very puzzled by this phenomenon. When I query, I can see that auto_increment_increment = 3 is correct.
When I insert and query again, it is 5, 7, 9, 10, 13. No other operations were performed during this period.
Moreover, this phenomenon can be reproduced and is not a one-time occurrence.
This is expected and consistent with the behavior in MySQL. The auto-increment value is related not only to auto_increment_increment but also to auto_increment_offset, the latter of which defaults to 1. The output of auto_increment always satisfies the following formula:
auto_increment_offset + N × auto_increment_increment (N is a natural number, such as 1, 2, 3, etc.)
Initially, you set auto_increment_increment to 2, so the auto_increment output is consistently 1, 3, 5, 7, 9, etc. When you change auto_increment_increment to 3, the next value greater than 9 that satisfies the above formula is 10, so it becomes 10, 13, 16, etc.
Correct, it needs to satisfy (ID - auto_increment_offset) % auto_increment_increment == 0. Here, after 9, 10 satisfies (10 - 1) % 3 == 0. Refer to this link for more details:
Thank you, master, I understand now! 
Thank you, master.
So that’s how it is.
This topic was automatically closed 1 minute after the last reply. No new replies are allowed.
|
username: 箱子NvN
|
username: 
|
username: 
|
username: