Duplicate entry when using BR to restore a NONCLUSTERED AUTO_ID_CACHE=1 table

[kennytm]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. prepare

use test;
create table t (a int primary key nonclustered auto_increment, b int) auto_id_cache = 1;
insert into t (b) values (1), (2), (3);
show table t next_row_id;
-- _TIDB_ROWID = 30001, AUTO_INCREMENT = 4

2. backup & restore

backup table t to 'local:///tmp/br';
drop table t;
restore table t from 'local:///tmp/br';

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

(explained below)

3. What did you see instead (Required)

3. the next_row_id are wrong

show table t next_row_id;
-- _TIDB_ROWID = 1, AUTO_INCREMENT = 4001

4. try insert more rows, result in duplicate keys of _tidb_rowid

insert into t (b) values (4), (5), (6);
-- ERROR 1062 (23000): Duplicate entry '1' for key 't.PRIMARY'

5. after _tidb_rowid increases to the available zone, we see the primary key's auto-inc values have a gap

insert into t (b) values (7), (8), (9);
select * from t;

+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
| 4004 |    7 |
| 4005 |    8 |
| 4006 |    9 |
+------+------+

4. What is your TiDB version? (Required)

Release Version: v7.1.1
Edition: Community
Git Commit Hash: cf441574864be63938524e7dfcf7cc659edc3dd8
Git Branch: heads/refs/tags/v7.1.1
UTC Build Time: 2023-07-19 10:20:53
GoVersion: go1.20.6
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

[kennytm]

cc #44716.

[jackysp]

PTAL @tiancaiamao

[tiancaiamao]

OK, I find the root cause is that in the NONCLUSTERED case, we have both tidb_rowid and auto_inc_id to be recovered. But the model.TableInfo does not have enough information for recovering.

Before #39041, model.TableInfo.AutoIncID is enough for recovering the table, because auto_inc_id use tidb_rowid meta key, they are the same.

After we seperate the meta key, the both need their own field in model.TableInfo.
Maybe we need to add something similiar to AUTO_INCREMENT=22032164310 like TIDB_ROWID=xxx

CREATE TABLE `t` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=4 TIDB_ROWID=30000 /*T![auto_id_cache] AUTO_ID_CACHE=1 */

[bolt-leandro]

Hi, the same problem happens even with CLUSTERED primary key. Version is 7.1.1.

mysql> create table t (a int primary key clustered auto_increment, b int) auto_id_cache = 1;
Query OK, 0 rows affected (0.08 sec)

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

mysql> insert into t (b) values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show table t next_row_id;
+---------+------------+-------------+--------------------+----------------+
| DB_NAME | TABLE_NAME | COLUMN_NAME | NEXT_GLOBAL_ROW_ID | ID_TYPE        |
+---------+------------+-------------+--------------------+----------------+
| test    | t          | a           |                  1 | _TIDB_ROWID    |
| test    | t          | a           |                  4 | AUTO_INCREMENT |
+---------+------------+-------------+--------------------+----------------+
2 rows in set (0.00 sec)

mysql> backup table t to 'local:///tmp/br';
+-----------------+------+--------------------+---------------------+---------------------+
| Destination     | Size | BackupTS           | Queue Time          | Execution Time      |
+-----------------+------+--------------------+---------------------+---------------------+
| local:///tmp/br | 1804 | 443600838315999235 | 2023-08-16 17:21:44 | 2023-08-16 17:21:44 |
+-----------------+------+--------------------+---------------------+---------------------+
1 row in set (0.04 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.23 sec)

mysql> restore table t from 'local:///tmp/br';
+-----------------+------+--------------------+--------------------+---------------------+---------------------+
| Destination     | Size | BackupTS           | Cluster TS         | Queue Time          | Execution Time      |
+-----------------+------+--------------------+--------------------+---------------------+---------------------+
| local:///tmp/br | 1804 | 443600838315999235 | 443600841107832836 | 2023-08-16 17:21:55 | 2023-08-16 17:21:55 |
+-----------------+------+--------------------+--------------------+---------------------+---------------------+
1 row in set (0.55 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> show table t next_row_id;
+---------+------------+-------------+--------------------+----------------+
| DB_NAME | TABLE_NAME | COLUMN_NAME | NEXT_GLOBAL_ROW_ID | ID_TYPE        |
+---------+------------+-------------+--------------------+----------------+
| test    | t          | a           |                  1 | _TIDB_ROWID    |
| test    | t          | a           |               4001 | AUTO_INCREMENT |
+---------+------------+-------------+--------------------+----------------+
2 rows in set (0.00 sec)

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

[jackysp]

Hi, the same problem happens even with CLUSTERED primary key. Version is 7.1.1.

mysql> create table t (a int primary key clustered auto_increment, b int) auto_id_cache = 1;
Query OK, 0 rows affected (0.08 sec)

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

mysql> insert into t (b) values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show table t next_row_id;
+---------+------------+-------------+--------------------+----------------+
| DB_NAME | TABLE_NAME | COLUMN_NAME | NEXT_GLOBAL_ROW_ID | ID_TYPE        |
+---------+------------+-------------+--------------------+----------------+
| test    | t          | a           |                  1 | _TIDB_ROWID    |
| test    | t          | a           |                  4 | AUTO_INCREMENT |
+---------+------------+-------------+--------------------+----------------+
2 rows in set (0.00 sec)

mysql> backup table t to 'local:///tmp/br';
+-----------------+------+--------------------+---------------------+---------------------+
| Destination     | Size | BackupTS           | Queue Time          | Execution Time      |
+-----------------+------+--------------------+---------------------+---------------------+
| local:///tmp/br | 1804 | 443600838315999235 | 2023-08-16 17:21:44 | 2023-08-16 17:21:44 |
+-----------------+------+--------------------+---------------------+---------------------+
1 row in set (0.04 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.23 sec)

mysql> restore table t from 'local:///tmp/br';
+-----------------+------+--------------------+--------------------+---------------------+---------------------+
| Destination     | Size | BackupTS           | Cluster TS         | Queue Time          | Execution Time      |
+-----------------+------+--------------------+--------------------+---------------------+---------------------+
| local:///tmp/br | 1804 | 443600838315999235 | 443600841107832836 | 2023-08-16 17:21:55 | 2023-08-16 17:21:55 |
+-----------------+------+--------------------+--------------------+---------------------+---------------------+
1 row in set (0.55 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> show table t next_row_id;
+---------+------------+-------------+--------------------+----------------+
| DB_NAME | TABLE_NAME | COLUMN_NAME | NEXT_GLOBAL_ROW_ID | ID_TYPE        |
+---------+------------+-------------+--------------------+----------------+
| test    | t          | a           |                  1 | _TIDB_ROWID    |
| test    | t          | a           |               4001 | AUTO_INCREMENT |
+---------+------------+-------------+--------------------+----------------+
2 rows in set (0.00 sec)

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

Could you please take a look @kennytm @tiancaiamao ? Thanks!

[tiancaiamao]

With CLUSTERED, the AUTO_INCREMENT is recorded, this bug does not affect that scene.
You can see that before the backup, AUTO_INCREMENT = 4
and after recover, AUTO_INCREMENT = 4001
There is a 'hole' in this auto id sequence, but it is expected, we lose some ids that are in the memory cache of auto-id service's