after importing NONCLUSTERED auto_increment and AUTO_ID_CACHE=1 table by lightning，insert report "duplicate entry"

[mayjiang0203]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Following is schema that will import by lighting

cat smalldb.t1-schema.sql
CREATE TABLE `t1` (
    `cacheid` int(11) NOT NULL AUTO_INCREMENT,
    `rk` varchar(16) NOT NULL,
    `cfq` varchar(10) NOT NULL,
    `ts` bigint(20) NOT NULL,
    `value` varchar(2048) DEFAULT NULL,
    PRIMARY KEY(`cacheid`) NONCLUSTERED)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_ID_CACHE 1;

After import finished, hit following error when try to insert new rows.

mysql> show table t1 next_row_id;
+---------+------------+-------------+--------------------+----------------+
| DB_NAME | TABLE_NAME | COLUMN_NAME | NEXT_GLOBAL_ROW_ID | ID_TYPE        |
+---------+------------+-------------+--------------------+----------------+
| smalldb | t1         | _tidb_rowid |                  1 | _TIDB_ROWID    |
| smalldb | t1         | _tidb_rowid |          577898371 | AUTO_INCREMENT |
+---------+------------+-------------+--------------------+----------------+
2 rows in set (0.21 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  3960000 |
+----------+
1 row in set (0.78 sec)

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                              |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `cacheid` int(11) NOT NULL AUTO_INCREMENT,
  `rk` varchar(16) NOT NULL,
  `cfq` varchar(10) NOT NULL,
  `ts` bigint(20) NOT NULL,
  `value` varchar(2048) DEFAULT NULL,
  PRIMARY KEY (`cacheid`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=577898371 /*T![auto_id_cache] AUTO_ID_CACHE=1 */ |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.20 sec)

mysql> select max(cacheid) from t1;
+--------------+
| max(cacheid) |
+--------------+
|      3960000 |
+--------------+
1 row in set (0.20 sec)

mysql> insert into t1(rk,cfq,ts) values('rk_txxxxxxxxx','cfg_xxxxx',7);
ERROR 1062 (23000): Duplicate entry '?' for key 't1.PRIMARY'

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

v7.3.0

[mayjiang0203]

same with #46093

[D3Hunter]

reopen it for lightning side

[D3Hunter]

this affects >=6.5 version, when enabling AUTO_ID_CACHE=1(auto_id and _tidb_rowid use different allocator), and table has implicit _tidb_rowid col

[seiya-annie]

need add a system test case for lightning by QA