lightning: auto increment value became much larger than expected after physical import with parallel import enabled

[kabileshKabi]

Bug Report

We made a cut-over to TiDB

The table is Messages table with Auto increment on "ID"
to maintain the ID sequence the table was created with "auto_id_cache" as below

**) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=730234878 /T![auto_id_cache] AUTO_ID_CACHE=1 /

In the MySQL last sequence was "14609942" but in TiDB ID was incremented by a vast number "730170668" pl find as below

mysql> select id,updatedAt from messages where id >=14609942 limit 10;
+-----------+------------+
| id | updatedAt |
+-----------+------------+
| 14609942 | 1729291247 | ----> MySQL
| 730170668 | 1729313914 | -----> TIDB
| 730170669 | 1729313914 |
| 730170670 | 1729313914 |
| 730170671 | 1729313914 |
| 730170672 | 1729313914 |
| 730170673 | 1729313914 |
| 730170674 | 1729313914 |
| 730170675 | 1729313914 |
| 730170676 | 1729313914 |
+-----------+------------+

the TIDB has added or incremented the value on its own, This is a critical bug making TiDB unusable in production

1. Minimal reproduce step (Required)

1,Create the table structure same as MySQL also have auto_id_cache=1 to maintain compatibility.
2, Migrate the data to TiDB using DM and setup replication. till this point ID would be matching
3, Stop or Pause the DM
4, Point the application directly to use TiDB, you can see a huge jump in auto increment value.

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

we expected the same sequence to be continue with TiDB, making it compatible

3. What did you see instead (Required)

we see a complete new ID sequence has been introduced, breaking the application

4. What is your TiDB version? (Required)

Release Version: v8.1.1
Edition: Community
Git Commit Hash: a7df4f9
Git Branch: HEAD
UTC Build Time: 2024-08-22 05:49:03
GoVersion: go1.21.13
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[dveeden]

Could you add the output of SHOW CREATE TABLE ... for

• MySQL
• TiDB before starting DM
• TiDB after starting DM

What does SELECT MAX(id) FROM ... return?

What was the task-mode in DM set to for this? Did you specify a import-mode ?

[kabileshKabi]

Table Structure with MySQL

CREATE TABLE `messages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `muid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `conversationId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sender` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `receiverType` enum('group','user') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'user',
  `receiver` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `category` enum('message','action','call','event','custom','interactive') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'text',
  `data` json NOT NULL,
  `senderOnly` tinyint(1) NOT NULL DEFAULT '0',
  `sentAt` int NOT NULL,
  `updatedAt` int DEFAULT NULL,
  `editedBy` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `editedAt` int DEFAULT NULL,
  `deletedBy` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deletedAt` int DEFAULT NULL,
  `deliveredAt` int DEFAULT NULL,
  `readAt` int DEFAULT NULL,
  `sentAtFPid` decimal(30,20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `messages_sender_index` (`sender`),
  KEY `messages_receiver_index` (`receiver`),
  KEY `messages_receivertype_index` (`receiverType`),
  KEY `messages_sentat_index` (`sentAt`),
  KEY `messages_deliveredat_index` (`deliveredAt`),
  KEY `messages_readat_index` (`readAt`),
  KEY `messages_editedat_index` (`editedAt`),
  KEY `messages_deletedat_index` (`deletedAt`),
  KEY `messages_updatedat_index` (`updatedAt`),
  KEY `msg_category_type` (`category`,`type`),
  KEY `msg_type` (`type`),
  KEY `msg_convoid_sender_senderonly` (`conversationId`,`sender`,`senderOnly`),
  KEY `msg_convoid_receivertype_receiver` (`conversationId`,`receiverType`,`receiver`),
  KEY `msg_convoid_category_type` (`conversationId`,`category`,`type`),
  KEY `msg_convoid_type` (`conversationId`,`type`),
  KEY `msg_convoid_sentat_id` (`conversationId`,`sentAt`,`id`),
  KEY `convoid_sender_deliveredat` (`conversationId`,`sender`,`deliveredAt`),
  KEY `convoid_sender_sentat` (`conversationId`,`sender`,`sentAt`),
  KEY `msg_sentatfpid` (`sentAtFPid`),
  KEY `msg_muid` (`muid`),
  KEY `convoid_sentatfpid_index` (`conversationId`,`sentAtFPid` DESC)
) ENGINE=InnoDB  AUTO_INCREMENT=14430410 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Table structure created in TIDB with Auto_ID_CACHE before starting DM,

CREATE TABLE `messages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `muid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `conversationId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sender` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `receiverType` enum('group','user') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'user',
  `receiver` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `category` enum('message','action','call','event','custom','interactive') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'text',
  `data` json NOT NULL,
  `senderOnly` tinyint(1) NOT NULL DEFAULT '0',
  `sentAt` int NOT NULL,
  `updatedAt` int DEFAULT NULL,
  `editedBy` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `editedAt` int DEFAULT NULL,
  `deletedBy` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deletedAt` int DEFAULT NULL,
  `deliveredAt` int DEFAULT NULL,
  `readAt` int DEFAULT NULL,
  `sentAtFPid` decimal(30,20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `messages_sender_index` (`sender`),
  KEY `messages_receiver_index` (`receiver`),
  KEY `messages_receivertype_index` (`receiverType`),
  KEY `messages_sentat_index` (`sentAt`),
  KEY `messages_deliveredat_index` (`deliveredAt`),
  KEY `messages_readat_index` (`readAt`),
  KEY `messages_editedat_index` (`editedAt`),
  KEY `messages_deletedat_index` (`deletedAt`),
  KEY `messages_updatedat_index` (`updatedAt`),
  KEY `msg_category_type` (`category`,`type`),
  KEY `msg_type` (`type`),
  KEY `msg_convoid_sender_senderonly` (`conversationId`,`sender`,`senderOnly`),
  KEY `msg_convoid_receivertype_receiver` (`conversationId`,`receiverType`,`receiver`),
  KEY `msg_convoid_category_type` (`conversationId`,`category`,`type`),
  KEY `msg_convoid_type` (`conversationId`,`type`),
  KEY `msg_convoid_sentat_id` (`conversationId`,`sentAt`,`id`),
  KEY `convoid_sender_deliveredat` (`conversationId`,`sender`,`deliveredAt`),
  KEY `convoid_sender_sentat` (`conversationId`,`sender`,`sentAt`),
  KEY `msg_sentatfpid` (`sentAtFPid`),
  KEY `msg_muid` (`muid`),
  KEY `convoid_sentatfpid_index` (`conversationId`,`sentAtFPid` DESC)
) ENGINE=InnoDB  AUTO_INCREMENT=14430410 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_ID_CACHE=1;

DM was started with "full" import mode with replication, we used physical import.

Auto-increment was fine in TiDB as well since Binlog format was FULL and replication happening from mysql

During the day of Cut-over we paused the DM task

last ID synced from mysql

+-----------+------------+
| id | updatedAt |
+-----------+------------+
| 14609942 | 1729291247 | ----> MySQL

When Application was directly pointed to TiDB from application it generated id starting with "730170668"

+-----------+------------+
| id | updatedAt |
+-----------+------------+
| 730170668 | 1729313914 | -----> TIDB

[dveeden]

Side note, you might want to use markdown to format code and output:

https://docs.github.com/en/get-started/writing-on-github/getting-started-with-writing-and-formatting-on-github/basic-writing-and-formatting-syntax#quoting-code

[dveeden]

I tried to reproduce this, but failed to do so:

MySQL 8.0.40 with:

CREATE TABLE t1 (id int AUTO_INCREMENT PRIMARY KEY) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
INSERT INTO t1 values (100);
INSERT INTO t1() values ();

TiDB v8.1.1 with:

CREATE TABLE t1 (id int AUTO_INCREMENT PRIMARY KEY) AUTO_ID_CACHE=1 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

DM task with task-mode: full

Result after stopping the DM task:

mysql-8.0.11-TiDB-v8.1.1> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=102 /*T![auto_id_cache] AUTO_ID_CACHE=1 */
1 row in set (0.00 sec)

mysql-8.0.11-TiDB-v8.1.1> TABLE t1;
+-----+
| id  |
+-----+
| 100 |
| 101 |
+-----+
2 rows in set (0.00 sec)

mysql-8.0.11-TiDB-v8.1.1> INSERT INTO t1() VALUES ();
Query OK, 1 row affected (0.01 sec)

mysql-8.0.11-TiDB-v8.1.1> TABLE t1;
+-----+
| id  |
+-----+
| 100 |
| 101 |
| 102 |
+-----+
3 rows in set (0.00 sec)

mysql-8.0.11-TiDB-v8.1.1> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=103 /*T![auto_id_cache] AUTO_ID_CACHE=1 */
1 row in set (0.00 sec)

[kabileshKabi]

Hope you tried with the physical import also after making the replication pls insert some via replication and then pause the DM task and make a direct write to TiDB

[bb7133]

Hi @kabileshKabi , thanks for reporting this issue.

According to the reproduction steps and other comments, you mentioned both 'DM' and 'Physical Import'(which is a concept of Lightning / IMPORT statement), could you please provide more details about how they're used in each step, so we're able to have an analysis?

Also, please notice that:

1. There's a known bug related to the physical import & auto_increment, which is not fixed in v8.1.1 yet(Incorrect AUTO_INCREMENT Value After Data Import Causing Duplicate Key Error(when AUTO_ID_CACHE=1) #56476).
2. As a workaround for your migration, you can use ALTER TABLE ... FORCE AUTO_INCREMENT=... to rebase the auto_increment value to a specific value(14609942 for your case).
3. As a distributed system, we do not guarantee AUTO_INCREMENT to be 100% compatible with MySQL in all cases, even when the AUTO_ID_CACHE is set to 1(see the MySQL compatibility mode part of the documentation).

[siddontang]

Hello, @kabileshKabi

From your comment above, I found

**) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=730234878 /T![auto_id_cache] AUTO_ID_CACHE=1 /

It seems that you use don't use the same AUTO_INCREMENT between MySQL and TiDB, for TiDB, it may be AUTO_INCREMENT=730234878. So if you insert data into TiDB directly, the ID may be from 730234878, not from the value set in MySQL.

I tried to reproduce:

Step 1

For MySQL

CREATE TABLE `messages` (   `id` bigint unsigned NOT NULL AUTO_INCREMENT,   `updatedAt` int DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB  AUTO_INCREMENT=14430410 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

For TiDB

CREATE TABLE `messages` (   `id` bigint unsigned NOT NULL AUTO_INCREMENT,   `updatedAt` int DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB  AUTO_INCREMENT=74430410 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Step 2

For MySQL

insert into message (updatedAt) values (now());

then start the DM replication

For TiDB

select * from messages;
+----------+------------+
| id       | updatedAt  |
+----------+------------+
| 14430410 | 1730085690 |
+----------+------------+

Step 3

stop the DM replication

For TiDB

insert into message (updatedAt) values (now());

Then select in TiDB

select * from messages;
+----------+------------+
| id       | updatedAt  |
+----------+------------+
| 14430410 | 1730085690 |
| 74460410 | 1730086179 |
+----------+------------+

you can see different id here. I guess this meets the expectation? please correct me if I am wrong @bb7133 @dveeden

[bb7133]

@siddontang

CREATE TABLE `messages` (   `id` bigint unsigned NOT NULL AUTO_INCREMENT,   `updatedAt` int DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB  AUTO_INCREMENT=74430410 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

If this is the original create table syntax, it's expected that the next ID allocated starts from 74430410.

But I'm not sure if this syntax is generated manually or by DM.

[kabileshKabi]

@siddontang your previous comment is the issue i exactly face, when directly Inserted in TiDB the Autoincrement ID gets leaped

[kabileshKabi]

Below Are the exact steps followed:
1, Dump the structure from MySQL
2, Import the structure file into TiDB by adding AUTO_ID_CACHE
3, Start the DM with Physical import and FULL mode
4, replication started between MySQL and TiDB

Till this point ID is matching between MySQL and TiDB

On the day of cut-over we paused the DM task replication and pointed the writes directly to TiDB --> This is when we see the jump in Auti-increment ID.

@siddontang @bb7133 @dveeden

[bb7133]

3, Start the DM with Physical import and FULL mode

@kabileshKabi About 'Physical Import' of DM, is it some argument or configuration of DM? I didn't find such a thing in DM document.

[kabileshKabi]

These are the configs exactly used in DM @bb7133

task-mode: all
import-mode: "physical"