mysql锁问题Lock wait timeout exceeded; try restarting transaction 问题 某同事提交故障如下: 显示执行的SQL为： UPDATE USER_STATUS SET REMEMBER_ME = '[["soo6K5cZk","thilY3jZk","5865d0554677eff2ba06014891a5635a",1734675764872]' WHERE USER_ID = 'xxx' # 报错 Lock wait timeout exceeded; try restarting transaction 排查 查询当前运行的sql，以分析是否有锁冲突 show processlist; # 结果只有当前运行的sql，这条update只有单条数据，理论上应该很快，但当前已经执行了超过 40 秒，到最后反馈超时 查询当前的事物表，存在哪些事物在执行 select * from information_schema.innodb_trx; # 查询到这条sql已经回滚结束了，但是仍然有一个几小时之前的事物，没有显示sql语句 根据其id，和运行中的信息关联查询 select * from (SELECT * FROM information_schema.processlist p LEFT JOIN information_schema.INNODB_TRX t ON p.id = t.trx_mysql_thread_id) a where id=7936144\G; # 发现其状态是 Running，但没有sql 如何处理 评估对业务影响不大，所以杀掉这个线程id后执行正常

mysql的字符集问题 问题背景 以前一直没有对mysql的字符集做研究，用起来也没有感觉到有问题，这次出现了一次乱码后好好的对字符集做了测试和研究，这里记录一下 原始的问题是：安装某个第三方的服务，发现页面打开内容是乱码的，一开始怀疑是web端问题，但是检查后无异常，接着就想到是数据方面的问题了，果然在查询数据库数据后，发现内容都是乱码，又查看导入mysql的sql原始文件，内容是正常的，而且sql文件中表字符集是 utf8，且数据库肯定是utf8，这到底是什么问题呢？ 排查过程 1、在服务器使用mysql本地命令进入库内查询表，显示内容正常，怀疑是客户端字符集与服务端不一致导致，了解到 show variables like "%char%" 可以查询客户端使用的字符集，查询出来的内容如下: mysql> show variables like "%char%"; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/charsets/ | +--------------------------+----------------------------------+ 以上结果比较奇怪的是除了操作系统显示的 utf8，其他的字符集都是 latin1，从本地查询内容显示正常来反推，可以知道 client、connection、results、server 都是一致，那么到底是哪个参数影响呢？更重要的是建表语句明明是 utf8，那么这里使用 latin1，为什么又是正常的？ 基于这几个问题和查询显示的结果，又进行了一些思考和测试，如下： 我在服务器使用默认的mysql客户端登录查询没有问题，但是使用其他软件查询就存在问题，所以怀疑也跟客户端有一些关系（字符集） 所以我分别测试了客户端使用 --default-character-set 来指定 utf8 或者 latin1，或者登录后使用 set names latin1/utf8 来设置字符集，发现 utf8 的时候则出现乱码的问题，latin1 的时候则查询正常 那么这个问题原因可以被定位了，当前客户端字符集与当前数据不一致导致的，而不是当前客户端字符集与server的字符集不一致，所以当前的结论是当初导入数据的时候客户端使用了latin1的字符集导致，而跟表使用什么字符集关系不大了，重要的是数据是 latin1 的编码 对于mysql来说，库、表、数据都可以分别使用不同的字符集编码 还有一个问题是，为什么服务器本地登录的mysql字符集是 latin1？通过 mysql -V 看到当前是mysql8，于是我分别指定了本机的mysql客户端5.7和mysql8登录查看默认的字符集设置，发现了如下的情况 mysql5.7客户端登录 mysql> show variables like "%char%"; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/charsets/ | +--------------------------+----------------------------------+ mysql8客户端登录 mysql> show variables like "%char%"; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/charsets/ | +--------------------------+----------------------------------+ 可以看到mysql5.7客户端登录的时候是符合我们预期的，并且符合日常的使用规范，而mysql8登录的时候则变成了latin1，想到这里的配置与my.cnf有关系，于是我在多个配置文件 ~/.my.cnf /etc/my.cnf 中都添加配置 [client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 但mysql8登录后仍然是 latin1，完全没有效果 又对比测试了mysql8客户端登录另一个mysql8服务端，可以发现下面是正常的 mysql> show variables like "%char%"; +--------------------------+--------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8mb3 | | character_sets_dir | /opt/software/8.0.30/share/charsets/ | +--------------------------+--------------------------------------+ 8 rows in set (0.00 sec) 那么问题可以进一步定位：当初使用了 mysql8 的客户端登录了 mysql5.7 的服务端导致默认的字符集配置无效（mysql8客户端默认为utf8mb4）， 但使用时又变成了latin1，会是原因是什么呢？(为什么默认的utf8mb4没有生效，是使用的latin1？) 查找资料后，有了如下发现: https://github.com/PyMySQL/mysqlclient/issues/504 该文章与我遇到的问题一模一样，也是mysql8客户端连接mysql5.7服务端，出现的字符集问题，大意是mysql8连接mysql5.7的时候握手协商字符集，发现mysql5.7不支持（mysql5.7不知道utf8mb4_0900_ai_ci，它的版本是utf8mb4_general_ci），于是使用了服务端的character_set_server默认字符集，而我们mysql5.7的服务端是latin1，配置文件中没有设置为utf8，所以是默认的 至此，我们已经知道了所有导致的原因，进行如下总结： mysql5.7服务端没有设置字符集，那么 character_set_server 默认就会使用latin1 mysql8客户端协商登录的时候，因为两个大版本代号不一致，导致无法正常协商设置客户端的字符集为utf8mb4，从而退回使用服务端默认的字符集，也就是latin1 接着此客户端（当前登录的字符集是latin1）导入了sql文本数据，那么数据字符集变成latin1，与表和库的字符集都没有关系了 而后续mysql8使用正常，是因为数据是latin1，而它因为握手协商问题也是latin1，所以显示正常。如果其他客户端使用的utf8则查询显示乱码 关于mysql5.7的字符集 使用mysql5.7客户端登录mysql5.7服务端的时候，发现字符集正确被设置为utf8（没有在任何配置文件中设置客户端字符集为utf8，服务端也为设置，登录后看到为latin1），而mysql8开始客户端默认的字符集才为utf8mb4，所以这里也算一个小疑问，通过排查检索后发现，服务器的 LANG 也会影响字符集设置，经测试mysql5.7客户端登录mysql5.7服务端被正确设置是因为 LANG 是 en_US.utf8 或者 zh_CN.utf8，如果去掉utf8，则登录后变为latin1 至此，此次问题的根本原因已经清楚，所以需要恢复数据正常，应该做如下操作： 解决办法： 当前数据使用latin1的客户端导出，保证文本内容都正确，再使用 utf8的客户端导入 mysql字符集的后续注意事项： 需要在 my.cnf 中设置好客户端使用的字符集 需要在服务端中设置好字符集类型，首选肯定是utf8，保证协商失败后退回默认值是对的 在一些代码或者开发中如果不对，可以使用 set names 来排查测试 设置好linux服务器的LANG变量

zabbix web显示的权限问题以及后台日志数据库连接问题 问题现象 zabbix在升级到某个版本后前台页面一直不太正常，dashboard 中经常会提示 permission denied ，但不是持续显示，而是一阵一阵的出现 看浏览器请求，有时候 systeminfo 这个请求可以返回正常的内容，有时候只有样式，但是没有数据，且 message 内容是 permission denied，应该可以确定是没有获取到数据导致的 排查分析 没有获取到数据，一般有如下可能： nginx、php 进程问题，调用 php 或者 php 执行脚本或代码存在权限问题 数据库问题，最终 php 代码需要在库中查询数据，但其实有时候查不到 zabbix产品bug 于是我检查了 nginx, php 的配置，均没有发现任何问题，并且 nginx 的日志基本上都是正常响应，而后我怀疑是php脚本权限问题（或者php执行脚本的用户），所以我直接给了 .php 文件 755 的权限，这样所有用户都不会有执行权限问题了，但是问题仍然没有解决 比较怪异的是，重启 zabbix server 可以保持一段时间的正常显示，大概是十几到二十多个小时，之后又会间断性异常 持续排查 在第一次未果后，我计划尽量减少 zabbix server 端的日志，并且持续观察输出，避免有任何异常被错过，功夫不负有心人，终于发现了这些奇怪的日志: 19030:20240628:134313.575 [Z3005] query failed: [4031] The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior. [select u.userid,u.roleid,u.username,r.type from sessions s,users u,role r where s.userid=u.userid and s.sessionid='d938c03d7d2e857296543a4ec4d78547' and s.status=0 and u.roleid=r.roleid] 19042:20240628:134709.563 [Z3005] query failed: [4031] The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior. [select u.userid,u.roleid,u.username,r.type from sessions s,users u,role r where s.userid=u.userid and s.sessionid='ce79c6b9df0683d051e2ee318a55d54d' and s.status=0 and u.roleid=r.roleid] 19013:20240628:134809.545 [Z3005] query failed: [4031] The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior. [select u.userid,u.roleid,u.username,r.type from sessions s,users u,role r where s.userid=u.userid and s.sessionid='ce79c6b9df0683d051e2ee318a55d54d' and s.status=0 and u.roleid=r.roleid] 这个日志初次看起来似乎也跟权限问题挨不上边，都是一些会话中断的问题。不过如果细细想来，如果zabbix连接数据库的会话被异常中断，那么是否可能导致代码无法查询出来数据并且提示权限不足呢？ 这完全是有可能的 所以我根据上面的错误进行了检索，发现了可能的如下问题: https://support.zabbix.com/browse/ZBX-23145 这个帖子上面的问题并不与我们完全一致，但是错误的内容可能是相似的，都是数据库连接中断并且无法重连，而且在对话中有一个重点: Error 4031 ER_CLIENT_INTERACTION_TIMEOUT 这个基本上就是我们的日志错误内容，根据连接可以进入到mysql的异常说明中: https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html#error_er_client_interaction_timeout 结合起来可以初步断定是这个问题 解决方法 这个问题是特定 zabbix server 版本 与 mysql 版本碰撞在一起的问题，升级任意一个组件都可以解决。在升级到最新版后经过多天的观察，问题不再出现 思考 这个问题一开始确实很奇怪，完全无法确定是哪里的问题，也无法调试代码来判断是哪里返回的异常，最重要的是中间升级2-3个版本也没有解决，查找zabbix permission denied 的问题和解决方法极少。最后还是通过持续的观察和抓住所有可能的异常问题才确定了原因 所以解决问题还是要耐心，不能没有类似参考的例子就放弃解决，问题总是根据不同环境出现不同的现象，要善于持续的追踪、了解、分析问题。

mysqldump mysql导出 方法一 mysqldump 不导出库创建语句、不导出表结构，mysql只导出数据 /opt/software/5/bin/mysqldump -uroot -h192.168.10.28 -pxxx -P 3306 --default-character-set=latin1 -n -t xxx > xxx.20240619.sql 方法二 mysqldump 导出指定字符集 在导出的时候也是可以设置 default-character-set 的来指定字符集的，但是发现只对数据生效，对字段的COMMENT无效

mysql批量修改definer 问题背景 数据库恢复后视图中有较多的definer属性，需要修改，如果不嫌麻烦可以把视图删除重新创建，但是使用批量的方式修改definer属性更便捷 先查出所有的definer： select DEFINER from information_schema.VIEWS; 在查出的definer中如果需要修改"abc"@"%"为"zxc"@"%"，则执行以下语句生成修改语句： select concat("alter DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where DEFINER='abc@%'; 导出为sql文件，然后直接执行 select concat("alter DEFINER=`dflbj1`@`%` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where table_schema='dflbj1' and DEFINER='dflbj@localhost' into outfile '/var/lib/mysql-files/dflbj1.sql'; mysqlpump备份提示 "dflbj"@"localhost" 无法查询某个表，但是这种一般是视图的错误，也没有提示是哪个库，有两个库都有这个表，那只能通过 information_schema.views 查询两个库中的视图使用了提示的 definer 了，发现是 dflbj1 这个用户和库下的表，视图definer很多都不对，使用上面的方法批量修改