Mysql线上问题整理

一、 Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation ‘=’

问题描述：关联表查询时关联字段的排序规则不统一，导致查询报错

解决方案：
1.SQL层面
在表关联条件 ON A = B 后面加上统一的排序规则即可，如 ON A = B COLLATE ‘utf8mb4_unicode_ci’

2.表字段层面
统一关联表的两个关联字段的排序规则，如将第一张表中A字段排序规则改为 utf8mb4_unicode_ci 即可
ALTER TABLE 表名 MODIFY 字段 字段类型 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

扩展

若要修改表中所有字段的排序规则，可以采用如下方式：
1.首先需要查出表所有字段的全部信息

SHOW FULL COLUMNS FROM 表名;

得到该表需要修改的字段排序规则, 比如要将该表所有以utf8_general_ci排序规则的字段均更改为utf8_unicode_ci排序规则；

2.修改表字段的排序规则

SELECT CONCAT(‘ALTER TABLE ‘, TABLE_SCHEMA,’.’,TABLE_NAME, ’ MODIFY COLUMN ‘,COLUMN_NAME,’ ‘,COLUMN_TYPE,’ COLLATE utf8_unicode_ci;') ‘修正SQL’
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ‘数据库名’ AND TABLE_NAME = ‘表名’ AND COLLATION_NAME = ‘utf8_general_ci’;

贴出修正SQL执行即可修改成功；

同理，若要修改表字段的字符集，采用如下方式：
先查看表的所有字段信息

select * from information_schema.COLUMNS where TABLE_NAME = ‘表名’;

得到字符集后，按照上述修改排序规则的修正SQL一样得到修改字符集的修正SQL（只需加上一个CHARACTER_SET_NAME = ‘修改后的字符集’ 的过滤条件），然后执行即可修改成功；

二、Azure Databricks 写数据到Mysql表，抛出异常java.sql.BatchUpdateException: Duplicate entry ‘xxx’ for key ‘PRIMARY’

问题背景：线上有一个数据迁移需求，需要用Databricks将数据保存在MySQL业务表。由于直接将数据save到业务主表存在一定的风险，所以采用先将其save到生产环境的staging临时表，然后再从临时表merge到业务主表。并且业务表的表结构存在一个主键索引和一个唯一索引以及其他普通索引，然后我这边将业务表的表结构复制为临时表的表结构（仅去掉其他普通索引，保留主键索引和唯一索引），然后在Databricks上通过spark将数据写到Mysql，Job执行一段时间后不断的出现问题标题所示的异常…

问题分析：Spark通过多个分区写DataFrame数据时，会根据对应数据表的各个索引进行校验，校验如果出现主键或唯一键重复了，spark执行插入操作时就会抛出Duplicate entry异常（本例中后面确认过待插入的数据确实存在唯一键重复的问题）

解决方案：
1.业务数据问题：通过移除数据表的唯一键约束，协同业务方说明重复键问题，后续删除重复数据以避免Merge业务主表时异常
2.忽略异常：可通过配置spark的option参数，如option(“ignore”,“true”)忽略重复键冲突或数据类型错误等异常信息，同时指定写入追加模式mode(“append”)配合使用忽略异常信息

扩展

Spark批量写入MySQL的注意事项：
1.可指定一定的分区提高并发度，如repartition(12)，spark会给每个分区开启一个事务处理一个批次的数据
2.可指定数据写入的批次大小，如option(“batchsize”,10000)，spark会往MySQL的一个事务中写入10000条数据后再commit事务，另外需要配合option(“isolationLevel”,“NONE”)，即无需指定隔离级别

三、com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting…

问题背景：线上AKS某个微服务出现频繁的Pod重启现象导致影响服务业务处理功能，随后也尝试重启了一下，没有明显效果，并且查看了近两天的容器的CPU使用率和内存工作集状态，发现CPU使用率都是爆红的，但是内存工作集是正常的，于是查看了容器服务日志，发现log一直卡在com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting…不动了（显然是数据库连接池没有初始化完成）并且容器状态原因是“CrashLoopBackOff”，同时检查了连接相同库的其他微服务并没有出现频繁重启的现象

问题分析：可能是由于分配Pod的容器内存不足导致服务失败或运行过程中发生OOM触发AKS的Pod重启
问题排查：通过在AKS配置文件中添加从不重启策略（deployment -> podTemplate -> restartPolicy: Never）后重启服务，过一段时间后查看容器的状态为“OOMKilled”，很显然是容器分配的内存不足导致的
问题解决：将原来pod资源分配memory limits 从600Mi调整到1200Mi（deployment -> podTemplate -> resources -> limits -> cpu & memory），重启服务

验证服务日志：服务正常！

PS：感觉AKS上容器监控有点不太准，导致不能及时发现是OOM问题