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

转自:https://mp.weixin.qq.com/s/N-zNNOzPeX72jrCxeHGAnw

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

 出现问题的版本: MySQL 8.0.23

推荐使用MGR的同学升到MySQL 8.0.23后的版本,非常稳在ProxySQL+MySQL 8.0 MGR结合中有一个监控用到SQL如下,工作多年一直比较稳,在8.0.23中倒下了

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的配置:

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%";

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

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其它相关的字段就不想说什么了。

mysql> select charset(MEMBER_HOST), MEMBER_HOST,charset(@@hostname), 
@@hostname from performance_schema.replication_group_members;

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

  1. 把@@hostname做字符转换

select * from performance_schema.replication_group_members  where  
performance_schema.replication_group_members.member_id=convert(@@server_uuid using ascii);

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

  1. 使用utf8的字段做对比 最终的修复办法如下:

select * from performance_schema.replication_group_members  where  
performance_schema.replication_group_members.member_id=@@server_uuid;

select * from performance_schema.replication_group_members  where  
performance_schema.replication_group_members.member_id=@@server_uuid;

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

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遇到问题或是经验也欢迎在评论中分享。

原文地址:https://www.cnblogs.com/digdeep/p/14882148.html