mysql maxallowedpacket maximum,MySQL 错误 “MySQL server has gone away” 和 max_allowed_packet 配置...

出错现象：

今天使用MySQL命令从备份文件还原数据库时， 提示“MySQL server has gone away” 错误。

原因：

MySQL对处理的数据的大小有限制， MySQL服务端和客户端都通过变量max_allowed_packet来控制，如果我们要处理比较大的数据时，需要自己调整变量max_allowed_packet的大小。

我的解决办法：

打开MySQL安装目录下的my.ini配置文件，修改[mysqld]的配置，[mysqld]下面默认没有max_allowed_packet配置项，于是添加一行配置信息，(注意：不要加错位置)：max_allowed_packet  = 500M

附录：

a) 下面是MySQL5.1用户手册中MySQL Server Administration目录中对 max_allowed_packet 的说明(可以到手册中查找， 手册默认在MySQL安装目录MySQL\MySQL Server 5.1\Docs下面)

Option Sets Variable

Yes

Variable Name

max_allowed_packet

Variable Scope

Both

Dynamic Variable

Yes

Value Set

Type

numeric

Default

1048576

Range

1024  --  1073741824

The maximum size of one packet or any generated/intermediate string.

The packet message buffer is initialized to net_buffer_length bytes, but can grow up to  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 and TEXT Types columns or long strings. It should be as big as the largest BLOB and TEXT Types you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; non-multiples are rounded down to the nearest multiple.

As of MySQL 5.1.31, the session value of this variable is read only. Before 5.1.31, setting the session value is allowed but has no effect.

b) 下面是MySQL5.1用户手册中MySQL Programs \ MySQL Client Programs目录中对max_allowed_packet的说明：

The maximum packet length to send to or receive from the server. (Default value is 16MB.)

(By jiarong_cheng 2012 )