Error Occurred When Migrating and Writing to TiDB from MySQL

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: mysql迁移写入TiDB时出错

| username: TiDBer_kmB0dl9H

[Test Environment for TiDB] Testing
[Encountered Issue: Problem Phenomenon and Impact] Displays primary key conflict, how to resolve incompatibility, attached MySQL table creation statement
[Attachment: Screenshot/Log/Monitoring]


| username: 小龙虾爱大龙虾 | Original post link

It looks like your data has a conflict based on the error message.

| username: 小龙虾爱大龙虾 | Original post link

Is the target end restoring an empty table?

| username: TiDBer_kmB0dl9H | Original post link

Is there data that can be overwritten?

| username: 小龙虾爱大龙虾 | Original post link

You can choose different handling methods, refer to: 使用逻辑导入模式 | PingCAP 文档中心

| username: 江湖故人 | Original post link

Is it a logical import? Could it be that the target table is not empty, causing a primary key conflict?

| username: Kongdom | Original post link

:thinking: If the table structures on both sides are consistent, could it be a case sensitivity issue? MySQL is case-sensitive, while TiDB is case-insensitive. This could lead to primary key conflicts when importing from MySQL to TiDB.

| username: 小龙虾爱大龙虾 | Original post link

His should not be, the primary key is of int type.

| username: 这里介绍不了我 | Original post link

Go check this record in the original table.

| username: Kongdom | Original post link

I just noticed it’s an auto-increment primary key. When everyone imports data, do they usually import the auto-increment primary key column as well?

| username: 小龙虾爱大龙虾 | Original post link

Another possibility is that the source table has id=0.

| username: 这里介绍不了我 | Original post link

When exporting with id=0, a new id will also be generated, right?

| username: Kongdom | Original post link

:thinking: If it’s migrating a specified ID, it shouldn’t auto-increment and should use the specified value, right?

| username: 小龙虾爱大龙虾 | Original post link

Some time ago, I encountered a problem where a primary key conflict was discovered after migration because the source end had id=0. The solution was the NO_AUTO_VALUE_ON_ZERO SQL mode.

| username: TIDB-Learner | Original post link

Duplicate data refers to duplicate primary keys and unique keys. Should the id field of the TiDB table be changed to AUTO_RANDOM?

| username: 哈喽沃德 | Original post link

Is it multiple data sources? Or was a part migrated before, and then a full migration was performed later?

| username: tidb菜鸟一只 | Original post link

Duplicate data, you can either select, replace, or skip it…

| username: changpeng75 | Original post link

If there are multiple data sources to the same target, it’s better to determine the ETL solution based on business logic.

| username: dba远航 | Original post link

For primary key conflicts, you can only choose to skip or delete the records in the target table, or modify the primary key values.

| username: Inkjade | Original post link

Your data has a problem. There is no primary key, and data conflicts have occurred.