Incorrect AUTO_INCREMENT Value After Data Import Causing Duplicate Key Error(when AUTO_ID_CACHE=1)

[dulao5]

Bug Report

Problem:
After importing data into a table, the AUTO_INCREMENT value is not updated to the correct value (max(id) + 1), leading to a “Duplicate entry” error on subsequent insertions.

1. Minimal reproduce step (Required)

1. Create a table with an AUTO_INCREMENT attribute set to a specific value (e.g., 5555):

• AUTO_INCREMENT=5555
• AUTO_ID_CACHE=1
• CLUSTERED table

CREATE TABLE `t3` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=5555 /*T![auto_id_cache] AUTO_ID_CACHE=1 */;

mysql> select count(*), max(id) from t3;
+----------+---------+
| count(*) | max(id) |
+----------+---------+
|        0 |    NULL |
+----------+---------+
1 row in set (0.01 sec)

2. Import data into the table:

import into t3 from '/tmp/load_data.csv';

3. After the import, AUTO_INCREMENT remains set to 5555, despite the max(id) being 99999.

mysql> select count(*), max(id) from t3;
+----------+---------+
| count(*) | max(id) |
+----------+---------+
|    99999 |   99999 |
+----------+---------+
1 row in set (0.04 sec)

4. Attempt to insert a new row:

insert into t3 values ();

The system throws a duplicate key error:

ERROR 1062 (23000): Duplicate entry '5555' for key 't3.PRIMARY'

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

After importing data, the AUTO_INCREMENT value should automatically adjust to max(id) + 1 to avoid any duplicate key conflicts.

3. What did you see instead (Required)

The AUTO_INCREMENT attribute remains unchanged, causing a duplicate entry error on subsequent inserts.

4. What is your TiDB version? (Required)

v8.1.0

[tiancaiamao]

import into using physical mode, so auto id service has no way to perceive the changes.
It's still using the old meta key value 5555

[D3Hunter]

after create table with both AUTO_ID_CACHE=1 and AUTO_INCREMENT=1000, id service automatically caches [1000, 4xxx], after physical import, lightning will rebase ID by directly change meta-key of auto-increment column, but id service still use its cached value for insert.

for the reason why we don't use DDL to rebase ids and write meta-key directly instead, see detail in #46171