mysql 长连接 has gone away,MySQL server has gone away

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