DM 同步 XA START 报错问题

🪐 TiDB 技术问题 备份&数据迁移
1

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

  • 【TiDB 版本】：4.0.0
  • 【问题描述】：报错如下：
    “Message”: “current pos (mysql-bin|000001.000669, 219047745): encountered incompatible DDL in TiDB:\ \tplease confirm your DDL statement is correct and needed.\ \tfor TiDB compatible DDL, please see the docs:\ \t English version: https://pingcap.com/docs/dev/reference/mysql-compatibility/#ddl\ \t Chinese version: https://pingcap.com/docs-cn/dev/reference/mysql-compatibility/#ddl\ \tif the DDL is not needed, you can use a filter rule with “*” schema-pattern to ignore it.\ \t : parse statement: line 1 column 2 near “XA START X’31302e37312e36342e35322e746d313539343131343937373035313030303136’,X’31302e37312e36342e35322e746d3332’,1096044365” %!!(MISSING)(EXTRA string=XA START X’31302e37312e36342e35322e746d313539343131343937373035313030303136’,X’31302e37312e36342e35322e746d3332’,1096044365)”,
    “RawCause”: "line 1 column 2 near “XA START X’31302e37312e36342e35322e746d313539343131343937373035313030303136’,X’31302e37312e36342e35322e746d3332’,1096044365”

按照https://asktug.com/t/topic/2711中的方法过滤XA事务，更新task配置后重新启动，但报错依旧，还请帮忙确认下问题情况，谢谢。
更新的配置：
filter-rules:

  • mysql-replica-06.filter.1

mysql-replica-06.filter.1:
schema-pattern: broker_*
table-pattern: “”
events: [“XA START”,“XA END”,“XA COMMIT”,“XA PREPARE”,“XA ROLLBACK”,“XA RECOVER”,“XA BEGIN”]
action: Ignore

2

请参考文档

https://docs.pingcap.com/zh/tidb-data-migration/v1.0/skip-or-replace-abnormal-sql-statements#sql-skip

3

以下2种方式都试过了
一
sql-skip -w 1x.xx.xx.xxx:8262 --sql-pattern=~(?i)XA\s+START --sharding client_baseinfo
resume-task client_baseinfo

image
image1511×583 69.4 KB

二：
sql-skip -w 1x.xx.xx.xxx:8262 --binlog-pos=mysql-bin.000669:219047499 client_baseinfo
sql-skip -w 1x.xx.xx.xxx:8262 --binlog-pos=mysql-bin.000669:219048845 client_baseinfo
resume-task client_baseinfo

binlog文件选自：
syncerBinlog": "(mysql-bin|000001.000669, 219047499)
“relayBinlog”: “(mysql-bin.000669, 219048845)”,

image
image1439×626 54.6 KB

最终结果都还是报错了

4

sql-skip 不能在这种语法不支持的 DDL 里使用，需要通过 binlog filter 进行过滤

5

麻烦检查一下任务配置文件，配置的 filter 是否有生效。
另外 table-pattern 不配置，或者配置为 table-pattern: "*"

6

整个配置文件如下，个人感觉不出哪里配置异常，还请帮忙确认下，谢谢。

name: client_baseinfo
task-mode: all
is-sharding: false
ignore-checking-items: [“auto_increment_ID”]
target-database:
host: 1x.xx.xxx.xxx #ip隐藏
port: 4000
user: root
password: QWTZgW5HWnyEiN5+O6nIlkA=
mysql-instances:

  • source-id: mysql-replica-06
    meta:
    binlog-name: mysql-bin.0000001
    binlog-pos: 4
    filter-rules:

    • mysql-replica-06.filter.1
      route-rules:
    • mysql-replica-06.route_rules.1
    • mysql-replica-06.route_rules.2

    black-white-list: mysql-replica-06.bw_list.1
    mydumper-config-name: mysql-replica-06.dump
    routes:
    mysql-replica-06.route_rules.1:
    schema-pattern: hdb_broker_*
    table-pattern: “”
    target-schema: hdb_broker
    target-table: “”
    mysql-replica-06.route_rules.2:
    schema-pattern: hdb_broker_*
    table-pattern: client_baseinfo
    target-schema: hdb_broker
    target-table: client_baseinfo

filters:
mysql-replica-06.filter.1:
schema-pattern: hdb_broker_*
table-pattern: “*”
events: [“XA START”,“XA END”,“XA COMMIT”,“XA PREPARE”,“XA ROLLBACK”,“XA RECOVER”,“XA BEGIN”]
action: Ignore

black-white-list:
mysql-replica-06.bw_list.1:
do-tables:
- db-name: hdb_broker_*
tbl-name: client_baseinfo

do-dbs: []
ignore-tables:
- db-name: hdb_broker_*
  tbl-name: client_baseinfo

ignore-dbs: []

mydumpers:
mysql-replica-06.dump:
mydumper-path: bin/mydumper
threads: 4
chunk-filesize: 64
skip-tz-utc: true

7

参考下 qq悟空 给你的答复呢？

8

filters:
mysql-replica-06.filter.1:
schema-pattern: “*”
sql-pattern: [“XA PREPARE”, “XA START”, “XA END”, “XA COMMIT”]
action: Ignore

用以上方法已经可以跳过XA的报错，但是 如果这个参数schema-pattern配置成hdb_broker_就无法跳过，还请确认下这个参数的情况。
schema-pattern: hdb_broker_

9

应该是可以跳过，是不是要跳过的table-pattern 配置的不对？

10

我之前把table-pattern直接删除了也是报错，所以应该是和这个没关的，现在不确定
schema-pattern 这个参数为何要用 * 来表示就可以跳过。暂时不理会了……多谢大佬的支持。

11

:handshake: 好的，下次可以再试试，感谢答复。

12

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