MySQL MGR监控中遇到的字符集Illegal mix of collations

2021-07-15 13:14:01 浏览数 (1)

ProxySQL整合MGR提供高可用，是我们知数堂课程中提供一个MySQL高可用的解决方案，架构如下：

出现问题的版本: MySQL 8.0.23

推荐使用MGR的同学升到MySQL 8.0.23后的版本，非常稳

在ProxySQL MySQL 8.0 MGR结合中有一个监控用到SQL如下，工作多年一直比较稳，在8.0.23中倒下了

代码语言：javascript复制
mysql> select * from performance_schema.replication_group_members  where 
performance_schema.replication_group_members.member_host=@@hostname;

报错：

ERROR 1267 (HY000): Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8_general_ci,SYSCONST) for operation '='

从提示上可以看到是在"="值查询中两个校验的字符集不一样，直接报错了。在以前的版本中，一样最多就是全表扫描罢了,但在8.0.23中比较严格直接报错。

先上修复结果：https://github.com/zhishutech/mysql_gr_routing_check 直接使用即可。另外也记住两个结论：

  1. MySQL早期两个字段字符集不一致，可能会导致全表扫描 （性能万恶之源）
  2. 在8.0.23中两个字段字符集不一致，目前看是直接报错（这个给赞）

原因分析：

在使用addition_to_sys8.sql中需要用到表，大多来自于performance_schema，利用SQL查询一下看看那些字段是ascii_general_ci的配置：

代码语言：javascript复制
SELECT table_schema, table_name, column_name, character_set_name, 
collation_name FROM information_schema.columns WHERE COLLATION_NAME 
='ascii_general_ci' and table_schema='performance_schema' and table_name like "%group%";

1 row in set (0.00 sec)

可以看到我们用的member_host定义成ascii码的字符集了。确认一下：

代码语言：javascript复制
CREATE TABLE `replication_group_members` (
  `CHANNEL_NAME` char(64) NOT NULL,
  `MEMBER_ID` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `MEMBER_HOST` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `MEMBER_PORT` int DEFAULT NULL,
  `MEMBER_STATE` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `MEMBER_ROLE` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `MEMBER_VERSION` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

performance_schema.replication_group_members 这个表不允许更改的，官方这块的开发人员应该扣KPI了，表的collate定义成utf8mb4_0900_ai_ci, 字段的定义成：utf8mb4_bin,这个如果互联网公司，就要用鞭刑处罚了。不过，我看看MySQL其它相关的字段就不想说什么了。

代码语言：javascript复制
mysql> select charset(MEMBER_HOST), MEMBER_HOST,charset(@@hostname), 
@@hostname from performance_schema.replication_group_members;

3 rows in set (0.00 sec)

看到这里可以几种解办法：

  1. 把@@hostname做字符转换
代码语言：javascript复制
select * from performance_schema.replication_group_members  where  
performance_schema.replication_group_members.member_id=convert(@@server_uuid using ascii);

但该修复方法和我们自已写的开发规范有些冲突，尽量减少在MySQL中运算。

  1. 使用utf8的字段做对比 最终的修复办法如下：
代码语言：javascript复制
select * from performance_schema.replication_group_members  where  
performance_schema.replication_group_members.member_id=@@server_uuid;

最后：从定位上看其实这是一个历史遗留问题，可以用下面SQL看一下目前MySQL还有多少字段是ascii码定义的，做到心里有数，掉到坑里也可以快速扑出来：

代码语言：javascript复制
SELECT table_schema, table_name, column_name, character_set_name, 
collation_name FROM information_schema.columns WHERE COLLATION_NAME 
='ascii_general_ci';

我看官方的bug社区中也有人提交类似的问题，但官方是对牛弹琴的搪塞过去。如果你是官方企业版用户，可以看看利用你们的企业版特权给官方提个SR，建议官方把MySQL8.0.25后的这些acssii码字段，全部改成和系统一样吧。也是推动MySQL 8.0早日向utf8mb4转换。

如果你有使用MGR遇到问题或是经验也欢迎在评论中分享。

sql 数据库 云数据库SQLServer ascii 网络安全

0 人点赞