DM 5.5 同步上游 mysql 8.0 数据 报错 Unknown collation: 'utf8mb4_0900_as_cs'

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

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 TiDB 使用环境】

DM version ：v5.5.0-nightly-20220107
上游mysql: 版本 mysql 8.0.20

【概述】DM 同步文件check 检测

INFRA [mysql@wqdcsrv3065 ~]# tiup dmctl --master-addr=10.67.200.37:8261 check-task dm-task.yaml
tiup is checking updates for component dmctl …
Starting component dmctl: /home/mysql/.tiup/components/dmctl/v5.4.0/dmctl/dmctl /home/mysql/.tiup/components/dmctl/v5.4.0/dmctl/dmctl --master-addr=10.67.200.37:8261 check-task dm-task.yaml
{
“result”: false,
“msg”: “[code=26005:class=dm-master:scope=internal:level=medium], Message: fail to check synchronization configuration with type: check was failed, please see detail
detail: {
“results”: [
{
“id”: 17,
“name”: “sharding table dw.user consistency checking”,
“desc”: “check consistency of sharding table structures”,
“state”: “fail”,
“errors”: [
{
“severity”: “fail”,
“short_error”: “[ddl:1273]Unknown collation: ‘utf8mb4_0900_as_cs’”
}
],
“extra”: “instance mysql-replica-49-3721 on sharding dw.user”
},
{
“id”: 16,
“name”: “sharding table percona_schema.xtrabackup_history consistency checking”,
“desc”: “check consistency of sharding table structures”,
“state”: “fail”,
“errors”: [
{
“severity”: “fail”,
“short_error”: “[ddl:1273]Unknown collation: ‘utf8mb4_0900_as_cs’”
}
],
“extra”: “instance mysql-replica-50-3021 on sharding percona_schema.xtrabackup_history”
},
{
“id”: 15,
“name”: “table structure compatibility check”,
“desc”: “check compatibility of table structure”,
“state”: “fail”,
“errors”: [
{
“severity”: “fail”,
“short_error”: “table testdb.t1 [ddl:1273]Unknown collation: ‘utf8mb4_0900_as_cs’”
},
{
“severity”: “fail”,
“short_error”: “table testdb.t2 [ddl:1273]Unknown collation: ‘utf8mb4_0900_as_cs’”
},
{
“severity”: “fail”,
“short_error”: “table percona_schema.xtrabackup_history [ddl:1273]Unknown collation: ‘utf8mb4_0900_as_cs’”
},
{
“severity”: “fail”,
“short_error”: “table ssdb.t_user_01 [ddl:1273]Unknown collation: ‘utf8mb4_0900_as_cs’”
},
{
“severity”: “fail”,
“short_error”: “table ssdb.user [ddl:1273]Unknown collation: ‘utf8mb4_0900_as_cs’”
}
],
“extra”: “address of db instance - 10.67.38.50:3021”
},
{
“id”: 7,
“name”: “table structure compatibility check”,
“desc”: “check compatibility of table structure”,
“state”: “fail”,
“errors”: [
{
“severity”: “fail”,
“short_error”: “table ssdb.user [ddl:1273]Unknown collation: ‘utf8mb4_0900_as_cs’”
},
{
“severity”: “fail”,
“short_error”: “table testdb.users [ddl:1273]Unknown collation: ‘utf8mb4_0900_as_cs’”
},
{
“severity”: “fail”,
“short_error”: “table db_dc_test.test1 [ddl:1273]Unknown collation: ‘utf8mb4_0900_as_cs’”
}
],
“extra”: “address of db instance - 10.67.39.49:3721”
}
],
“summary”: {
“passed”: false,
“total”: 18,
“successful”: 14,
“failed”: 4,
“warning”: 0
}
}”
}

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

2 个赞
2

tidb目前不支持这种排序规则
https://docs.pingcap.com/zh/tidb/stable/character-set-and-collation#字符集和排序规则

1 个赞
3

报错信息很明确了，不支持‘utf8mb4_0900_as_cs’字符集。

参考TiDB支持的字符集和排序规则：
https://docs.pingcap.com/zh/tidb/dev/character-set-and-collation#支持的字符集和排序规则

1 个赞
4

上游的交易库 都是这个字符集， 没有办法改交易库的字符集 ， 有办法跳过去吗？

5

已经解决了。

手动在目标端创建DDL的建表语句即可。

{
“result”: true,
“msg”: “check pass!!!”
}

6

:+1:手动创建下表跳过去是可以的

1 个赞
7

第一个fail细节说明检查共享表结构的down了

2 个赞
8

这种问题，估计会成官方解决的

9

联系官方的支持人员给看一下

2 个赞
10

上面说是已经解决了

2 个赞
11

有同一问题哈，这种涉及表数多了咋整？都统一先建表，还是统一库级别先转换？

1 个赞
12

还是需要能自动转换好些

13

能自动转换当然更好

2 个赞
14

自动转换需要做配置吗？怎么配置？

15

DM 任务完整配置文件，可以通过设置这个 collation_compatible: "strict" 配置项同步上下游 charset、collation 信息，设置 strict 模式后会按照上游 MySQL 默认 collation 进行补充，不过如果补充的 collation TiDB 不支持，还是会报错。
`

1 个赞
16

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