Error When Using DM for Data Synchronization Between MySQL and TiDB

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

Original topic: 使用dm做MySQL到TiDB之间的数据同步报错

| username: terry0219

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.1.6
[Encountered Problem: Problem Phenomenon and Impact] When using DM to synchronize data to downstream TiDB, an error occurs because the upstream MySQL performed a table change operation, causing DM synchronization to report the error “Error 1412: Table definition has changed, please retry transaction.” How should this be handled? The DM version is v6.1.6.

| username: Billmay表妹 | Original post link

When the table structure of the upstream MySQL database changes, the DM synchronization task may report an error with the message “Error 1412: Table definition has changed, please retry transaction.” This is because MySQL locks the table when performing DDL operations, which can cause the synchronization task to fail if DM is synchronizing that table.

To solve this problem, you can try the following methods:

  1. When performing DDL operations on the upstream MySQL, try to avoid modifying tables that are being synchronized by DM. You can perform DDL operations during off-peak hours or use online DDL tools such as pt-online-schema-change.
  2. In the DM configuration file, you can enable ANSI quotes mode by setting the enable-ansi-quotes parameter to true. This way, when synchronizing DDL statements, DM will enclose table names and column names in double quotes to avoid conflicts with MySQL keywords. For example:
mysql-instances:
  - source-id: "mysql-replica-01"
    ...
    mydumper-config-name: "global"
    loader-config-name: "global"
    syncer-config-name: "global"
    enable-ansi-quotes: true

Note that enabling ANSI quotes mode may cause some MySQL clients to be unable to connect to TiDB properly because these clients do not support ANSI quotes mode. Therefore, when enabling this mode, you need to test the application to ensure there are no compatibility issues.
3. If the above methods do not solve the problem, you can try setting the safe-mode parameter to false in the DM configuration file to disable DM’s safe mode. This way, when synchronizing DDL statements, DM will directly execute the ALTER TABLE statement instead of using PT tools for online DDL operations. For example:

mysql-instances:
  - source-id: "mysql-replica-01"
    ...
    mydumper-config-name: "global"
    loader-config-name: "global"
    syncer-config-name: "global"
    safe-mode: false

Note that disabling safe mode may lead to data inconsistency or data loss issues. Therefore, when using this mode, you need to be extra cautious to ensure data integrity and consistency.

| username: terry0219 | Original post link

Thank you :+1:

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.