作为DBA运维的你是否有过这些苦恼:
现如今,DBA们在维护数据库实例(MySQL技术路线)时,面对字符集似乎已默认选择utf8 这个事情,是否关注过collation?
作为一名DBA运维人员,有时候会遇到一些我们无从下手的灵异现象。
例如:程序访问数据库每隔一段时间就会出现中断,时间可能固定或不固定,自己的三板斧抡完之后仍不奏效,求助开发,开发表示己方无错,夹在中间的你该如何处理?
心中有章,遇事不慌
如果我们第一次(或多次)遇到无法解决的问题该如何处理呢?可以关注公众号,关注《包拯断案》专栏,让小编为你排忧解难~
包拯秘籍:
✨一整套故障排错及应对策略送给你,让你像包拯一样断案如神:
#首先
我们碰到此问题之后,要做到心中有章(章程),遇事不慌。一定要冷静,仔细了解故障现象(针对开发/用户反馈的问题,仔细沟通故障现象、操作流程、数据库架构等信息);
#其次
我们需要根据故障现象进行初步分析,先判断是程序问题还是基础服务问题;
#然后
结合上述思考,我们需要进行逐步验证并排除,并确定问题排查方向;
#接着
确定了问题方向,针对具体问题进行具体分析。通过现象得出部分结论,通过部分结论继续进行排查及论证;
#最后
针对问题有了具体分析,并进行线下复现,从而梳理故障报告。
真实案例,我们能赢
说了这么多理论,想必实践更让你心动。
那么我们就拿一个真实案例进行分析:
某客户在GreatDB准生产环境调试存储过程时,其中有一个存储过程执行call proc报“ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_bin,IMPLICIT) and (utf8mb4_bin,IMPLICIT)”,但反馈测试环境没问题。
2.1 故障处理场景
夫耳闻之,不如目见之;目见之,不如足践之;足践之不如手辨之。
故障背景:
业务模拟割接调试存储过程中,发现call procedure时报“ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_bin,IMPLICIT) and (utf8mb4_bin,IMPLICIT)”错误,反馈测试环境没问题。
以下涉及到的信息均为自测模拟数据:
图片
收到信息10s后:询问调用存储过程的基础信息(包含:存储过程名、库名、实例环境信息)
收到信息20s后:存储过程使用的collations是什么,为什么会产生不一致?
发送命令:
show create procedure gobench1_proc\G
大脑回复:该存储过程字符集使用的是utf8mb4,校验集用的是utf8mb4_bin
信息如下:
greatdb> show create procedure gobench1_proc\G
*************************** 1. row ***************************
Procedure: gobench1_proc
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`pcms`@`%` PROCEDURE `gobench1_proc`(sid varchar(10))
begin
select count(*) from info where NAME = sid;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_bin
1 row in set (0.00 sec)
收到信息50s后:GreatDB全局校验集是什么?
发送命令:
show global variables like "%coll%";
大脑回复:GreatDB默认库级别的utf8mb4_0900_bin校验集
输出信息
greatdb> show global variables like "%coll%";
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| collation_connection | utf8mb4_0900_bin |
| collation_database | utf8mb4_0900_bin |
| collation_server | utf8mb4_0900_bin |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.01 sec)
收到信息70s后:存储过程中是否有字符串比较,存储过程参数传输是否有差异?
发送命令:
show create procedure gobench1_proc\G
大脑回复: 存储过程中涉及到几个表的查询,where条件中涉及到字符串比较
收到信息100s后:查看涉及到的表的字符集和校验集是什么?
发送命令:
select TABLE_COLLATION from information_schema.tables where table_schema='collation_dbs' and table_name in ('info');
大脑回复:表collation使用的也是utf8mb4_bin
收到信息120s后:查看存储过程涉及的表的校验集
发送指令:
select table_schema,table_name,COLUMN_NAME,COLLATION_NAME from COLUMNS where table_schema = 'collation_dbs' and TABLE_NAME in ('info') and COLLATION_NAME
AME like "utf8%";
大脑回复:表中字符串列使用的是utf8mb4_0900_bin
收到信息200s后:为什么库、表、存储过程的校验集和实例默认校验集不一致?
发送指令:
查看GreatDTS迁移工具在做表结构转换时,是否默认指定
大脑回复:经过排查和确认,GreatDTS转换表结构并未指定collation
收到信息250s后:表的字符集和校验集是哪来的?
发送指令:
查看库的校验集和表的校验集一致,进行测试,schema的校验集优先集比全局校验集高
大脑回复:当建库时如果指定校验集,则创建表时不指定校验集会默认继承库的校验集,而不是全局校验集
收到信息300s后:库的校验集哪来的?
发送指令:
测试-GreatDTS迁移工具创建库时是否指定了校验集
大脑回复:GreatDTS迁移工具创建库时会指定一个校验集,校验集为utf8mb4_bin,导致表的校验集也是utf8mb4_bin
(故障已定位并反馈,进行相应应急处理…)
问题原因:
因为使用GreatDTS迁移工具在做去O的表结构转换时,如果库不存在,则会默认创建库。
在创建库时指定了字符集和校验集(字符集为utf8mb4,校验集为utfmb4_bin),表结构转换时不指定校验集和字符集,所以表的字符集和校验集继承了库,同时列的字符串校验集也会继承表的校验集,创建存储过程时默认列的校验集会继承库的校验集。
处理手段:
方法1:临时处理
如果存储过程中用到的条件列比对只涉及一个表,或多个表条件列都是同一个校验集,那么可以通过session collation来规避;
方法2:永久处理
如果存储过程中用到的条件列对比涉及多个表,且条件列的校验集都不相同,那么需要重建表结构,将所有的字符串相关的列、库、表、存储过程、视图等的校验集都对齐。
经过上述一顿猛如虎的操作和排查,5分钟内快速定位了问题原因,且及时和客户业务方进行沟通,最大化地减少并避免了业务受到的影响。
同时,在故障排查过程中,保留了排查步骤及结果图,故障处理完成后进行故障报告编写,全流程专业、顺畅、有序的操作得到了客户的认可与肯定。
整体项目运维经验复盘:
1)本次故障主要由于第三方工作做表结构迁移导致;
2)指定数据库运行环境及运行状态需要定期巡检;
3)需增加相关参数的定期监控;
4)要将根据巡检项开发自动化运维脚本与告警相结合。
番外篇-展昭答疑解惑
1)为什么列的校验集和表的还不一致?
答:由于时间紧迫,且该环境有多人操作,可能导致列无法追查,也有可能做过alter table语句,做过业务适配修改。
2)为什么100多个存储过程,只有这一个存储过程有问题?
答:存储过程中如果不涉及到表列的比较,且校验集一致,则不会存在该问题。