MySQL “Got an error reading communication packet”错误原因查找.pdf

szrsu

285

5页

1次

2022-12-20

免费下载

zhishutech / tech-blog-en2zh

Public

tech-blog-en2zh / mysql / 28-mysql-got-an-error-reading-communication-packet-errors.md

yejr Update 28-mysql-got-an-error-reading-communication-packet-errors.md

…

History

2 contributors

Code Issues Pull requests Actions Projects Security Insights

master

MySQL “Got an error reading communication

packet”

作者：Muhammad Irfan

原文地址：https://www.percona.com/blog/2016/05/16/mysql-got-an-error-reading-

communication-packet-errors/

翻译：徐晨亮

In this blog post, we’ll discuss the possible reasons for MySQL “Got an error reading

communication packet” errors and how to address them.

在这篇文章中，我们将会讨论MySQL"Got an error reading communication packet"错误的原因以及

如何定位它们。

In Percona’s managed services, we often receive customer questions on communication failure

errors. So let’s discuss possible reasons for this error and how to remedy it.

在Percona的托管服务中，我们经常收到关于通信失败错误的客户咨询。因此让我们一起来讨论下

该错误可能的原因以及如何来规避。

##MySQL Communication Errors

First of all, whenever a communication error occurs, it increments the status counter for either

Aborted_clients or Aborted_connects, which describe the number of connections that were

aborted because the client died without closing the connection properly and the number of failed

attempts to connect to MySQL server (respectively). The possible reasons for both errors are

numerous (see the Aborted_clients increments or Aborted_connects increments sections in the

MySQL manual).

120 lines (64 sloc) 16.5 KB

首先，不论何时通信错误发生，状态计数器 Aborted clients 或者 Aborted connects 都会增加，表
示由于客户端未正确关闭连接而断开的次数，以及连接到MySQL服务器失败的尝试次数。这两个错
误的可能原因有很多(参见MySQL 手册中的Aborted_clients increments或aborted_connections
increments部分）
In the case of log_warnings, MySQL also writes this information to the error log (shown below):
在log_warnings>1的情况下，MySQL同样将该信息写入到error log（如下所示）:
In this case, MySQL increments the status counter for Aborted_clients, which could mean:
The client connected successfully but terminated improperly (and may relate to not closing
the connection properly)
The client slept for longer than the defined wait_timeout or interactive_timeout seconds
(which ends up causing the connection to sleep for wait_timeout seconds and then the
connection gets forcibly closed by the MySQL server)
The client terminated abnormally or exceeded the max_allowed_packet for queries
The above is not an all-inclusive list. Now, let’s identify what is causing this problem and how to
remedy it.
以下情况下， MySQL会增加Aborted_clients状态变量的计数器，也就意味着：
客户端已经成功连接，但是异常终止了（可能与未正确关闭连接有关系）
客户端sleep时间超过了变量wait_timeout或 interactive_timeout定义的秒数（最终导致连接休
眠的时间超过系统变量wait_timeout的值，然后被MySQL强行关闭）
客户端异常中断或查询超出了 max_allowed_packet值
以上是一个非全部包含的原因列表。现在让我们找出造成该问题的原因并且如何规避。
Fixing MySQL Communication Errors
To be honest, aborted connection errors are not easy to diagnose. But in my experience, it’s
related to network/firewall issues most of the time. We usually investigate those issues with the
help of Percona toolkit scripts, i.e. pt-summary / pt-mysql-summary / pt-stalk. The outputs from
those scripts can be very helpful.
说实话，aborted connections错误并不容易诊断。但是在我的经验中，大多数时间是网络或者防火
墙的原因。我们通常使用Percona toolkit脚本，例如 pt-summary / pt-mysql-summary / pt-stalk来
调查这些问题。这些脚本的输出非常有用。
Some of the reasons for aborted connection errors can be:
[Warning] Aborted connection 305628 to db: 'db' user: 'dbuser' host: 'hostname' (Got an err
[Warning] Aborted connection 305627 to db: 'db' user: 'dbuser' host: 'hostname' (Got an err

首先，不论何时通信错误发生，状态计数器 Aborted clients 或者 Aborted connects 都会增加，表
示由于客户端未正确关闭连接而断开的次数，以及连接到MySQL服务器失败的尝试次数。这两个错
误的可能原因有很多(参见MySQL 手册中的Aborted_clients increments或aborted_connections
increments部分）
In the case of log_warnings, MySQL also writes this information to the error log (shown below):
在log_warnings>1的情况下，MySQL同样将该信息写入到error log（如下所示）:
In this case, MySQL increments the status counter for Aborted_clients, which could mean:
The client connected successfully but terminated improperly (and may relate to not closing
the connection properly)
The client slept for longer than the defined wait_timeout or interactive_timeout seconds
(which ends up causing the connection to sleep for wait_timeout seconds and then the
connection gets forcibly closed by the MySQL server)
The client terminated abnormally or exceeded the max_allowed_packet for queries
The above is not an all-inclusive list. Now, let’s identify what is causing this problem and how to
remedy it.
以下情况下， MySQL会增加Aborted_clients状态变量的计数器，也就意味着：
客户端已经成功连接，但是异常终止了（可能与未正确关闭连接有关系）
客户端sleep时间超过了变量wait_timeout或 interactive_timeout定义的秒数（最终导致连接休
眠的时间超过系统变量wait_timeout的值，然后被MySQL强行关闭）
客户端异常中断或查询超出了 max_allowed_packet值
以上是一个非全部包含的原因列表。现在让我们找出造成该问题的原因并且如何规避。
Fixing MySQL Communication Errors
To be honest, aborted connection errors are not easy to diagnose. But in my experience, it’s
related to network/firewall issues most of the time. We usually investigate those issues with the
help of Percona toolkit scripts, i.e. pt-summary / pt-mysql-summary / pt-stalk. The outputs from
those scripts can be very helpful.
说实话，aborted connections错误并不容易诊断。但是在我的经验中，大多数时间是网络或者防火
墙的原因。我们通常使用Percona toolkit脚本，例如 pt-summary / pt-mysql-summary / pt-stalk来
调查这些问题。这些脚本的输出非常有用。
Some of the reasons for aborted connection errors can be:
[Warning] Aborted connection 305628 to db: 'db' user: 'dbuser' host: 'hostname' (Got an err
[Warning] Aborted connection 305627 to db: 'db' user: 'dbuser' host: 'hostname' (Got an err

of 5

免费下载

mysql

关注

评论