正在对 RDS for MySQL 运行语句,出现 "MySQL server has gone away" 报错 或 "Lost connection to server during query"错误,我该如何排查并解决此类问题?
对于此类问题,最常见的原因是 MySQL Server timeout 并关闭客户端连接,您可以会收到如下错误[1]:
注:为了避免翻译的准确性,下述内容采用MySQL 官方文档原文
Error Code | Description |
---|---|
CR_SERVER_GONE_ERROR | The client couldn't send a question to the server. |
CR_SERVER_LOST | The client didn't get an error when writing to the server, but it didn't get a full answer (or any answer) to the question. |
您可以参考如下解决方案来避免此类错误:
1.如果连接超时,您可以将 wait_timeout[2] 和 interactive_timeout[3] 参数的值调高一些,从而延长 MySQL 超时时间。
# 查看当前配置
mysql> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| interactive_timeout | 600 |
+---------------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 600 |
+---------------+-------+
1 row in set (0.00 sec)
2.请验证应用程序超时短于 MySQL 超时,并确保应用程序关闭空闲连接,因为如果连接空闲时间过长,则可能会从客户端不正确的关闭该连接。
3.如果您的查询结果过大,请将max_allowed_packet [4]参数调高。
#查看当前配置
mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 33554432 |
+--------------------+----------+
1 row in set (0.01 sec)
4.如果您有设置init_connect[5],请务必确保用户对参数中设置的连接流程均具有 EXECUTE 权限。
#查看当前配置
mysql> show global variables like 'init_connect';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| init_connect | |
+---------------+-------+
1 row in set (0.00 sec)
5.如果所有连接同时中断,请检查MySQL错误日志,确认实例是否处于健康状态。
注:本文不会给出具体的建议值,因为这与您的应用程序息息相关。
[1] https://dev.mysql.com/doc/refman/5.7/en/gone-away.html
[2] https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout
[3] https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_interactive_timeout
[4] https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
[5] https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_init_connect
如果您有其他问题,欢迎您联系火山引擎技术支持服务