DM同步上游MySQL的时,遇到字段类型有损变更怎么办?

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

  • 【TiDB 版本】:DM1.0.4 TIDB4.0
  • 【问题描述】:同步时日志里发现有一错误,不支持的语法 [ERROR] [subtask.go:255] [“unit process error”] [subtask=test] [unit=Sync] [“error information”="{“Type”:1,“msg”:"[code=10006:class=database:scope=downstream:level=high] execute statement failed: ALTER TABLE evbase.erp_bill_index CHANGE COLUMN id id INT(10) UNSIGNED NOT NULL: Error 8200: Unsupported modify column: length 10 is less than origin 11\ngithub.com/pingcap/dm/pkg/terror.(*Error).Delegate\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:267\ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ExecuteSQLWithIgnoreError\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:183\ngithub.com/pingcap/dm/syncer.(*DBConn).executeSQLWithIgnore.func3\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/db.go:268\ngithub.com/pingcap/dm/pkg/retry.(*FiniteRetryStrategy).Apply\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/retry/strategy.go:71\ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ApplyRetryStrategy\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:204\ngithub.com/pingcap/dm/syncer.(*DBConn).executeSQLWithIgnore\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/db.go:263\ngithub.com/pingcap/dm/syncer.(*Syncer).syncDDL\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:896\ngithub.com/pingcap/dm/syncer.(*Syncer).Run.func3\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1103\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1357",“error”:{“ErrCode”:10006,“ErrClass”:1,“ErrScope”:2,“ErrLevel”:3,“Message”:“execute statement failed: ALTER TABLE evbase.erp_bill_index CHANGE COLUMN id id INT(10) UNSIGNED NOT NULL: Error 8200: Unsupported modify column: length 10 is less than origin 11”,“RawCause”:“Error 8200: Unsupported modify column: length 10 is less than origin 11”}}"] [2020/04/27 10:39:32.823 +08:00] [ERROR] [subtask.go:255] [“unit process error”] [subtask=test] [unit=Sync] [“error information”="{“msg”:"[code=36061:class=sync-unit:scope=internal:level=high] current pos (mysql-bin|000001.000946, 709394970): fail to handle ddl job for ALTER TABLE erp_bill_index CHANGE id id INT(10) UNSIGNED NOT NULL\ngithub.com/pingcap/dm/pkg/terror.(*Error).Generate\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:232\ngithub.com/pingcap/dm/syncer.(*Syncer).handleQueryEvent\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1737\ngithub.com/pingcap/dm/syncer.(*Syncer).Run\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1312\ngithub.com/pingcap/dm/syncer.(*Syncer).Process\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:599\ngithub.com/pingcap/dm/syncer.(*Syncer).Resume\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:2320\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1357",“error”:{“ErrCode”:36061,“ErrClass”:11,“ErrScope”:3,“ErrLevel”:3,“Message”:“current pos (mysql-bin|000001.000946, 709394970): fail to handle ddl job for ALTER TABLE erp_bill_index CHANGE id id INT(10) UNSIGNED NOT NULL”}}"]

SQL:ALTER TABLE erp_bill_index CHANGE id id INT(10) UNSIGNED NOT NULL ;

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。

你好

tidb 字段类型不支持有损变更,譬如从 int(11) change to in(10),上游如果出现类似语法,可以通过 sql-skip 一次性跳过,如果忽略类似的语法,请配置 黑白名单和binlog event 进行过滤

alter table partition也不支持呀

你好,

目测是的,在 tidb 中执行下,如果不成功就是不支持。

遇到不支持的语法可以在上面链接搜索下~