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

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

The application encounters an error message: MySQLNonTransientConnectionException: Got an error writing communication packets. It is related to packet and has never encountered this problem before. The Database error Log does not have any exceptions. What is the problem?

1. Fault
Fault environment:
$ 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

Exception thrown by the Client: MySQLNonTransientConnectionException: Got an error writing communication packets
Database Server exception:
Mysql> flush tables;
ERROR 1160 (08S01): Got an error writing communication packets

-- Because the federated engine is used, the table queried by the client is federated, which is a bug.
-- Directly querying federated is OK. It may be because the returned data volume does not reach the set value, because we only use simple count
Mysql> select count (*) from tadv_gold;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 31525 |
+ ---------- +
1 row in set (0.46 sec)

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

Ii. Analysis and Solution
MySQL monitoring displays the prompt of Slave has max_allowed_packet size less than master. The following describes the problem:
Each slave in a replication topology shoshould 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.

Suggestion:
Investigate why slave SZ-DB-SRV01: 3307 has a max_allowed_packet size of 4 MiB, which is less than master SZ-DB-SRV01: 3306 s 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

-- The above description is inconsistent with the max_allowed_packet parameter between the master and slave nodes. It seems that it has nothing to do with the problem in this article.
-- Because the target table of the federated engine called from this database is not located in the slave database with the packet value too small, but another instance.
-- Even if the slave database is set too small, theoretically the message Size value from the current database federated to another instance should not be affected.
-- The modification was made, because the adjustment to 16 MB does not have much impact on the current hardware.

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 & gt; 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 again.
Mysql> flush tables; -- Author: Leshami
Query OK, 0 rows affected (0.03 sec) -- Blog: http://blog.csdn.net/leshami

3. About the max_allowed_packet Parameter
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 4 MB as of MySQL 5.6.6, 1 MB 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 shoshould be as big as the largest BLOB you want to use. the protocol limit for max_allowed_packet is 1 GB. the value shoshould 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 shoshould 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 1 GB, but individual client programs might override this. for example, mysql and mysqldump have defaults of 16 MB and 24 MB, 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.