DM同步上游mysql的改字段名及改默认值的ddl报错

LI-ldc · 2021 年6 月 18 日 07:58

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：

【概述】场景 + 问题概述
DM同步上游mysql的改字段名及默认值的ddl报错

【备份和数据迁移策略逻辑】
lightning全量，dm做增量

【现象】业务和数据库现象

【问题】当前遇到的问题
“errors”: [
{
“ErrCode”: 36062,
“ErrClass”: “sync-unit”,
“ErrScope”: “internal”,
“ErrLevel”: “high”,
“Message”: “startLocation: [position: (mysql-bin.000144, 670771848), gtid-set: ], endLocation: [position: (mysql-bin.000144, 670772125), gtid-set: ], origin SQL: [ALTER TABLE ts_coupon_group_multi\r\ CHANGE COLUMN dis_tag dis_tags varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘折扣标签’ AFTER end_time]: fail to handle shard ddl [ALTER TABLE db_ysbg_user.ts_coupon_group_multi CHANGE COLUMN dis_tag dis_tags VARCHAR(20) CHARACTER SET UTF8 COLLATE utf8_general_ci NOT NULL DEFAULT _UTF8MB4’’ COMMENT ‘折扣标签’ AFTER end_time] in optimistic mode, because schema conflict detected, conflict error: [code=11111:class=functional:scope=internal:level=medium], Message: fail to try sync the optimistic shard ddl lock task6-db_ysbg_user.ts_coupon_group_multi: there will be conflicts if DDLs [ALTER TABLE db_ysbg_user.ts_coupon_group_multi CHANGE COLUMNdis_tag dis_tags VARCHAR(20) CHARACTER SET UTF8 COLLATE utf8_general_ci NOT NULL DEFAULT _UTF8MB4’’ COMMENT ‘折扣标签’ AFTER end_time] are applied to the downstream. old table info: CREATE TABLE tbl(begin_time INT(11) NOT NULL DEFAULT 0, coupontype_gp_id INT(11) NOT NULL, dis_tag VARCHAR(20) CHARACTER SET UTF8 COLLATE utf8_bin NOT NULL DEFAULT , end_time INT(11) NOT NULL DEFAULT 0, id INT(11) NOT NULL AUTO_INCREMENT, lowest_dis DECIMAL(4,3) NOT NULL DEFAULT 1.000, lowest_tag VARCHAR(30) CHARACTER SET UTF8 COLLATE utf8_bin NOT NULL DEFAULT , PRIMARY KEY (id)) CHARSET UTF8 COLLATE UTF8_BIN, new table info: CREATE TABLE tbl(begin_time INT(11) NOT NULL DEFAULT 0, coupontype_gp_id INT(11) NOT NULL, dis_tags VARCHAR(20) CHARACTER SET UTF8 COLLATE utf8_general_ci NOT NULL DEFAULT , end_time INT(11) NOT NULL DEFAULT 0, id INT(11) NOT NULL AUTO_INCREMENT, lowest_dis DECIMAL(4,3) NOT NULL DEFAULT 1.000, lowest_tag VARCHAR(30) CHARACTER SET UTF8 COLLATE utf8_bin NOT NULL DEFAULT , PRIMARY KEY (id)) CHARSET UTF8 COLLATE UTF8_BIN, RawCause: at tuple index 2: at map key “dis_tags”: combining contradicting orders (1 \u0026\u0026 -1), Workaround: Please use show-ddl-locks command for more details.”,
“RawCause”: “”,
“Workaround”: “Please use show-ddl-locks command for more details.”
}
]

							{
                            "ErrCode": 36062,
                            "ErrClass": "sync-unit",
                            "ErrScope": "internal",
                            "ErrLevel": "high",
                            "Message": "startLocation: [position: (mysql-bin.000144, 669971834), gtid-set: ], endLocation: [position: (mysql-bin.000144, 669972108), gtid-set: ], origin SQL: [ALTER TABLE `ts_coupon_group_multi`\r\
MODIFY COLUMN `lowest_dis`  decimal(4,3) NOT NULL DEFAULT 2 COMMENT '列表折后约xx价格对应的折扣 0.97表示九七折' AFTER `lowest_tag`]: fail to handle shard ddl [ALTER TABLE `db_ysbg_user`.`ts_coupon_group_multi` MODIFY COLUMN `lowest_dis` DECIMAL(4,3) NOT NULL DEFAULT 2 COMMENT '列表折后约xx价格对应的折扣 0.97表示九七折' AFTER `lowest_tag`] in optimistic mode, because schema conflict detected, conflict error: [code=11111:class=functional:scope=internal:level=medium], Message: fail to try sync the optimistic shard ddl lock task6-`db_ysbg_user`.`ts_coupon_group_multi`: there will be conflicts if DDLs [ALTER TABLE `db_ysbg_user`.`ts_coupon_group_multi` MODIFY COLUMN `lowest_dis` DECIMAL(4,3) NOT NULL DEFAULT 2 COMMENT '列表折后约xx价格对应的折扣 0.97表示九七折' AFTER `lowest_tag`] are applied to the downstream. old table info: CREATE TABLE `tbl`(`begin_time` INT(11) NOT NULL DEFAULT 0, `coupontype_gp_id` INT(11) NOT NULL, `dis_tag` VARCHAR(20) CHARACTER SET UTF8 COLLATE utf8_bin NOT NULL DEFAULT , `end_time` INT(11) NOT NULL DEFAULT 0, `id` INT(11) NOT NULL AUTO_INCREMENT, `lowest_dis` DECIMAL(4,3) NOT NULL DEFAULT 1.000, `lowest_tag` VARCHAR(30) CHARACTER SET UTF8 COLLATE utf8_bin NOT NULL DEFAULT , PRIMARY KEY (`id`)) CHARSET UTF8 COLLATE UTF8_BIN, new table info: CREATE TABLE `tbl`(`begin_time` INT(11) NOT NULL DEFAULT 0, `coupontype_gp_id` INT(11) NOT NULL, `dis_tag` VARCHAR(20) CHARACTER SET UTF8 COLLATE utf8_bin NOT NULL DEFAULT , `end_time` INT(11) NOT NULL DEFAULT 0, `id` INT(11) NOT NULL AUTO_INCREMENT, `lowest_dis` DECIMAL(4,3) NOT NULL DEFAULT 2, `lowest_tag` VARCHAR(30) CHARACTER SET UTF8 COLLATE utf8_bin NOT NULL DEFAULT , PRIMARY KEY (`id`)) CHARSET UTF8 COLLATE UTF8_BIN, RawCause: at tuple index 2: at map key \"lowest_dis\": at tuple index 0: distinct singletons (1.000 vs 2), Workaround: Please use `show-ddl-locks` command for more details.",
                            "RawCause": "",
                            "Workaround": "Please use show-ddl-locks command for more details."
                        }

【业务影响】

【TiDB 版本】
tidb：v5.0.2
dm: v2.0.3

yilong · 2021 年6 月 18 日 10:35

show-ddl-locks 看下
这个表是上游多个表吗？同步到下游tidb一个表中吗？
麻烦上传下task 配置文件

LI-ldc · 2021 年6 月 21 日 02:56

2、上游mysql只有一个表，同步到下游tidb的一个表
3、
name: task6
shard-mode: “optimistic”
task-mode: incremental
meta-schema: “dm_meta”
timezone: “Asia/Shanghai”
case-sensitive: false
online-ddl-scheme: “gh-ost”
ignore-checking-items: [“table_schema”]
clean-dump-file: true

target-database:
host: “192.168.0.34”
port: 4000
user: “root”
password: “W/pQpGRBp9804IdBL5RHckAFliSNPR4AHcgGPA==”

session:
tidb_skip_utf8_check: 1
tidb_constraint_check_in_place: 0

block-allow-list:
bw-rule-1:
do-dbs: [“db_diffd”, “db_export”, “db_gyb”, “db_fgedl”, “db_lfweo”, “db_mdp”, “db_pureftfpd”, “db_xxl_job”, “db_ysb*”]
ignore-dbs: [“test”]
ignore-tables:
- db-name: “db_fgedl”
tbl-name: “ts_difagasose_order”

mydumpers:
global:
threads: 64
chunk-filesize: 64
skip-tz-utc: true
extra-args: “–consistency none”

loaders:
global:
pool-size: 64
dir: “./dumped_data”

syncers:
global:
worker-count: 64
batch: 500
enable-ansi-quotes: true
safe-mode: true

mysql-instances:

source-id: "source1"
meta:
  binlog-name: mysql-bin.000144
  binlog-pos: 487396454
block-allow-list:  "bw-rule-1"

route-rules: [“route-rule-1”]

mydumper-config-name: "global"
loader-config-name: "global"
syncer-config-name: "global"

yilong · 2021 年6 月 21 日 13:09

shard-mode: “pessimistic” # 如果为分库分表合并任务则需要配置该项。默认使用悲观协调模式 “pessimistic”，在深入了解乐观协调模式的原理和使用限制后，也可以设置为乐观协调模式 “optimistic”
在和您确认下，上游只有一个mysql实例，并且只同步一个表？那么不需要配置这个参数，建议重新设置任务同步。如果上游是多个表同步到下游一个表，那么需要配置这个参数。
比如： test_1, test_2 表结构相同，都要同步到下游 test ,那么会需要配置这个参数，多谢。

system · 2022 年10 月 31 日 19:08

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