mysql packet size_关于mysql的服务端参数max_allowed_packet

1、有次用mysqldump备份出的东西导入数据，报错如下：

mysqldump MySQL server has gone away

当时吓一跳，还以为mysql挂了，其实mysql并没有问题，是一个参数max_allowed_packet在作怪，这个参数在mysql5.6的官方解释如下：

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.

The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

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.

主要意思是说如果表字段里面有blob字段或者长字符串，可能会报这个错误。我们导入的表，其实字段不算大，观察报错行对应的内容是一个类似下面的sql：insert into t_xxx values('xxxx','xxxx',...),('yyyy','yyyy',...),('zzzz','zzzz',...)...这是一种mysql的特殊用法，可以在单条insert语句里插入多条记录(在oracle里没法这么用)，但这就很容易导致即使表字段不大，这条insert语句却很长，超过了服务端设定的最大分组大小(max_allowed_packet)，这个参数的默认值是4M。

知道原因后，解决就简单了：

SET GLOBAL max_allowed_packet = 64M

在/etc/my.cnf设定max_allowed_packet = 64M，持久化改变。

2、如果max_allowed_packet过小，在应用里面(比如jdbc)会有类似下面的报错：

270692 [http-8080-3] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: S1000

270692 [http-8080-3] ERROR org.hibernate.util.JDBCExceptionReporter - Packet for query is too large (1063 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.

这里面显示查询分组包的大小1063字节大于db服务器设定的1024字节。