原因一: 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;