DM同步MySQL数据到TiDB时报错

老鹰506 · 2024 年11 月 29 日 14:59

【 TiDB 使用环境】生产环境
【 TiDB 版本】7.5.4

使用dm同步阿里云RDS 数据到 TiDB 7.5.3版本时，Dump 和 Load 阶段都没有报错，但是在Sync阶段报错，具体报错信息如下

$ tiup dmctl --master-addr 192.168.3.156:8261 query-status rds-to-tidb-for-shopdb
tiup is checking updates for component dmctl ...
A new version of dmctl is available:
   The latest version:         v8.4.0
   Local installed version:    v8.2.0
   Update current component:   tiup update dmctl
   Update all components:      tiup update --all

Starting component `dmctl`: /home/tidb/.tiup/components/dmctl/v8.2.0/dmctl/dmctl --master-addr 192.168.3.156:8261 query-status rds-to-tidb-for-shopdb
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "",
            "sourceStatus": {
                "source": "rds-shopdb",
                "worker": "dm-192.168.3.153-8262",
                "result": null,
                "relayStatus": {
                    "masterBinlog": "(mysql-bin.002619, 11239063)",
                    "masterBinlogGtid": "11d06196-a8b8-11eb-afec-0c42a1121b4c:1-103942405,406f2881-ac8f-11ed-8993-043f72a0fbbe:1-38591245,cd058783-aa96-11ec-a8bf-0c42a1df22ac:1-113402275",
                    "relaySubDir": "406f2881-ac8f-11ed-8993-043f72a0fbbe.000001",
                    "relayBinlog": "(mysql-bin.002619, 11239063)",
                    "relayBinlogGtid": "11d06196-a8b8-11eb-afec-0c42a1121b4c:1-103942405,406f2881-ac8f-11ed-8993-043f72a0fbbe:1-38591245,cd058783-aa96-11ec-a8bf-0c42a1df22ac:1-113402275",
                    "relayCatchUpMaster": true,
                    "stage": "Running",
                    "result": null
                }
            },
            "subTaskStatus": [
                {
                    "name": "rds-to-tidb-for-shopdb",
                    "stage": "Paused",
                    "unit": "Sync",
                    "result": {
                        "isCanceled": false,
                        "errors": [
                            {
                                "ErrCode": 36067,
                                "ErrClass": "sync-unit",
                                "ErrScope": "internal",
                                "ErrLevel": "high",
                                "Message": "startLocation: [position: (mysql-bin|000001.002618, 34650758), gtid-set: 11d06196-a8b8-11eb-afec-0c42a1121b4c:1-103942405,406f2881-ac8f-11ed-8993-043f72a0fbbe:1-38536938,cd058783-aa96-11ec-a8bf-0c42a1df22ac:1-113402275], endLocation: [position: (mysql-bin|000001.002618, 34650962), gtid-set: 11d06196-a8b8-11eb-afec-0c42a1121b4c:1-103942405,406f2881-ac8f-11ed-8993-043f72a0fbbe:1-38536939,cd058783-aa96-11ec-a8bf-0c42a1df22ac:1-113402275], origin SQL: [XA START X'3137322e31372e3131362e3134372e746d313733323837343431383338343030303730',X'3137322e31372e3131362e3134372e746d313339',1096044365]: parse DDL: XA START X'3137322e31372e3131362e3134372e746d313733323837343431383338343030303730',X'3137322e31372e3131362e3134372e746d313339',1096044365",
                                "RawCause": "line 1 column 2 near \"XA START X'3137322e31372e3131362e3134372e746d313733323837343431383338343030303730',X'3137322e31372e3131362e3134372e746d313339',1096044365\" ",
                                "Workaround": "Please confirm your DDL statement is correct and needed. For TiDB compatible DDL, see https://docs.pingcap.com/tidb/stable/mysql-compatibility#ddl. You can use `handle-error` command to skip or replace the DDL or add a binlog filter rule to ignore it if the DDL is not needed."
                            }
                        ],
                        "detail": null
                    },
                    "unresolvedDDLLockID": "",
                    "sync": {
                        "totalEvents": "2018",
                        "totalTps": "67",
                        "recentTps": "0",
                        "masterBinlog": "(mysql-bin.002619, 11239063)",
                        "masterBinlogGtid": "11d06196-a8b8-11eb-afec-0c42a1121b4c:1-103942405,406f2881-ac8f-11ed-8993-043f72a0fbbe:1-38591245,cd058783-aa96-11ec-a8bf-0c42a1df22ac:1-113402275",
                        "syncerBinlog": "(mysql-bin|000001.002618, 34650693)",
                        "syncerBinlogGtid": "11d06196-a8b8-11eb-afec-0c42a1121b4c:1-103942405,406f2881-ac8f-11ed-8993-043f72a0fbbe:1-38536938,cd058783-aa96-11ec-a8bf-0c42a1df22ac:1-113402275",
                        "blockingDDLs": [
                        ],
                        "unresolvedGroups": [
                        ],
                        "synced": false,
                        "binlogType": "local",
                        "secondsBehindMaster": "0",
                        "blockDDLOwner": "",
                        "conflictMsg": "",
                        "totalRows": "2018",
                        "totalRps": "67",
                        "recentRps": "0"
                    },
                    "validation": null
                }
            ]
        }
    ]
}

这个错信息 XA START X’3137322e31372e3131362e3134372e746d313733323837343431383338343030303730’,X’3137322e31372e3131362e3134372e746d313339’,1096044365 是MySQL的分布式事务

看TiDB官网文档，有个信息

是直接不支持XA 事务么

这个有没有其他办法呢？

有猫万事足 · 2024 年11 月 29 日 15:14

从业务上看确实需要分布式事务支持嘛？

如果不需要，是否可以通过 innodb_support_xa 参数关闭这个功能。

kevinsna · 2024 年12 月 1 日 00:28

检查应用代码，避免使用XA事务，或者将XA事务替换为本地事务。如果业务逻辑允许，这是最推荐的解决方案，如果您无法修改应用代码，可以考虑在DM中使用binlog过滤规则来忽略这些XA事务相关的命令。这可以通过添加binlog filter规则来实现，使得这些命令不会被同步到TiDB，看了下当前的tidb 8.4仍旧是不支持xa语法的

老鹰506 · 2024 年12 月 4 日 13:28

业务上反馈没有用，我对比了本地和阿里云，这个参数都是ON的状态，但是本地自建的MySQL5.7 的binlog中就没有这个XA 记录，阿里云的RDS 就有。

所以目前也很奇怪这个到底是怎么出现的

老鹰506 · 2024 年12 月 4 日 13:30

我使用 dm同步的时候配置的filter不生效的，配置如下，求救哪里配置的不对么

... ... 
filters:
  filter-ignore-xa:
    schema-pattern: "shop"
    table-pattern: "*"
    events: ["^XA "]
    action: Ignore
...

mysql-instances:
  -
    source-id: "rds-shopdb"

    route-rules: ["route-rule-1"]
    filter-rules: ["filter-ignore-xa"]
    block-allow-list: "block-rule-1"
    mydumper-config-name: "global"
    loader-config-name: "global"
    syncer-config-name: "global"

有猫万事足 · 2024 年12 月 8 日 11:33

既然没用到这个功能，尝试关闭再试试好了。

老鹰506 · 2024 年12 月 9 日 06:28

实际验证是关闭不了这个参数的，执行set global 没有报错，但是有个warn信息，查看warn信息提示不支持修改

root@192.168.1.xxx:3306 14:26:  [(none)] >show variables like 'innodb_support_xa';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_support_xa | ON    |
+-------------------+-------+
1 row in set (0.01 sec)

root@192.168.1.xxx:3306 14:27:  [(none)] >set global innodb_support_xa = OFF ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@192.168.1.xxx:3306 14:27:  [(none)] >show warnings ;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                          |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------+
| Warning |  131 | Using innodb_support_xa is deprecated and the parameter may be removed in future releases. Only innodb_support_xa=ON is allowed. |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@192.168.1.xxx:3306 14:27:  [(none)] >show variables like 'innodb_support_xa';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_support_xa | ON    |
+-------------------+-------+
1 row in set (0.00 sec)

后来通过在DM task配置中过滤这个XA 操作实现了同步

system · 2024 年12 月 16 日 06:29

此话题已在最后回复的 7 天后被自动关闭。不再允许新回复。