after create table add auto_id_cache , the AUTO_INCREMENT col have the huge gap

[lisun8523]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

sysbench 1.20 tidb 7.5.1 1)vim oltp_common.lua modify create_table function add AUTO_ID_CACHE=1 2) the create table sql like this: CREATE TABLE sbtest2.sbtest4 (

id int(11) NOT NULL AUTO_INCREMENT,

k int(11) NOT NULL DEFAULT ‘0’,

c char(120) NOT NULL DEFAULT ‘’,

pad char(60) NOT NULL DEFAULT ‘’,

PRIMARY KEY (id) /T![clustered_index] CLUSTERED /,

KEY k_4 (k)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /T![auto_id_cache] AUTO_ID_CACHE=1 /

time sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=xxx.xxx.16.65 --mysql-port=6000 --mysql-user=root --mysql-password=‘xxx’ --mysql-db=sbtest --table_size=1000000 --tables=10 --threads=10 --time=300 prepare

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

the max(id) from sbtest_test = the count(*) from sbtest_table

3. What did you see instead (Required)

select max(id),min(id) ,count(1) from sbtest2.sbtest4;

±--------±--------±---------+

| max(id) | min(id) | count(1) |

±--------±--------±---------+

| 1486752 | 1 | 1000000 |

±--------±--------±---------+ insert 2688 rows ,the max(id)=count(),
but insert 2689 rows ,the max(id)=4001 ,count()=2689

4. What is your TiDB version? (Required)

7.5.1

[aytrack]

all tables have the same NEXT_GLOBAL_ROW_ID

mysql> show table sbtest.sbtest1 next_row_id;
+---------+------------+-------------+--------------------+----------------+
| DB_NAME | TABLE_NAME | COLUMN_NAME | NEXT_GLOBAL_ROW_ID | ID_TYPE        |
+---------+------------+-------------+--------------------+----------------+
| sbtest  | sbtest1    | id          |                  1 | _TIDB_ROWID    |
| sbtest  | sbtest1    | id          |            1486753 | AUTO_INCREMENT |
+---------+------------+-------------+--------------------+----------------+
2 rows in set (0.00 sec)

mysql> select max(id),min(id) ,count(1) from sbtest.sbtest2;
+---------+---------+----------+
| max(id) | min(id) | count(1) |
+---------+---------+----------+
| 1486752 |       1 |  1000000 |
+---------+---------+----------+
1 row in set (0.50 sec)

mysql> select max(id),min(id) ,count(1) from sbtest.sbtest3;
+---------+---------+----------+
| max(id) | min(id) | count(1) |
+---------+---------+----------+
| 1486752 |       1 |  1000000 |
+---------+---------+----------+
1 row in set (0.51 sec)

mysql> select max(id),min(id) ,count(1) from sbtest.sbtest4;
+---------+---------+----------+
| max(id) | min(id) | count(1) |
+---------+---------+----------+
| 1486752 |       1 |  1000000 |
+---------+---------+----------+
1 row in set (0.53 sec)

[tiancaiamao]

I find a pattern:

mysql> select count(*) from sbtest1 where id < 4000;
+----------+
| count(*) |
+----------+
|     2688 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1 where id >= 4000 and id < 8000;
+----------+
| count(*) |
+----------+
|     2688 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from sbtest1 where id >= 8000 and id < 12000;
+----------+
| count(*) |
+----------+
|     2688 |
+----------+
1 row in set (0.01 sec)

...

Add the first 2688 id is continuous ... and then a 'hole' within a batch (4000)

mysql> select count(*) from sbtest1 where id <= 2688;
+----------+
| count(*) |
+----------+
|     2688 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1 where id > 2688 and id < 4000;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from sbtest1 where id <= 6688 and id > 4000;
+----------+
| count(*) |
+----------+
|     2688 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1 where id > 6688 and id < 8000;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

[tiancaiamao]

Well, I find the root cause.

When I use 'insert into sbtest (k) values (xxx)' one value one time, there is no 'hole' in the result table. When I use 'insert into sbtest (k) values (xxx), (yyy), (zzz) ...' several value one time, the bug happen.

When we need multiple auto ids but the current batch is not enough to serve it, it will discard the current and get a new batch to serve this allocation. For example, current max auto id is 3997, we want alloc 7 ids, 3997 + 7 > 4000, so we discard [3997, 4000) and alloc the 7 ids from [4000-4007) ...

[tiancaiamao]

Minimal reproduce:

create table t (id int primary key auto_increment, k int) AUTO_ID_CACHE=1;
insert into t (k) values (1);
insert into t values (3997, 2);   ## make id close to the end of the batch which is 4000
mysql> select * from t;
+------+------+
| id   | k    |
+------+------+
|    1 |    1 |
| 3997 |    2 |
+------+------+
2 rows in set (0.01 sec)

insert into t (k) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
mysql> select * from t;
+------+------+
| id   | k    |
+------+------+
|    1 |    1 |
| 3997 |    2 |
| 4001 |    1 |
| 4002 |    2 |
| 4003 |    3 |
| 4004 |    4 |
| 4005 |    5 |
| 4006 |    6 |
| 4007 |    7 |
| 4008 |    8 |
| 4009 |    9 |
| 4010 |   10 |
+------+------+
12 rows in set (0.00 sec)

As you can see, 3998 ~ 4000 is discarded