建议增加DM配置参数,跳过报错binlog

我知道dm支持 binlog-event-filter , 可以

filters:
  rule-1:
    schema-pattern: "test_*"
    ​table-pattern: "t_*"
    ​events: ["truncate table", "drop table"]
    sql-pattern: ["^DROP\\s+PROCEDURE", "^CREATE\\s+PROCEDURE"]
    ​action: Ignore

但是 在query-state报错如下

"msg": "[code=11006:class=functional:scope=internal:level=high] encountered incompatible DDL in TiDB:\n\tplease confirm your DDL statement is correct and needed.\n\tfor TiDB compatible DDL, please see the docs:\n\t  English version: https://pingcap.com/docs/dev/reference/mysql-compatibility/#ddl\n\t  Chinese version: https://pingcap.com/docs-cn/dev/reference/mysql-compatibility/#ddl\n\tif the DDLis not needed, you can use a filter rule with \"*\" schema-pattern to ignore it.\n\t : parse statement: line 4 column 15 near \"PROCEDURE IF EXISTS `auto_create_schedule`\" %!(EXTRA string=-- ----------------------------\r\n-- Procedure structure for auto_create_schedule\r\n-- ----------------------------\r\nDROP PROCEDURE IF EXISTS `auto_create_schedule`)\ngithub.com/pingcap/dm/pkg/terror.(*Error).Delegate\n\t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:267\ngithub.com/pingcap/dm/pkg/parser.Parse\n\t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/parser/common.go:42\ngithub.com/pingcap/dm/syncer.(*Syncer).parseDDLSQL\n\t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/ddl.go:67\ngithub.com/pingcap/dm/syncer.(*Syncer).handleQueryEvent\n\t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1483\ngithub.com/pingcap/dm/syncer.(*Syncer).Run\n\t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1230\ngithub.com/pingcap/dm/syncer.(*Syncer).Process\n\t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:539\ngithub.com/pingcap/dm/syncer.(*Syncer).Resume\n\t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:2188\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1337"

在worker的报错如下

[2019/08/29 08:27:54.786 +08:00] [ERROR] [common.go:35] ["parse statement"] [sql="-- ----------------------------\r\n-- Procedure structure for auto_create_schedule\r\n-- ----------------------------\r\nDROP PROCEDURE IF EXISTS `auto_create_schedule`"] [error="line 4 column 15 near \"PROCEDURE IF EXISTS `auto_create_schedule`\" "]
[2019/08/29 08:27:54.786 +08:00] [ERROR] [ddl.go:70] ["encountered incompatible DDL in TiDB:\n\tplease confirm your DDL statement is correct and needed.\n\tfor TiDB compatible DDL, please see the docs:\n\t  English version: https://pingcap.com/docs/dev/reference/mysql-compatibility/#ddl\n\t  Chinese version: https://pingcap.com/docs-cn/dev/reference/mysql-compatibility/#ddl\n\tif the DDL is not needed, you can use a filter rulewith \"*\" schema-pattern to ignore it.\n\t "] [task=pre] [unit="binlog replication"] [sql="-- ----------------------------\r\n-- Procedure structure for auto_create_schedule\r\n-- ----------------------------\r\nDROP PROCEDURE IF EXISTS `auto_create_schedule`"]
[2019/08/29 08:27:54.786 +08:00] [ERROR] [syncer.go:1485] ["fail to parse statement"] [task=pre] [unit="binlog replication"] [event=query] [statement="-- ----------------------------\r\n-- Procedure structure for auto_create_schedule\r\n-- ----------------------------\r\nDROP PROCEDURE IF EXISTS `auto_create_schedule`"] [schema=slx] ["last position"="(mysql-bin|000001.000003, 203585670)"] [position="(mysql-bin|000001.000003, 203585946)"] ["gtid set"=NULL] [error="[code=11006:class=functional:scope=internal:level=high] encountered incompatible DDL in TiDB:\n\tplease confirm your DDL statement is correct and needed.\n\tfor TiDB compatible DDL, please see the docs:\n\t  English version: https://pingcap.com/docs/dev/reference/mysql-compatibility/#ddl\n\t  Chinese version: https://pingcap.com/docs-cn/dev/reference/mysql-compatibility/#ddl\n\tif the DDL is not needed, you can use a filter rule with \"*\" schema-pattern to ignore it.\n\t : parse statement: line 4 column 15 near \"PROCEDURE IF EXISTS `auto_create_schedule`\" %!(EXTRA string=-- ----------------------------\r\n-- Procedure structure for auto_create_schedule\r\n-- ----------------------------\r\nDROP PROCEDURE IF EXISTS `auto_create_schedule`)"]

虽然可以改正则,但是按照官方的兼容性列表,要加很多正则条件才行。 能否直接增加一个skipError的开关参数?

DM 支持跳过 (skip) 不兼容的 SQL:https://pingcap.com/docs-cn/v3.0/reference/tools/data-migration/skip-replace-sqls/

这个我用过,但是需要找到binlog的偏移量,但是上面这个报错,query-error没有报错信息,查不到偏移量。

  mysql-tidb-replica-03.filters.1:
    schema-pattern: "*"
    events: ["truncate table", "drop table","drop database"]
    action: Ignore
  mysql-tidb-replica-03.filters.2:
    schema-pattern: "*"
    sql-pattern: ["^DROP\\s+PROCEDURE", "^CREATE\\s+PROCEDURE","ALTER\\s+TABLE[\\s\\S]*ADD\\s+PARTITION", "ALTER\\s+TABLE[\\s\\S]*DROP\\s+PARTITION","ALTER\\s+TABLE[\\s\\S]*ADD\\s+PRIMARY"]
    action: Ignore

这样写,报 * 冲突

  mysql-tidb-replica-03.filters.1:
    schema-pattern: "*"
    events: ["truncate table", "drop table","drop database"]
    sql-pattern: ["^DROP\\s+PROCEDURE", "^CREATE\\s+PROCEDURE","ALTER\\s+TABLE[\\s\\S]*ADD\\s+PARTITION", "ALTER\\s+TABLE[\\s\\S]*DROP\\s+PARTITION","ALTER\\s+TABLE[\\s\\S]*ADD\\s+PRIMARY"]
    action: Ignore

这样写,不跳过 sql="-- ----------------------------\r\n-- Procedure structure for auto_create_schedule\r\n-- ----------------------------\r\nDROP PROCEDURE IF EXISTS auto_create_schedule"

但是这个问题挺频发的,想着跟event-filter 的ignore一样,直接跳过这种的,否则一天得处理好几次

worker的报错日志 sql="-- ----------------------------\r\n-- Procedure structure for auto_create_schedule\r\n-- ----------------------------\r\nDROP PROCEDURE IF EXISTS auto_create_schedule"

filters:
  rule-1:
    schema-pattern: "*"
    ​events: ["truncate table", "drop table"]
    sql-pattern: ["^-.*DROP\\s+PROCEDURE", "^CREATE\\s+PROCEDURE"]
    ​action: Ignore

updata-task /path/to/task.yaml
resume-task task-name
query-status task-name
sql-skip --worker=127.0.0.1:8262 --sql-pattern=~^-.*DROP\s+PROCEDURE  task-name
resume-task task-name
query-status task-name

两种方案都不行,不知道这个正则到底该咋写了。

尝试把.* 等信息去掉,使用 DROP\s+PROCEDURE

试过,我试了十好几种的正则了,各种改,白搭

^DROP\\s+PROCEDURE
DROP
DROP\\s+PROCEDURE
*DROP*

啥的都试了

:smiley: 有任何进展么?

这个最开始的问题是想跳过一个 DDL 操作吗?DM 里面的有个 relay.meta 文件里有记录 binlog 的位点信息,可以作为跳过参考。

这是dmctl里的query-status

query-error里没错误

image

在worker的relay.meta是

image

sql-skip --worker=127.0.0.1:8262 --binlog-pos=mysql-bin|000004.577008213 task-name

又从日志里扒拉可能有的蛛丝马迹

sql-skip --worker=127.0.0.1:8262 --binlog-pos=mysql-bin|000001.000003:203585670 task-name

query-status 还是一毛一样的错误

我就是想跳过这个错误,试过了,sql-skip,各种方式找binlog pos,也试过了pattern,也不行 也试过了改task-yaml,然后update,配置ignore filter,各种drop的规则也不行

找到 position 之后,停掉任务,修改下 meta 里面的 position 信息,然后启动下任务。

请问一下,我怎么从当前有问题的binlog直接跳过到现在当前的binlog呢?

上一条解决方案里面有解答,参考操作下就可以。

我按照上一条操作了,现在query-status是跳过了。还是有错误。我query-error的时候看着还是之前的binlog有问题。

下面是我用query-error看的错误

我query-error的时候binlog还是在之前的错误,显然从query-error task-name 的时候我并没有跳过这个binlog的错误。但是在query-status task-name看的时候是跳过了。我是按照您上面说的那种方式,我先stop-task task-name停掉任务,然后stop停掉 Worker,之后修改meta中的pso点来跳过的binlog,然后重新start 开启 Worker,之后重新去启动 start-task task.yaml文件,启动start的时候是没报错的。这是我刚刚做的流程。现在就还是上面这个问题。

上面不兼容的 sql 语句里面包含换行符,按照正则表达的是规则 .* 是无法匹配上 \r 和 \n 的,所 以"^-.*DROP\s+PROCEDURE" 这种写法是对不,^-(.|\r|\n)*DROP\s+PROCEDURE" 是正确的写法

但是下面的 DROP DROP\s+PROCEDURE 两个规则不能生效很奇怪,我尝试了一下都可以生效,你这边有相关的 log 吗,我帮你看一下