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

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

我知道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:
	please confirm your DDL statement is correct and needed.
	for TiDB compatible DDL, please see the docs:
	  English version: https://pingcap.com/docs/dev/reference/mysql-compatibility/#ddl
	  Chinese version: https://pingcap.com/docs-cn/dev/reference/mysql-compatibility/#ddl
	if the DDLis not needed, you can use a filter rule with "*" schema-pattern to ignore it.
	 : parse statement: line 4 column 15 near "PROCEDURE IF EXISTS `auto_create_schedule`" %!(EXTRA string=-- ----------------------------\r
-- Procedure structure for auto_create_schedule\r
-- ----------------------------\r
DROP PROCEDURE IF EXISTS `auto_create_schedule`)
github.com/pingcap/dm/pkg/terror.(*Error).Delegate
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:267
github.com/pingcap/dm/pkg/parser.Parse
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/parser/common.go:42
github.com/pingcap/dm/syncer.(*Syncer).parseDDLSQL
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/ddl.go:67
github.com/pingcap/dm/syncer.(*Syncer).handleQueryEvent
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1483
github.com/pingcap/dm/syncer.(*Syncer).Run
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1230
github.com/pingcap/dm/syncer.(*Syncer).Process
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:539
github.com/pingcap/dm/syncer.(*Syncer).Resume
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:2188
runtime.goexit
	/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
-- Procedure structure for auto_create_schedule\r
-- ----------------------------\r
DROP 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:
	please confirm your DDL statement is correct and needed.
	for TiDB compatible DDL, please see the docs:
	  English version: https://pingcap.com/docs/dev/reference/mysql-compatibility/#ddl
	  Chinese version: https://pingcap.com/docs-cn/dev/reference/mysql-compatibility/#ddl
	if the DDL is not needed, you can use a filter rulewith "*" schema-pattern to ignore it.
	 "] [task=pre] [unit="binlog replication"] [sql="-- ----------------------------\r
-- Procedure structure for auto_create_schedule\r
-- ----------------------------\r
DROP 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
-- Procedure structure for auto_create_schedule\r
-- ----------------------------\r
DROP 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:
	please confirm your DDL statement is correct and needed.
	for TiDB compatible DDL, please see the docs:
	  English version: https://pingcap.com/docs/dev/reference/mysql-compatibility/#ddl
	  Chinese version: https://pingcap.com/docs-cn/dev/reference/mysql-compatibility/#ddl
	if the DDL is not needed, you can use a filter rule with "*" schema-pattern to ignore it.
	 : parse statement: line 4 column 15 near "PROCEDURE IF EXISTS `auto_create_schedule`" %!(EXTRA string=-- ----------------------------\r
-- Procedure structure for auto_create_schedule\r
-- ----------------------------\r
DROP PROCEDURE IF EXISTS `auto_create_schedule`)"]

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

2

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

3

这个我用过，但是需要找到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 -- Procedure structure for auto_create_schedule\r -- ----------------------------\r DROP PROCEDURE IF EXISTS auto_create_schedule"

4

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

5

worker的报错日志 sql="-- ----------------------------\r -- Procedure structure for auto_create_schedule\r -- ----------------------------\r DROP 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=~^-.*DROPs+PROCEDURE  task-name
resume-task task-name
query-status task-name

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

6

尝试把.* 等信息去掉，使用 DROPs+PROCEDURE

7

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

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

啥的都试了

8

:smiley: 有任何进展么？

9

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

10

这是dmctl里的query-status

image
image1187×795 24.1 KB

query-error里没错误

image

在worker的relay.meta是

image

而

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

image
image1406×22 2.41 KB

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

image
image1556×268 24.1 KB

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

image
image798×502 6.83 KB

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

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

11

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

12

%E5%9B%BE%E7%89%87
%E5%9B%BE%E7%89%871901×775 27.4 KB

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

13

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

14

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

%E5%9B%BE%E7%89%87
%E5%9B%BE%E7%89%871896×775 28 KB

下面是我用query-error看的错误
%E5%9B%BE%E7%89%87
%E5%9B%BE%E7%89%871905×906 82.1 KB

15

我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的时候是没报错的。这是我刚刚做的流程。现在就还是上面这个问题。

16

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

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

17

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