用syncer报DDL语句不兼容的错误

  • 【TiDB 版本】:TiDB-v3.0.7
  • 【问题描述】:

我用syncer,实现mysql到tidb集群的增量同步。今天在syncer里面报了个错误,说是DDL语句不兼容,可是我单独在tidb命令行里面执行这个ddl语句是可以执行的。 我想问,为什么syncer说不兼容,不让执行,而直接在tidb命令行里面能执行呢。

错误日志如下:

[root@pd-tidbserver syncer]# cat syncer.log

encountered incompatible DDL in TiDB: create table fine_user (id varchar(255) not null, birthday datetime, creationType integer not null, description varchar(1000), email varchar(255), enable bit, language varchar(255), lastOperationType integer not null, male bit, mobile varchar(255), password varchar(255) not null, realName varchar(255), userName varchar(255) not null, workPhone varchar(255), primary key (id)) please confirm your DDL statement is correct and needed. for TiDB compatible DDL, please see the docs: English version: https://github.com/pingcap/docs/blob/master/sql/ddl.md Chinese version: https://github.com/pingcap/docs-cn/blob/master/sql/ddl.md if the DDL is not needed, you can modify the meta file and restart syncer to skip it.

2019/12/19 11:09:08 syncer.go:748: [error] [error query event][schema]bi [error type]incompatible DDL [sql]create table fine_user (id varchar(255) not null, birthday datetime, creationType integer not null, description varchar(1000), email varchar(255), enable bit, language varchar(255), lastOperationType integer not null, male bit, mobile varchar(255), password varchar(255) not null, realName varchar(255), userName varchar(255) not null, workPhone varchar(255), primary key (id)) [error message]line 1 column 167 near “language varchar(255), lastOperationType integer not null, male bit, mobile varchar(255), password varchar(255) not null, realName varchar(255), userName varchar(255) not null, workPhone varchar(255), primary key (id))” [current pos](binglog.000017, 764132432) [next pos](binglog.000017, 764133550) [current gtid set] [next gtid set] 2019/12/19 11:09:08 syncer.go:1018: [info] flush all jobs meta = syncer-binlog = (binglog.000017, 764132432), syncer-binlog-gtid = 2019/12/19 11:09:08 meta.go:135: [info] save position to file, binlog-name:binglog.000017 binlog-pos:764132432 binlog-gtid: 2019/12/19 11:09:08 main.go:79: [error] /home/jenkins/agent/workspace/build_tidb_enterprise_tools_master/go/src/github.com/pingcap/tidb-enterprise-tools/syncer/ddl.go:74: [error type]incompatible DDL [sql]create table fine_user (id varchar(255) not null, birthday datetime, creationType integer not null, description varchar(1000), email varchar(255), enable bit, language varchar(255), lastOperationType integer not null, male bit, mobile varchar(255), password varchar(255) not null, realName varchar(255), userName varchar(255) not null, workPhone varchar(255), primary key (id)) [error message]line 1 column 167 near “language varchar(255), lastOperationType integer not null, male bit, mobile varchar(255), password varchar(255) not null, realName varchar(255), userName varchar(255) not null, workPhone varchar(255), primary key (id))” /home/jenkins/agent/workspace/build_tidb_enterprise_tools_master/go/src/github.com/pingcap/tidb-enterprise-tools/syncer/syncer.go:749: /home/jenkins/agent/workspace/build_tidb_enterprise_tools_master/go/src/github.com/pingcap/tidb-enterprise-tools/syncer/syncer.go:193:
2019/12/19 11:09:08 syncer.go:928: [info] print status exits, err:context canceled

我在tidb里面,是能执行这个语句的:

image

  1. 麻烦提供下 syncer 的版本以及配置
  2. 建议使用 DM 进行同步。

好的,以后我用DM同步,我看官网文档里面迁移部分介绍mysql增量同步用的syncer,所以就用了。第一次使用tidb,看一个资料上介绍的,才了解到该数据库。

[tidb@pd-tidbserver syncer]$ ./syncer -V

[2019/12/19 13:44:45.417 +08:00] [WARN] [disk.go:52] [“Remove temporary file error”] [tmpDir=/tmp/tidb-server-syncer] [error=“unlinkat /tmp/tidb-server-syncer: operation not permitted”] [2019/12/19 13:44:45.417 +08:00] [WARN] [disk.go:56] [“Mkdir temporary file error”] [tmpDir=/tmp/tidb-server-syncer] [error=“mkdir /tmp/tidb-server-syncer: file exists”] Release Version: v1.0.0-77-g68ca5fc Git Commit Hash: 68ca5fcc676b07acb79a0f93ab0363e3e64d4254 Git Branch: master UTC Build Time: 2019-12-04 06:54:29 Go Version: go version go1.13 linux/amd64

[tidb@pd-tidbserver syncer]$ cat syncer.meta

binlog-name = “binglog.000018” binlog-pos = 622523418 binlog-gtid = “”

[tidb@pd-tidbserver syncer]$ cat config.toml |grep -v ^# |grep -v ^$

log-level = “info” log-file = “syncer.log” log-rotate = “day” server-id = 1344 meta = “./syncer.meta” worker-count = 16 batch = 100 flavor = “mysql” status-addr = “:8271” stop-on-ddl = false max-retry = 1000 [from] host = “172.16.2.131” user = “tidb_syncer” password = “1q2w3e4r” port = 3306 [to] host = “127.0.0.1” user = “tidb_syncer” password = “1q2w3e4r” port = 4000

这边在 3.0.3 tidb + syncer 的最新版本测试,在上游 mysql 5.7 建表没有遇到问题。可以尝试:

  1. 替换 syncer 为最新版本
  2. syncer 修改 log-level : "debug" 看下 debug 的信息
  3. 替换为 DM 进行同步尝试。