环境：

MacOS 10.12

客户端Sequel Pro 1.1.2

MySQL 5.7.17

 

 

客户端连接数据库报错：

连接失败：

Connection failed!

Unable to connect to host 127.0.0.1, or the request timed out.

Be sure that the address is correct and that you have the necessaryprivileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Lost connection to MySQL server at 'reading initialcommunication packet', system error: 0

 

情景分析：在执行contains查询之后重新打开一个连接标签，连接数据库报错。后来修复好这个问题之后又重新这样操作了一下，没影响，说明和contains语句没关系。

 

查看了下本地mysql错误日志：

 

2017-09-26T02:12:03.297648Z 14 [Note] Access denied for user'root'@'localhost' (using password: YES)

2017-09-26T02:31:12.057623Z 18 [Note] Aborted connection 18 to db:'b2b_test' user: 'root' host: 'localhost' (Got an error reading communicationpackets)

2017-09-26T02:31:12.057776Z 17 [Note] Aborted connection 17 to db:'b2b_test' user: 'root' host: 'localhost' (Got an error reading communicationpackets)

2017-09-26T02:31:12.057838Z16 [Note] Aborted connection 16 to db: 'unconnected' user: 'root' host: 'localhost'(Got an error reading communication packets)

 

 

查询了官网错误码相关信息：

 

原文：

 

MySQL5.7 Reference Manual 

B.5.2.10 CommunicationErrors and Aborted Connections

Ifconnection problems occur such as communication errors or aborted connections,use these sources of information to diagnose problems:

·The error log. See Section 5.4.2, “The Error Log”.

· The general query log.See Section 5.4.3, “TheGeneral Query Log”.

·The Aborted_xxx and Connection_errors_xxx status variables.

See Section 5.1.7,“Server Status Variables”.

· The host cache, which isaccessible using the host_cache Performance Schema table. See Section 8.12.5.2,“DNS Lookup Optimization and the Host Cache”,andSection 25.11.16.1,“The host_cache Table”.

If you start the server withthe log_error_verbosity system variable set to 3, you might find messages like this in yourerror log:

[Note] Aborted connection 854 to db: 'employees' user: 'josh'

If a client is unable even toconnect, the server increments the Aborted_connects status variable. Unsuccessful connection attempts can occur for thefollowing reasons:

· A client attempts toaccess a database but has no privileges for it.

· A client uses anincorrect password.

· A connection packet doesnot contain the right information.

· It takes more than connect_timeout seconds to obtain a connect packet. See Section 5.1.5,“Server System Variables”.

Ifthese kinds of things happen, it might indicate that someone is trying to breakinto your server! If the general query log is enabled, messages for these typesof problems are logged to it.

If a client successfully connects butlater disconnects improperly or is terminated, the server increments the Aborted_clients status variable, and logs an Abortedconnection message to the error log. The causecan be any of the following:

·The client program didnot call mysql_close() before exiting.

·The client had beensleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server. See Section 5.1.5,“Server System Variables”.

·The client program endedabruptly in the middle of a data transfer.

Otherreasons for problems with aborted connections or aborted clients:

·The max_allowed_packet variable value is too small or queries require more memory than youhave allocated for mysqld.See Section B.5.2.9, “Packet TooLarge”.

· Use of Ethernet protocolwith Linux, both half and full duplex. Some Linux Ethernet drivers have thisbug. You should test for this bug by transferring a huge file using FTP betweenthe client and server machines. If a transfer goes in burst-pause-burst-pausemode, you are experiencing a Linux duplex syndrome. Switch the duplex mode forboth your network card and hub/switch to either full duplex or to half duplexand test the results to determine the best setting.

·A problem with the threadlibrary that causes interrupts on reads.

·Badly configured TCP/IP.

· Faulty Ethernets, hubs,switches, cables, and so forth. This can be diagnosed properly only byreplacing hardware.

See also Section B.5.2.8,“MySQL server has gone away”.

 

谷歌翻译如下：

B.5.2.10通信错误和异常连接

如果发生连接问题，例如通信错误或中止的连接，请使用这些信息源来诊断问题：

·错误日志。请参见第5.4.2节“错误日志”。

· 一般查询日志。请参见第5.4.3节“常规查询日志”。

· 该 和 状态变量。请参见 第5.1.7节“服务器状态变量”。 Aborted_xxxConnection_errors_xxx

· 主机缓存，可以使用host_cache性能模式表进行访问 。请参见第8.12.5.2节“DNS查找优化和主机缓存”和 第25.11.16.1节“host_cache表”。

如果您将log_error_verbosity系统变量设置为3 启动服务器，则 可能会在错误日志中找到类似的消息：

[Note] Aborted connection 854 to db: 'employees' user: 'josh'

如果客户端无法连接，服务器会增加Aborted_connects状态变量。由于以下原因，可能会发生不成功的连接尝试：

·客户端尝试访问数据库，但没有权限。

·客户端使用错误的密码。

·连接数据包不包含正确的信息。

·connect_timeout获取连接数据包 需要 几秒钟的时间。请参见 第5.1.5节“服务器系统变量”。

如果这些事情发生，这可能表明有人试图打破您的服务器！如果启用了常规查询日志，则会记录这些类型问题的消息。

如果客户端成功连接，但之后断开连接不正确或终止，则服务器将增加 Aborted_clients状态变量，并将“ 中止连接”消息记录到错误日志中。原因可以是以下任何一种：

·客户端程序mysql_close()在退出之前未调用 。

·客户端已经睡眠超过 wait_timeout或 interactive_timeout 秒，而不向服务器发出任何请求。请参见 第5.1.5节“服务器系统变量”。

·客户端程序在数据传输过程中突然结束。

中止连接或中止客户端出现问题的其他原因：

·该max_allowed_packet 变量的值太小或查询需要比你分配更多的内存的mysqld。见 B.5.2.9节，“Packet Too Large”。

·使用以太网协议与Linux，一半和全双工。一些Linux以太网驱动程序有这个错误。您应该通过在客户端和服务器机器之间传输使用FTP传输大量文件来测试此错误。如果传输进入突发暂停突发暂停模式，则会遇到Linux双工综合症。将您的网卡和集线器/交换机的双工模式切换到全双工或半双工，并测试结果以确定最佳设置。

· 线程库的问题导致读取中断。

· 配置不正确的TCP/ IP。

·故障以太网，集线器，交换机，电缆等。这可以通过更换硬件来正确诊断。

另见B.5.2.8节“MySQL服务器已经消失”。

 

 

 

 

我是属于第二种情况，客户端关闭连接姿势不对，简单点说就是mysql_close()在关闭客户端连接的时候未被调用，就类似支付完成没有支付成功回调一样的道理。那次是怎么解决呢，最快的办法就是重启mysql服务。

本文链接二维码可以保存在本地：保存

http://phpersay.com/index.php?c=index&m=articleDetailInfo&id=80

分类 MySQL | 浏览 (3929) | 赞 (0) | 踩 (0) | 作者:Ivan（一凡） | 2017-09-26 12:27:16 |

相关文章

1. Mac上安装MySQL 并配置 my.cnf
2. MySQL kill 其中运行时间很长的sql
3. MySQL数据超出字段范围会报错：Out of range value for column
4. Mac下mysql服务启动失败原因小记
5. Geometry byte string must be little endian.