1、错误信息
软件:Navicat for MySQL (Version 9.0.15 - enterprise)
操作:Tools - "Data Synchronization..."(数据同步),使用Navicat Premium 进行数据同步没有问题
结论:Navicat for MySQL (Version 9.0.15 - enterprise) 这个版本同步数据存在bug,具体分析请详细阅读此文。
[Msg] [Sync] Synchronization Start...
[Msg] [Sync] Synchronizing: localhost_rdis.`atdps_a`.`a1` -> localhost_rdis.`atdps_a`.`prescription_medicine`
[Err] [Sync] Source server SQL execution error:1271 - Illegal mix of collations for operation 'concat_ws'
[Err] [Sync] 1271 - 1271 - Illegal mix of collations for operation 'concat_ws'
[Msg] [Sync] Synchronization Failed: MD5 signature preparation failure
[Msg] [Sync] Finished - Unsuccessful Synchronization
[Msg] [Sync] Source Server: ROLLBACK
[Msg] [Sync] Target Server: ROLLBACK
--------------------------------------------------
2、Mysql 记录日志如下
event_time | user_host | thread_id | server | command_type | argument |
2020年8月4日 14:34:55 | [rdis] @ localhost [127.0.0.1] | 110872 | 0 | Connect | rdis@localhost on |
2020年8月4日 14:34:55 | rdis[rdis] @ localhost [127.0.0.1] | 110872 | 0 | Query | SET NAMES utf8 |
2020年8月4日 14:34:55 | rdis[rdis] @ localhost [127.0.0.1] | 110872 | 0 | Query | SHOW VARIABLES LIKE 'lower_case_%' |
2020年8月4日 14:34:55 | rdis[rdis] @ localhost [127.0.0.1] | 110872 | 0 | Query | SHOW VARIABLES LIKE 'profiling' |
2020年8月4日 14:34:55 | rdis[rdis] @ localhost [127.0.0.1] | 110872 | 0 | Query | SHOW CHARACTER SET |
2020年8月4日 14:34:55 | rdis[rdis] @ localhost [127.0.0.1] | 110872 | 0 | Query | SHOW COLLATION |
2020年8月4日 14:34:55 | rdis[rdis] @ localhost [127.0.0.1] | 110872 | 0 | Query | SHOW DATABASES |
2020年8月4日 14:34:56 | [rdis] @ localhost [127.0.0.1] | 110873 | 0 | Connect | rdis@localhost on |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110873 | 0 | Query | SET NAMES utf8 |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110873 | 0 | Query | SHOW VARIABLES LIKE 'lower_case_%' |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110873 | 0 | Query | SHOW VARIABLES LIKE 'profiling' |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110873 | 0 | Query | SHOW CHARACTER SET |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110873 | 0 | Query | SHOW COLLATION |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110873 | 0 | Query | SHOW DATABASES |
2020年8月4日 14:34:56 | [rdis] @ localhost [127.0.0.1] | 110874 | 0 | Connect | rdis@localhost on |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110874 | 0 | Query | SET NAMES utf8 |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110874 | 0 | Init DB | atdps_a |
2020年8月4日 14:34:56 | [rdis] @ localhost [127.0.0.1] | 110875 | 0 | Connect | rdis@localhost on |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110875 | 0 | Query | SET NAMES utf8 |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110875 | 0 | Init DB | atdps_a |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110875 | 0 | Query | SET FOREIGN_KEY_CHECKS=0 |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110874 | 0 | Query | SET AUTOCOMMIT=0 |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110874 | 0 | Query | BEGIN |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110875 | 0 | Query | SET AUTOCOMMIT=0 |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110875 | 0 | Query | BEGIN |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110874 | 0 | Query | SELECT * FROM `a1` LIMIT 0 |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110875 | 0 | Query | SELECT * FROM `prescription_medicine` LIMIT 0 |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110874 | 0 | Query | SET net_write_timeout=86400 |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110874 | 0 | Query | SELECT BINARY CONCAT_WS(',',`SID`) ckey,MD5(CONCAT_WS(',',MD5(CONCAT_WS(',',IFNULL(LENGTH(`SID`),- 1),IFNULL(LENGTH(`PATIENT_ID`),- 1))),`SID`,`PATIENT_ID`)) md5a,`SID` FROM `a1`ORDER BY ckey |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110874 | 0 | Query | ROLLBACK |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110875 | 0 | Query | ROLLBACK |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110872 | 0 | Quit | |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110874 | 0 | Quit | |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110873 | 0 | Quit | |
2020年8月4日 14:34:56 | rdis[rdis] @ localhost [127.0.0.1] | 110875 | 0 | Quit |
2.1 其报错语句如下
SELECT BINARY CONCAT_WS(',',`SID`) ckey,MD5(CONCAT_WS(',',MD5(CONCAT_WS(',',IFNULL(LENGTH(`SID`),- 1),IFNULL(LENGTH(`PATIENT_ID`),- 1))),`SID`,`PATIENT_ID`)) md5a,`SID` FROM `a1`ORDER BY ckey
2.1.1、美化
SELECT
BINARY CONCAT_WS(',', `SID`) ckey,
MD5(
CONCAT_WS(
',',
MD5(
CONCAT_WS(
',',
IFNULL(LENGTH(`SID`) ,- 1),
IFNULL(LENGTH(`PATIENT_ID`) ,- 1)
)
),
`SID`,
`PATIENT_ID`
)
) md5a,
`SID`
FROM
`a1`
ORDER BY
ckey
2.1.2 问题分析
[Err] 1271 - Illegal mix of collations for operation 'concat_ws'
2.1.2.1、对于查询进行拆分,插入临时表进行分析
DROP TABLE IF EXISTS t2;CREATE TABLE if NOT EXISTS t2 SELECT
BINARY CONCAT_WS(',', `SID`) ckey,
SID as SID,
PATIENT_ID as PATIENT_ID,
CONCAT_WS(
',',
IFNULL(LENGTH(`SID`) ,- 1),
IFNULL(LENGTH(`PATIENT_ID`) ,- 1)
) as l1,
MD5(
CONCAT_WS(
',',
IFNULL(LENGTH(`SID`) ,- 1),
IFNULL(LENGTH(`PATIENT_ID`) ,- 1)
)
) as l2,
MD5(
CONCAT_WS(
',',
MD5(
CONCAT_WS(
',',
IFNULL(LENGTH(`SID`) ,- 1),
IFNULL(LENGTH(`PATIENT_ID`) ,- 1)
)
),
`SID`,
`PATIENT_ID`
)
) md5a
FROM
`a1`
ORDER BY
ckey;
SELECT COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = 'atdps_a' AND TABLE_NAME = 't2';
2.1.2.2、查询结果
2.1.2.3、分析
下面语句中 “MD5(CONCAT_WS(',',IFNULL(LENGTH(`SID`) ,- 1),IFNULL(LENGTH(`PATIENT_ID`) ,- 1)))” 的字符集是【utf8mb4 utf8mb4_general_ci】而Patient_ID的字符集是【gbk gbk_chinese_ci】,而CONCAT_WS不允许多个字符集的数据进行字符串拼接。
----------------------------------------------------------------------------------------------
MD5(
CONCAT_WS(
',',
MD5(CONCAT_WS(',',IFNULL(LENGTH(`SID`) ,- 1),IFNULL(LENGTH(`PATIENT_ID`) ,- 1))),
`SID`,
`PATIENT_ID`
)
) md5a
---------------------------------------------------------------------------------------------------
解决方案:使用 CONVERT 函数将不一致的字符集数据转换为一致
---------------------------------------------------------------------------------------------------
MD5(
CONCAT_WS(
',',
CONVERT(
MD5(CONCAT_WS(',',IFNULL(LENGTH(`SID`) ,- 1),IFNULL(LENGTH(`PATIENT_ID`) ,- 1)))
USING gbk ) COLLATE gbk_chinese_ci,
`SID`,
`PATIENT_ID`
)
) md5a
---------------------------------------------------------------------------------------------------
2.1.3、修复问题
SELECT
BINARY CONCAT_WS(',', `SID`) ckey,
MD5(
CONCAT_WS(
',',
CONVERT(MD5(
CONCAT_WS(
',',
IFNULL(LENGTH(`SID`) ,- 1),
IFNULL(LENGTH(`PATIENT_ID`) ,- 1)
)
) USING gbk ) COLLATE gbk_chinese_ci,
`SID`,
`PATIENT_ID`
)
) md5a,
`SID`
FROM
`a1`
ORDER BY
ckey;