DDL语句的comment中文出现乱码

[dlnufox]

MySQL中，charset为 utf8mb4，collation为 utf8mb4_general_ci

测试过程中建表语句如下：

CREATE TABLE `xxx` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id，自增',
  `run_time` int(13) NOT NULL DEFAULT '0' COMMENT '运行总时长-秒',
  `init_time` int(13) NOT NULL DEFAULT '0' COMMENT '初始化时长-秒',
  `simulation_time` int(13) NOT NULL DEFAULT '0' COMMENT '模拟计算耗时-秒',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='hadoop处理能力标准监控程序运行时长';

Canal server接收后，得到的SQL语句如下：

create table `xxx` (
`id` int(11) NOT NULL auto_increment COMMENT 'šłĽťĒģidԾƍᙌĘě',
`run_time` int(13) NOT NULL DEFAULT 0 COMMENT 'ŤŅźŤ°ĆśÄĽśó∂ťēŅ-Áßí',
`init_time` int(13) NOT NULL DEFAULT 0 COMMENT 'ŚąĚŚßčŚĆĖśó∂ťēŅ-Áßí', 
`simulation_time` int(13) NOT NULL DEFAULT 0 COMMENT 'ś®°śčüŤģ°ÁģóŤÄóśó∂-Áßí', 
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'ŚąõŚĽļśó∂ťóī',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'śõīśĖįśó∂ťóī',
PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'hadoopŚ§ĄÁźÜŤÉĹŚäõś†áŚáÜÁõĎśéßÁ®čŚļŹŤŅźŤ°Ćśó∂ťēŅ';

问题可能的原因，在canal server接收到binlog日志时，会从binlog日志中得到 clientCharset = 45，从而得到 charsetName = MacCentralEurope，再解码得出上述的带中文乱码的SQL

canal/dbsync/src/main/java/com/taobao/tddl/dbsync/binlog/event/QueryLogEvent.java

Line 482 in f46133d

charsetName = CharsetConversion.getJavaCharset(clientCharset);

canal/dbsync/src/main/java/com/taobao/tddl/dbsync/binlog/CharsetConversion.java

Line 106 in f46133d

putEntry(45, "utf8mb4", "utf8mb4_general_ci", "MacCentralEurope");

        // 这两项定义是否正确？
        putEntry(45, "utf8mb4", "utf8mb4_general_ci", "MacCentralEurope");
        putEntry(46, "utf8mb4", "utf8mb4_bin", "MacCentralEurope");

[agapple]

你最好debug一下event收到的编码是啥

[dlnufox]

1、MySQL中，charset为 utf8mb4，collation为 utf8mb4_general_ci，在mysql官网对应的 ID= 45，在生产库中执行这个sql

SHOW COLLATION WHERE Charset = 'utf8mb4';

查出来也是一样的

2、并且在debug时，在下面代码执行过程，从binlog日志中得到 clientCharset = 45，从而得到 charsetName = MacCentralEurope

        /* A 2nd variable part; this is common to all versions */
        final int queryLen = dataLen - dbLen - 1;
        dbname = buffer.getFixString(dbLen + 1);
        if (clientCharset >= 0) {
            charsetName = CharsetConversion.getJavaCharset(clientCharset);

            if ((charsetName != null) && (Charset.isSupported(charsetName))) {
                query = buffer.getFixString(queryLen, charsetName);
            } else {
                logger.warn("unsupported character set in query log: " + "\n    ID = " + clientCharset + ", Charset = "
                            + CharsetConversion.getCharset(clientCharset) + ", Collation = "
                            + CharsetConversion.getCollation(clientCharset));

                query = buffer.getFixString(queryLen);
            }
        } else {
            query = buffer.getFixString(queryLen);
        }

debug截图

3、我尝试把

putEntry(45, "utf8mb4", "utf8mb4_general_ci", "MacCentralEurope");

改成了

putEntry(45, "utf8mb4", "utf8mb4_general_ci", "UTF-8");

然后，乱码就解决了

debug截图

4、在代码中，与 utf8mb4 对应的 javaCharset 只有 45，46 两项所为 MacCentralEurope，除此之外，其他都对应到了 UTF-8

canal/dbsync/src/main/java/com/taobao/tddl/dbsync/binlog/CharsetConversion.java

Line 106 in f46133d

putEntry(45, "utf8mb4", "utf8mb4_general_ci", "MacCentralEurope");

[agapple]

应该是当时的笔误，已修复