sync_diff_inspector对比报错，提示 Illegal mix of collations，但上下游collation是一样的

dba-kit · 2022 年11 月 23 日 07:17

版本为6.1.2，是通过DM同步到下游TiDB，在对比时候报错，手动核对上下游的表结构，两者是一致的，都是DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci。
错误日志如下所示，有几点比较奇怪：

提示Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation ‘>’
主键都是标准的字母和数字，但是程序找到的边界值都是乱码字符

[2022/11/23 14:27:58.480 +08:00] [WARN] [utils.go:764] ["execute checksum query fail"] [query="SELECT COUNT(*) as CNT, BIT_XOR(CAST(CRC32(CONCAT_WS(',', `broker`, `account3_id`, `payment_method_id`, `create_time`, `update_time`, CONCAT(ISNULL(`broker`), ISNULL(`account3_id`), ISNULL(`payment_method_id`), ISNULL(`create_time`), ISNULL(`update_time`))))AS UNSIGNED)) as CHECKSUM FROM `db`.`trade_bank_card` WHERE (((`broker` < ?) OR (`broker` = ? AND `account3_id` < ?) OR (`broker` = ? AND `account3_id` = ? AND `payment_method_id` <= ?)) AND (TRUE));"] [args="[\"\\u000e)\\u000e)\\u000e)\\u000e1\",\"\\u000e)\\u000e)\\u000e)\\u000e1\",\"\\u000e*\\u000e,\\u000e,\\u000e1\\u000e,\\u000e.\\u000e1\",\"\\u000e)\\u000e)\\u000e)\\u000e1\",\"\\u000e*\\u000e,\\u000e,\\u000e1\\u000e,\\u000e.\\u000e1\",\"\\u000e3\\u000f.\\u000f\\u0010\\u000e+\\u000f.\\u000em\\u000em\\u000e)\\u000e\\ufffd\\u000e\\ufffd\\u000e0\\u0010^\"]"] [error="Error 1267: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '<='"]
[2022/11/23 14:27:58.480 +08:00] [WARN] [utils.go:764] ["execute checksum query fail"] [query="SELECT COUNT(*) as CNT, BIT_XOR(CAST(CRC32(CONCAT_WS(',', `broker`, `account3_id`, `payment_method_id`, `create_time`, `update_time`, CONCAT(ISNULL(`broker`), ISNULL(`account3_id`), ISNULL(`payment_method_id`), ISNULL(`create_time`), ISNULL(`update_time`))))AS UNSIGNED)) as CHECKSUM FROM `db`.`trade_bank_card` WHERE (((`broker` = ?) AND ((`account3_id` > ?) OR (`account3_id` = ? AND `payment_method_id` > ?)) AND ((`account3_id` < ?) OR (`account3_id` = ? AND `payment_method_id` <= ?))) AND (TRUE));"] [args="[\"\\u000e)\\u000e)\\u000e)\\u000e1\",\"\\u000e+\\u000e+\\u000e,\\u000e1\\u000e1\\u000e+\\u000e0\",\"\\u000e+\\u000e+\\u000e,\\u000e1\\u000e1\\u000e+\\u000e0\",\"\\u000e`\\u000e0\\u000e,\\u000f\\ufffd\\u0010Q\\u0010Z\\u000e,\\u0010\\u0002\\u000e2\\u000e/\\u0010j\\u000f\\ufffd\",\"\\u000e+\\u000e-\\u000e1\\u000e)\\u000e-\\u000e*\\u000e-\",\"\\u000e+\\u000e-\\u000e1\\u000e)\\u000e-\\u000e*\\u000e-\",\"\\u000e`\\u0010\\u001f\\u000e/\\u000f\\ufffd\\u000e\\ufffd\\u000em\\u0010D\\u000e,\\u0010D\\u000f[\\u0010\\u0002\\u000e\\ufffd\"]"] [error="Error 1267: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '>'"]
[2022/11/23 14:27:58.480 +08:00] [WARN] [utils.go:764] ["execute checksum query fail"] [query="SELECT COUNT(*) as CNT, BIT_XOR(CAST(CRC32(CONCAT_WS(',', `broker`, `account3_id`, `payment_method_id`, `create_time`, `update_time`, CONCAT(ISNULL(`broker`), ISNULL(`account3_id`), ISNULL(`payment_method_id`), ISNULL(`create_time`), ISNULL(`update_time`))))AS UNSIGNED)) as CHECKSUM FROM `db`.`trade_bank_card` WHERE (((`broker` = ?) AND ((`account3_id` > ?) OR (`account3_id` = ? AND `payment_method_id` > ?)) AND ((`account3_id` < ?) OR (`account3_id` = ? AND `payment_method_id` <= ?))) AND (TRUE));"] [args="[\"\\u000e)\\u000e)\\u000e)\\u000e1\",\"\\u000e*\\u000e,\\u000e,\\u000e1\\u000e,\\u000e.\\u000e1\",\"\\u000e*\\u000e,\\u000e,\\u000e1\\u000e,\\u000e.\\u000e1\",\"\\u000e3\\u000f.\\u000f\\u0010\\u000e+\\u000f.\\u000em\\u000em\\u000e)\\u000e\\ufffd\\u000e\\ufffd\\u000e0\\u0010^\",\"\\u000e*\\u000e.\\u000e1\\u000e0\\u000e+\\u000e0\\u000e1\",\"\\u000e*\\u000e.\\u000e1\\u000e0\\u000e+\\u000e0\\u000e1\",\"\\u000eJ\\u000e-\\u000e.\\u000e\\ufffd\\u000f.\\u000e3\\u000e\\ufffd\\u000e1\\u000e3\\u000e-\\u0010\\u0002\\u000e-\"]"] [error="Error 1267: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '>'"]
[2022/11/23 14:27:58.488 +08:00] [WARN] [utils.go:764] ["execute checksum query fail"] [query="SELECT COUNT(*) as CNT, BIT_XOR(CAST(CRC32(CONCAT_WS(',', `broker`, `account3_id`, `payment_method_id`, `create_time`, `update_time`, CONCAT(ISNULL(`broker`), ISNULL(`account3_id`), ISNULL(`payment_method_id`), ISNULL(`create_time`), ISNULL(`update_time`))))AS UNSIGNED)) as CHECKSUM FROM `db`.`trade_bank_card` WHERE (((`broker` = ?) AND ((`account3_id` > ?) OR (`account3_id` = ? AND `payment_method_id` > ?)) AND ((`account3_id` < ?) OR (`account3_id` = ? AND `payment_method_id` <= ?))) AND (TRUE));"] [args="[\"\\u000e)\\u000e)\\u000e)\\u000e1\",\"\\u000e+\\u000e+\\u000e,\\u000e1\\u000e1\\u000e+\\u000e0\",\"\\u000e+\\u000e+\\u000e,\\u000e1\\u000e1\\u000e+\\u000e0\",\"\\u000e`\\u000e0\\u000e,\\u000f\\ufffd\\u0010Q\\u0010Z\\u000e,\\u0010\\u0002\\u000e2\\u000e/\\u0010j\\u000f\\ufffd\",\"\\u000e+\\u000e-\\u000e1\\u000e)\\u000e-\\u000e*\\u000e-\",\"\\u000e+\\u000e-\\u000e1\\u000e)\\u000e-\\u000e*\\u000e-\",\"\\u000e`\\u0010\\u001f\\u000e/\\u000f\\ufffd\\u000e\\ufffd\\u000em\\u0010D\\u000e,\\u0010D\\u000f[\\u0010\\u0002\\u000e\\ufffd\"]"] [error="Error 1105: runtime error: index out of range [24] with length 24"]

WalterWj · 2022 年11 月 23 日 07:53

admin check 下试试

海石花47 · 2022 年11 月 23 日 08:02

表结构贴一下? 有没有忽略 text、blob字段之类的？

dba-kit · 2022 年11 月 23 日 08:48

表结构很简单，只有三个varchar字段，两个datetime字段。两边都比对过，都是

dba-kit · 2022 年11 月 23 日 08:49

这个也检查了下，没有报错信息，我手动把表dump出来也没报错。

dba-kit · 2022 年11 月 23 日 08:49

难道是跟6.1默认启动了new_collations_enabled_on_first_bootstrap有关系？

海石花47 · 2022 年11 月 23 日 08:57

show table status 看看两边的collection 都是一样的么？都是 utf8_unicode_ci？

dba-kit · 2022 年11 月 23 日 09:03

两边表结构是一样的，collcate也是一样

Leavrth · 2022 年11 月 23 日 09:37

在 sync-diff-inspector 配置文件中指定的下游数据库中执行这个 SQL，看看有没有异常
SHOW STATS_BUCKETS WHERE db_name= ‘…’ AND table_name= ‘…’;

dba-kit · 2022 年11 月 23 日 09:41

确实比较诡异，是乱码的分割

dba-kit · 2022 年11 月 23 日 09:43

请问，这个查询的是哪个系统表啊？难道是系统表的字符集问题？

Leavrth · 2022 年11 月 23 日 09:50

在 mysql.stats_buckets 中

dba-kit · 2022 年11 月 23 日 10:00

看了下这个表的字符集是utf8mb4_bin，那其他字符集的buckets的边界写到里边字符集就变成了utf8mb4_bin了么？

dba-kit · 2022 年11 月 23 日 10:11

用同样的表结构新建了一个utf8mb4的表，并把数据导入后，发现这个表的值正常了，里边是正常的数字了

Leavrth · 2022 年11 月 23 日 10:15

嗯，后面版本会尝试修复这个问题

dba-kit · 2022 年11 月 23 日 10:16

额，现在有办法不让它读这个表么？我记得如果两边都是MySQL的话，会根据随机数来计算出来chunk的边界。

Leavrth · 2022 年11 月 23 日 10:18

是的，现在的 workaround 就是在 sync-diff 配置中强制让这个表走一个不存在的索引，例如

######################### Task config #########################
[task]
        output-dir = "./output"

        source-instances = ["mysql1"]

        target-instance = "tidb0"

        target-check-tables = ["schema*.table*", "!c.*", "test2.t2"]
        target-configs = ["config1"]

######################### Table config #########################
[table-configs.config1]
target-tables = ["schema*.test*", "test2.t2"]  #  填写出问题的表
index-fields = ["broker"]

dba-kit · 2022 年11 月 23 日 10:33

额，按照说的设置成index-fields = [“idx_not_exist”]，报列不存在。难道是要对上百个表分别设置index_fields么？

dba-kit · 2022 年11 月 23 日 10:52

["failed to build bucket iterator, fall back to use random iterator"] [error="no index to split buckets not found"]
不过按照这个方式，对单表来核对，确实是可以的。有没有简单的办法，针对所有表来设置啊。。

Leavrth · 2022 年11 月 23 日 11:05

看了一下，是有的哈，把 index-fields 换成 range 就行了

######################### Task config #########################
[task]
        output-dir = "./output"

        source-instances = ["mysql1"]

        target-instance = "tidb0"

        target-check-tables = ["schema*.table*", "!c.*", "test2.t2"]
        target-configs = ["config1"]

######################### Table config #########################
[table-configs.config1]
target-tables = ["schema*.test*", "test2.t2"]  #  填写出问题的表
range = "true and true"