因为修改mysql字段编码以及排序规则导致的一次线上生产故障

背景

        在一次晚上九点半的发布完成后，A同学在进行功能验证的时候，发现有一个sql查询很慢，通过explain关键字看后发现是ALL类型，没有走上索引。B同学这时候提出可能是因为字段长度不同导致走不上索引，在修改两个表的关联查询的字段长度一致后，发现还是走不上索引。B同学又提出可能是因为字段编码不同导致的问题，经查，发现编码确实不同。于是修改了字段编码以及排序规则，原来的字段编码格式是utf8，修改后的字段编码是utf8mb4，走上了索引，解决了查询性能问题，但是导致了一个更大的问题产生。。。

问题发现（2022-12-01 23:35）

        1、三方合作厂商反馈APP上的通行码不能显示，于是在第一时间查看后端接口响应，发现接口响应是正常且正确的；

        2、于是咨询APP的前端有没有发布，APP的前端反馈今天没有发布；

        3、看浙里办上的通行码，是能够显示正确的，说明后端接口也是没有问题的，只是APP那边存在某一个问题导致APP的通行码不能显示；

        4、由于三方厂商都是通过开放平台调用我们接口，所以又去开放平台看接口调用，发现出参中的报错信息是“该人员信息uid已不存在，请确认”。接着就看入参，发现是大写的uid，然后我们就去数据库查询，发现刚刚改的那个字段确实不支持忽略大小写查询了。问三方厂商，三方厂商说是一直都是传递的大写的uid的，包括之前联调的需求。这么一说，以前联调的时候确实他们是传递的大写的uid的，只不过字段支持大小写查询，也就忽略了这个隐藏的问题。

问题定位（2022-12-01 23:45）

        综上所述，定位到问题是因为修改后的字段不支持忽略大小写查询了。

问题解决

        目前定位到问题了，由于两个原因，导致APP那边的业务不通。一个是我们这边的字段编码修改了，一个是合作厂商那边的uid传递的都是大写的，所以从这两个角度进行解决这个问题。

        首先，我们想让合作厂商修改下uid传递小写，咨询过三方厂商后，由于以下两点原因，他们那边无法发布更改，所以他们那边不能动（2022-12-01 23:50）：

        1、该合作厂商git使用不规范，很多未开发完成、测试完成的代码都在master上，所以现在不能发布；

        2、将身份证生成md5大写的方法是另外一个已请假的同事写的，所有的业务都是传递的这个大写的uid，现在改了如果不测试的话，也不敢贸然改动，不确定影响范围有多大。

        那么此时，就只能我们这边动了。

        步骤：

        1、首先，我们想的是先把字段编码改回来，但是目前业务方已经在运行了，发现通过客户端工具执行DDL过程中，很快就无响应了，此时所有接口都瘫痪了，此时需要直接杀进程了（2022-12-01 23:52）；

        2、先通过如下命令查询正在执行的事务，发现并没有找到刚刚的那个DDL事务（事后经查，DDL的时候实际上是一个写文本文件的过程，因此不支持事务）（2022-12-01 23:55）；

select * from information_schema.INNODB_TRX;

        3、找不到该事务后，就只能换命令了，这时候我想到这个可能不是一个事务，但肯定是一个进程，可以通过如下命令显示正在执行的进程（2022-12-01 24:00）；

show processlist;

        4、执行命令后，再根据Info列排序，找到了刚刚的那个DDL命令，然后找到对应的进程id，再执行kill id，再请求接口发现还是不行，然后又执行了上述命令，还是有这个DDL进程存在的，但是是一个新的进程id（这点没搞懂，为什么会再有一个新的进程id，难道是mysql的重试吗？），然后又kill 新的进程id，此时接口回复正常了，只是合作厂商的服务还是不行，因为此时字段并没有成功做到更改（2022-12-02 00:10）。

        5、然后C同学发给我一个sql，可以更方便直观的看到此时的进程：（2022-12-02 00:13）

select * from information_schema.processlist where Command != 'Sleep' order by Time desc;

        执行之后，是这个结果

        6、此时没有什么进展，都在找方法，后来C同学提出了lock=none这个参数，也就是在DDL语句末尾加上这个参数。

lock=none

        此时我们并没有急于在生产环境直接执行，而是先在测试环境执行，执行通过；然后又在生产环境的bak表上执行，也是执行成功的。但是测试环境以及生产环境的bak表的数据库事务不是很多，所以执行10s左右都成功了。最后是在生产环境的表执行优化后的sql，发现还是不行，还是会锁住。然后又回到了d步骤（2022-12-02 00:45）；

        7、此时又没有了进展，所以我就想先了解下字段的排序规则的原理，因为刚刚改的是字段编码+字段排序规则，字段编码规范都是用的utf8mb4，所以问题应该不会出现在字段编码上（2022-12-02 01:02）;

        8、因此得出，只有以_ci结尾的排序规则是不区分大小写的，但是目前是区分大小写的，所以目前的排序规则应该不是以_ci结尾。refresh数据库，查看字段信息，发现字段编码是utf8mb4，但是排序规则是utf8mb4_bin！！！！此时，算是定位到了最核心的问题了，也就是我们不用修改字段编码了，只需要把排序规则改为utf8_general_ci即可（2022-12-02 01:03）;

        9、既然不用修改字段编码了，那么改下sql，把编码去掉试试可以吗？经过尝试，发现并不能只修改排序规则，sql语法直接就报错了（2022-12-02 01:16）；

        10、后来我们讨论了表编码影响、增加接口的拦截器将uid的大写改为小写、先bak一张表然后改bak表字段的排序规则再改表名改回来等等方式解决这个问题，包括之前改那次成功可能是因为服务没起来，所以没有事务，所以很快就改成功了的讨论（2022-12-02 01:25）；

        11、讨论下来感觉原理都差不多，于是C同学给DBA打电话（2022-12-02 01:30）；

        12、DBA电话没打通，所以只剩了一种办法了，只能停服务了，模拟之前改成功的那次的场景。刚开始是把三方合作厂商的应用停了，执行DDL，发现还是不行，执行d步骤。于是又把我们这边的两个会读写这张表的两个应用停掉了，等数据库中没有未提交的事务之后，执行DDL，大约10秒，执行完毕。refresh数据库，查看字段信息，字段编码是utf8mb4，排序规则已经变成了utf8_general_ci！！！！此时在数据中查询大写uid，是能够查询到数据的（2022-12-02 01:45)！

        13、然后启动刚刚停掉的三个应用的服务，服务启动成功后，验证通过。此时，查询性能问题以及引起的这个字段排序规则问题都已经解决，系统恢复正常（2022-12-02 01:50)。

反思

        在此次事件中主要暴露了两个问题：

        1、合作厂商都是走archery审批，但是我们字段修改不规范，没有走arhcery审批：

        1）这个问题是因为字段排序规则修改错误引起的，我们修改的时候是通过数据库客户端可视化界面来进行操作的，并不是通过sql进行的。（反思：即使通过可视化界面来操作，也应该看sql预览，检查一下sql，执行sql来进行DDL，这样就能避免改错排序规则问题的产生，而不是通过客户端工具，并不能盲目相信可视化界面）；
        2）由于我们是内部同学，所以难免有些数据库的特权，这就导致了操作数据库的不规范（反思：sql变更尤其是DDL变更，还是应该通过平台工具的，比如archery等工具。规范也能减少问题的产生）；
        3）数据库的字段的变更也应该报备一声，评估一下风险（反思：字段的变更还是需要谨慎谨慎再谨慎）；

        2、对于三方合作厂商一直传递的大写uid的问题，之前联调的时候发现过这个问题，但是当时觉得反正查询大小写忽略了，就没事了，然后就导致了这次的问题（反思：要做到有问题及时反馈，及时修正）。

后续安排

        1、对于已创建的数据库和要创建的数据库做到编码统一，避免修改编码：

        1）之前有通过阿里云、archery两种方式建库，但是archery默认的库编码是utf8，字段编码是utf8，需要将默认的编码都改为utf8mb4，避免后续建表再建出utf8编码的库和表（已完成）;

        2）将已建的所有库、表、字段的编码改为utf8mb4以及字段的排序规则改为utf8_general_ci，避免之后此类的问题产生（已完成）；

        2、对于我们内部同学操作数据库不规范问题，后续C同学会回收所有开发同学的数据库账号权限（已完成）；

        3、三方合作厂商需要修改通过小写的uid作为请求参数，已经排进他们的计划中了（已完成）。

总结

        最后还是通过停应用的方式来解决的。为什么没有在一开始就停应用呢？是因为考虑到修改的字段的那张表被两个程序读写，支撑了APP、浙里办、工作台三个应用，由于只有APP传递的uid不规范，所以只有APP是不可用的，其他两个应用是可以正常使用的，为了保障其他两个应用可用，所以才在最后时刻采用了这个办法。

        在写到这里的时候，发现如果当时通过archery进行DDL，其实也不用浪费这么多时间。