Auto increment increments by 2 while it should be increment by 1

[dveeden]

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE t1(id SERIAL);
INSERT INTO t1() VALUES();
INSERT INTO t1() VALUES();
SELECT LAST_INSERT_ID();
TABLE t1;
INSERT INTO t1() VALUES(),(),();
SELECT LAST_INSERT_ID();
TABLE t1;
SHOW GLOBAL VARIABLES LIKE 'auto_increment%';

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

MySQL 8.3.0

sql> CREATE TABLE t1(id SERIAL);
Query OK, 0 rows affected (0.0551 sec)

sql> INSERT INTO t1() VALUES();
Query OK, 1 row affected (0.0110 sec)

sql> INSERT INTO t1() VALUES();
Query OK, 1 row affected (0.0100 sec)

sql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.0003 sec)

sql> TABLE t1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.0004 sec)

sql> INSERT INTO t1() VALUES(),(),();
Query OK, 3 rows affected (0.0096 sec)

Records: 3  Duplicates: 0  Warnings: 0

sql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.0004 sec)

sql> TABLE t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.0004 sec)

sql> SHOW GLOBAL VARIABLES LIKE 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.0028 sec)

3. What did you see instead (Required)

TiDB

mysql> CREATE TABLE t1(id SERIAL);
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO t1() VALUES();
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO t1() VALUES();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

mysql> TABLE t1;
+----+
| id |
+----+
|  1 |
|  3 |
+----+
2 rows in set (0.00 sec)

mysql> INSERT INTO t1() VALUES(),(),();
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

mysql> TABLE t1;
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
|  6 |
|  7 |
+----+
5 rows in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

Tested with 7.5.1 and 8.0.0

[dveeden]

cc @mjonss

[bb7133]

Hi @dveeden , this is actually a known compatibility issue of TiDB, see #982 for more details.

We've tried to fix it(like #28448) but we're not confident enough for merging it(changing any existing behavior could be a problem for TiDB users). That's why it is only changed/fixed with AUTO_ID_CACHE=1(#39041).

[dveeden]

Closing this as duplicate of #982