MySQL server has gone away

MySQL About 1,964 words

原因一: MySQL服务宕了

查看MySQL的运行时长:

show global status like 'uptime';

输出:

+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Uptime        | 3414707 |
+---------------+---------+

原因二: MySQL连接超时

某个MySQL长连接很久没有新的请求发起,达到了server端的timeout,被server强行关闭。 此后再通过这个connection发起查询的时候,就会报错server has gone away (大部分PHP脚本就是属于此类

show global variables like '%timeout';

输出:

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| connect_timeout              | 28800    |
| delayed_insert_timeout       | 300      |
| have_statement_timeout       | YES      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 5        |
| innodb_rollback_on_timeout   | OFF      |
| interactive_timeout          | 1800     |
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 300      |
| net_write_timeout            | 300      |
| rpl_semi_sync_master_timeout | 5000     |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 60       |
| wait_timeout                 | 1800     |
+------------------------------+----------+

interactive_timeout/wait_timeout是1800秒,即MySQL链接在无操作1800秒后被自动关闭。

原因三: MySQL请求链接进程被主动kill

show global status like 'com_kill';

输出:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill      | 7     |
+---------------+-------+

原因四: Your SQL statement was too large

当查询的结果集超过max_allowed_packet也会出现这样的报错。

show global variables like 'max_allowed_packet';

输出: 64M

+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+

修改参数:

set global max_allowed_packet=1024*1024*16;

解决方案

在my.cnf文件中添加或者修改以下两个变量:(需重启)

wait_timeout=28800
interactive_timeout = 28800

如果不能修改my.cnf,可以执行语句(重启后失效)

SET GLOBAL interactive_timeout=28800;
SET GLOBAL wait_timeout=28800;
Views: 2,923 · Posted: 2019-04-07

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓

扫描下方二维码关注公众号和小程序↓↓↓


Today On History
Browsing Refresh