Duplicate entry error with @@auto_increment_increment and @@auto_increment_offset when using AUTO_ID_CACHE=1

[tiancaiamao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. setup a cluster

tiup playground --mode tikv-slim
./bin/tidb-server -store=tikv -path=127.0.0.1

2. set @@auto_increment_increment and @@auto_increment_offset global variable

set @@global.auto_increment_increment = 66;
set @@global.auto_increment_offset = 9527;

(exit the mysql client and login again to make the global variable take effect)

3. create table and insert data

create table t (id int primary key auto_increment, k int) auto_id_cache = 1;
insert into t (k) values (1),(2),(3);
mysql> select * from t;
+------+------+
| id | k |
+------+------+
| 9527 | 1 |
| 9593 | 2 |
| 9659 | 3 |
+------+------+
3 rows in set (0.00 sec)

4. restart tidb

5. show create table and insert data again

show create table t;
mysql> insert into t (k) values (4);
ERROR 1062 (23000): Duplicate entry '9527' for key 't.PRIMARY'

2. What did you expect to see? (Required)

No duplicate entry error

3. What did you see instead (Required)

Duplicate entry

4. What is your TiDB version? (Required)

master

mysql> select tidb_version();
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.1.0-alpha-99-g3d82fc5be3-dirty
Edition: Community
Git Commit Hash: 3d82fc5be3a2b319de7e073e8b2b29d8f8f7ae94
Git Branch: master
UTC Build Time: 2024-04-15 14:46:06
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[tiancaiamao]

In MySQL, when the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.

Ref https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html

[tiancaiamao]

So the expect result above is

1 1
67 2
133 3
199 4

[jackysp]

Cannot reproduce this issue on v7.1.3. Is it OK to do more investigation on it?

[tiancaiamao]

Change from major to moderate because not so many users change the default system variable.
So it's not so common to trigger the bug.

[tiancaiamao]

Cannot reproduce this issue on v7.1.3. Is it OK to do more investigation on it?

Now I know why... v7.1 branch does not contain this fix #46550
So the behaviour differs and the reproduce steps described in this issue do not work on 7.1 branch

@jackysp

[sylar8023baby]

The result in tidb v6.5.10 is as follows:

then the result in mysql v8.0.36 is as follows:

[sylar8023baby]

The result in tidb v6.5.10 is as follows:

then the result in mysql v8.0.36 is as follows: