MySQL莫名的MySQL server has gone away

windows恢复的时候导入数据,其实sql文件也不大才67m

C:/Users/zhwq>mysql -u root -p Enter password: *******
ERROR 2006 (HY000) at line 11379: MySQL server has gone away

拿到自己的Linux下试了下,却又提示

[root@zhwq ~]# mysql -uroot
ERROR 1153 (08S01) at line 11379: Got a packet bigger than 'max_allowed_packet' bytes

通过修改'max_allowed_packet' 参数,却又可以执行

再回到win下修改'max_allowed_packet' 变量也可以了,不再报"MySQL server has gone away"错误了

奇怪于是认真再去翻阅了下'max_allowed_packet' 参数的说明

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 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.

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 allows it. On the client side, max_allowed_packet has a default of 1GB. Some programs such as mysql and mysqldump enable you to change the client-side value by setting max_allowed_packet on the command line or in an option file.

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

明白了,应该是sql文件中有一个比较长的字符串,而'max_allowed_packet'则定义了MySQLserver和客户端之间通信的单个数据最大值,超过了这个值所以导致了网络连接失败,从而报出了MySQL server has gone away错误!

值得注意的是,在MySQL Replication中同样是一个客户端和一个服务器之间的通信,所以同样也要设置好这个值,尤其在row模式下的Replication,每次跟新都传递的是每个记录所有的值,这将导致传递的数据很大,所以需要关注这个系统变量.MySQL手册也提到将来会改变这个模式,将之传递修改的列.全文如下

max_allowed_packet 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.

Row-based replication currently sends all columns and column values for updated rows from the master to the slave, including values of columns that were not actually changed by the update. This means that, when you are replicating large column values using row-based replication, you must take care to set max_allowed_packet large enough to accommodate the largest row in any table to be replicated, even if you are replicating updates only, or you are inserting only relatively small values.

We are working to make it possible in a future MySQL release to send the changed columns only.