MySQL每隔一小会不操作报错ERROR 2013 (HY000) Lost connection to MySQL server during query问题

本文主要是介绍MySQL每隔一小会不操作报错ERROR 2013 (HY000) Lost connection to MySQL server during query问题，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MySQL每隔一小会不操作报错ERROR 2013 (HY000) Lost connection to MySQL server during query问题

连接OA的MySQL库发现每隔一小会不操作就报错如下：

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-01-11 09:47:41 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now();
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select now();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    453751
Current database: *** NONE ***

+---------------------+
| now()               |
+---------------------+
| 2022-01-11 09:48:27 |
+---------------------+
1 row in set (0.00 sec)

实际上有两个参数如下：

interactive_timeout = 1800  ##交互式会话空闲超时时间(mysql工具、mysqldump等)

wait_timeout = 1800  ##非交互式会话空闲超时时间，mysql api程序,jdbc连接数据库等

查看数据库的这两个参数值（单位：s）：

mysql> show variables where Variable_name in ('interactive_timeout','wait_timeout');
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 30    |
| wait_timeout        | 30    |
+---------------------+-------+
2 rows in set (0.00 sec)

由于我是使用mysql数据库本身的命令行交互式客户端mysql，因此调整调整interactive_timeout的值即可。

通过调整interactive_timeout=10,30,60后，会话1连接进去不操作，会话2持续观察：

会话2：
mysql> show full processlist;
+--------+------+---------------------+-------+---------+------+-------+-----------------------+
| Id     | User | Host                | db    | Command | Time | State | Info                  |
+--------+------+---------------------+-------+---------+------+-------+-----------------------+
|      2 | root | ::1:53493           | td_oa | Sleep   |    1 |       | NULL                  |
|     41 | root | ::1:53566           | td_oa | Sleep   |   28 |       | NULL                  |
|     45 | root | ::1:53574           | td_oa | Sleep   |   20 |       | NULL                  |
|   1709 | root | ::1:56311           | td_oa | Sleep   |   68 |       | NULL                  |
| 454546 | root | 192.168.1.188:54046 | NULL  | Sleep   |    7 |       | NULL                  |
| 454553 | root | 192.168.1.188:54048 | NULL  | Query   |    0 | init  | show full processlist |
+--------+------+---------------------+-------+---------+------+-------+-----------------------+
6 rows in set (0.00 sec)

这篇关于MySQL每隔一小会不操作报错ERROR 2013 (HY000) Lost connection to MySQL server during query问题的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！