[Regarding the Issue of Global System Variable Reconnection Not Taking Effect] Encountered a Strange Phenomenon During Experiment, Seems Like a Bug

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

Original topic: 【关于系统变量 global重连未生效的情况】做实验的时候遇到了个神奇的现象 好像是bug

| username: 箱子NvN

[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

| username: 箱子NvN | Original post link

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

| username: 箱子NvN | Original post link

After reconnecting like this, querying the parameters first will not appear.

| username: 啦啦啦啦啦 | Original post link

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?

| username: 箱子NvN | Original post link

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.

| username: ddhe9527 | Original post link

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.

| username: 啦啦啦啦啦 | Original post link

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:

| username: 箱子NvN | Original post link

Thank you, master, I understand now! :hugs::hugs:

| username: 箱子NvN | Original post link

Thank you, master.
:laughing::laughing: So that’s how it is.

| username: system | Original post link

This topic was automatically closed 1 minute after the last reply. No new replies are allowed.