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

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

    應用程式端碰到錯誤提示，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.