ERROR 1160 (08S01) : Got an error writing communication packets

    应用程序端碰到错误提示，MySQLNonTransientConnectionException:Got an error writing communication packets。与packet有关，之前一直都没有碰到过这个问题。数据库error日志也未见半点异常，到底是怎么一回事呢？

1、故障现象 故障环境： $ cat /etc/issue CentOS release 5.9 (Final) Kernel \r on an \m

[mysql@GZ-DB-MASTER01 ~]$ mysql --version mysql  Ver 14.14 Distrib 5.6.12, for Linux (x86_64) using  EditLine wrapper

客户端抛出的异常:MySQLNonTransientConnectionException:Got an error writing communication packets 数据库服务器端异常： mysql> flush tables; ERROR 1160 (08S01): Got an error writing communication packets

--由于使用了federated引擎，客户端查询的表为federated，以为是bug呢 --而直接查询federated是ok的，有可能是返回的数据量没有达到设置值的缘故，因为我们只是简单的count mysql> select count(*) from tadv_gold; +----------+ | count(*) | +----------+ |    31525 | +----------+ 1 row in set (0.46 sec)

mysql> select count(*) from tadv_invest_record; +----------+ | count(*) | +----------+ |     6761 | +----------+ 1 row in set (0.08 sec)

二、分析与解决 MySQL监控看到了Slave has max_allowed_packet size less than master的提示，下面是这个问题的描述： Each slave in a replication topology should have a max_allowed_packet size at least as large as its master's. The max_allowed_packet variable sets an upper limit on the size of any single message between the MySQL server and clients, including replication slaves. If you are replicating large column values (such as might be found in TEXT or BLOB columns) and max_allowed_packet is too small on the master, the master fails with an error, and the slave shuts down the I/O thread. If max_allowed_packet is too small on the slave, this also causes the slave to stop the I/O thread.

建议： Investigate why slave SZ-DB-SRV01:3307 has a max_allowed_packet size of 4 MiB, which is less than master SZ-DB-SRV01:3306s max_allowed_packet size of 16 MiB.

Links and Further Reading MySQL Manual: Replication and max-allowed-packet MySQL Manual: System Variable Index - M MySQL Manual: Server System Variables set global max_allowed_packet=16777216

--上面的描述是主从之间的参数max_allowed_packet不一致，似乎与本文的问题无关 --因为我们从该库调用的federated引擎目标表并不位于这个packet值过小的从库，而是另外一个实例。 --即使从库设置的过小，理论上应该不影响当前库federated到另一实例的message大小值。 --还是尝试进行了修改，因为调整到16MB，对当前的硬件影响不会太大。

mysql> show variables like '%packet%'; +--------------------------+------------+ | Variable_name            | Value      | +--------------------------+------------+ | max_allowed_packet       | 4194304    | | slave_max_allowed_packet | 1073741824 | +--------------------------+------------+ 2 rows in set (0.00 sec)

mysql> set global max_allowed_packet=16777216; Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%packet%'; +--------------------------+------------+ | Variable_name            | Value      | +--------------------------+------------+ | max_allowed_packet       | 16777216   | | slave_max_allowed_packet | 1073741824 | +--------------------------+------------+ 2 rows in set (0.00 sec)

--再次flush tables，成功鸟。 mysql> flush tables;                   --Author: Leshami Query OK, 0 rows affected (0.03 sec)   --Blog  : http://blog.csdn.net/leshami

3、关于参数max_allowed_packet The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function. The default is 4MB as of MySQL 5.6.6, 1MB before that.

The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet. You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple. When you change the message buffer size by changing the value of the max_allowed_packet variable, you should also change the buffer size on the client side if your client program permits it. The default max_allowed_packet value built in to the client library is 1GB, but individual client programs might override this. For example, mysql and mysqldump have defaults of 16MB and 24MB, respectively. They also enable you to change the client-side value by setting max_allowed_packet on the command line or in an option file.The session value of this variable is read only.

补充@20150528：关于这个问题，存在一个Bug，在metalink上可以找到相关文档，ID 1595441.1