Wrong AUTO_INCREMENT value in SHOW CREATE TABLE output

[dveeden]

Bug Report

1. Minimal reproduce step (Required)

sql> create table t(id int primary key auto_increment) auto_increment=321;
Query OK, 0 rows affected (0.1178 sec)

sql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=321
1 row in set (0.0009 sec)

sql> insert into t values();
Query OK, 1 row affected (0.0204 sec)

sql> table t;
+-----+
| id  |
+-----+
| 321 |
+-----+
1 row in set (0.0012 sec)

sql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30321
1 row in set (0.0013 sec)

sql> insert into t values();
Query OK, 1 row affected (0.0150 sec)

sql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30321
1 row in set (0.0026 sec)

sql> table t;
+-----+
| id  |
+-----+
| 321 |
| 322 |
+-----+
2 rows in set (0.0037 sec)

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

I would expect the AUTO_INCREMENT in the SHOW CREATE TABLE... output to go from 321 to 322, etc.

3. What did you see instead (Required)

AUTO_INCREMENT=30321

4. What is your TiDB version? (Required)

sql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.5.0-alpha-252-gd177ee4ba-dirty
Edition: Community
Git Commit Hash: d177ee4ba6fd72990e21307024df0f62a3eba67a
Git Branch: master
UTC Build Time: 2022-01-31 16:19:38
GoVersion: go1.16.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.0007 sec)

[dveeden]

cc @mjonss @bb7133

[dveeden]

It looks like a tidb-server that is handing out values in the range of 1..30000 based on the AUTO_ID_CACHE shows 30000 here instead of the next value it would assign.

Creating a table with AUTO_INCREMENT

sql> create table t(id int primary key auto_increment);
Query OK, 0 rows affected (0.0954 sec)

sql> insert into t values();
Query OK, 1 row affected (0.0202 sec)

sql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001
1 row in set (0.0012 sec)

sql> table t;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.0018 sec)

sql> insert into t values();
Query OK, 1 row affected (0.0121 sec)

sql> insert into t values();
Query OK, 1 row affected (0.0125 sec)

sql> insert into t values();
Query OK, 1 row affected (0.0143 sec)

sql> insert into t values();
Query OK, 1 row affected (0.0123 sec)

sql> table t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.0028 sec)

So 1..5 are allocated and the AUTO_INCREMENT value of the table is 30001.

sql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001
1 row in set (0.0028 sec)

sql> ALTER TABLE t AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.0806 sec)

sql> insert into t values();
Query OK, 1 row affected (0.0208 sec)

sql> table t;
+-------+
| id    |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
|     5 |
| 30001 |
+-------+
6 rows in set (0.0013 sec)

We tried to change the AUTO_INCREMENT value to 100, expecting a table with 1..5,100, but instead got 1..5,30001 as it reset the AUTO_INCREMENT, but not with the value we specified. This next value would be similar for a second TiDB Server or after restart (AUTO_ID_CACHE behavior)

Now we use force to set the AUTO_INCREMENT:

sql> ALTER TABLE t FORCE AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.1037 sec)

sql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=100
1 row in set (0.0011 sec)

sql> insert into t values();
Query OK, 1 row affected (0.0204 sec)

sql> table t;
+-------+
| id    |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
|     5 |
|   100 |
| 30001 |
+-------+
7 rows in set (0.0034 sec)

sql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30100
1 row in set (0.0012 sec)

Now it works in the way we expect.

I think that with #32078 this would be much easier to understand for users. Maybe we also need to extend pingcap/docs#7502 to explain this a bit better.

[mjonss]

I'm not sure this is a bug or something that is not documented well enough.

If I understand the implementation correctly, then the PD has each tables next AUTO_INCREMENT number, but each TiDB node that has updated/inserted (possibly used) the table also has a cache of (AUTO_ID_CACHE ids, default 30000).

Since when the first AUTO_INCREMENT is needed TiDB will request a set of AUTO_ID_CACHE numbers from PD or when assigning that column to a given value, it will request AUTO_ID_CACHE numbers starting from max(, ).

So the more TiDB nodes there are, the more cached numbers there will be.

Also when a TiDB node stops/restart, it will not hand back the cached AUTO_INCREMENT values.

Setting AUTO_ID_CACHE to 1 should work more similar to MySQL, but would create much more overhead in TiDB/PD, due to the TiDB nodes always need to get a new AUTO_INCREMENT from PD for each row.

My opinion is that this is an issue of documentation (and being more clear about the case in ALTER TABLE t AUTO_INCREMENT <lower value> without FORCE, see #32078 ), due to the optimisation turned on by default to let each TiDB node have AUTO_ID_CACHE (default 30000) values.

@dveeden What is the expected AUTO_INCREMENT value in SHOW CREATE TABLE in a cluster with multiple TiDB nodes and AUTO_ID_CACHE > 1? The next value it would get from the TiDB node running the SHOW CREATE TABLE, the next value that would be from a newly restarted TiDB node (i.e. current implementation, return what PD stored) or ?!? (Aside from issue #32078 )

[dveeden]

@mjonss I am also not sure that there is a bug here. However due to the caching it behaves differently than MySQL/InnoDB. So it could just be that the documentation isn't clear enough about this.

It also looks that when setting the AUTO_INCREMENT that this, at least at first, changes the behavior a bit.

It would also be good to add docs to https://pingcap.github.io/tidb-dev-guide/ about the inner workings of AUTO_INCREMENT/AUTO_RANDOM and how the interaction between PD, TiKV, TiDB, etc works.

[dveeden]

cc @morgo @TomShawn @tisonkun

[morgo]

I agree with @mjonss ; it's somewhat ambiguous because of how the cache works, and not strictly a bug.

We encounter many test-cases where the output between MySQL and TiDB is the auto increment behavior. I expect that it is not just tests, and some users will be affected.

I created pingcap/docs#6911 to address the most common issue and filed pingcap/docs#7515 to mention another common problem. But we can document this aspect better too, since there is already a section of the docs which talks about the cache.

[tiancaiamao]

Now we have MySQL compatible AUTO_INCREMENT #38442
If you create table with AUTO_ID_CACHE=1, the behaviour is compatible with MySQL.