【mysql案例】导入报错-ERROR 2013 (HY000)

【现象】 导入MySQL的mysqldump文件时报错，提示ERROR 2013 (HY000) at line xxoo: Lost connection to MySQL serverduring query，导入操作中断退出。 【原因】 要导入的mysqldump文件中insert的values太多，超过了MySQL参数max_allowed_packet的，进而导致

【现象】

导入MySQL的mysqldump文件时报错，提示ERROR 2013 (HY000) at line xxoo: Lost connection to MySQL serverduring query，导入操作中断退出。

【原因】

要导入的mysqldump文件中insert的values值太多，超过了MySQL参数max_allowed_packet的值，进而导致导入操作中断退出。

【解决方法】

调高max_allowed_packet的值：

SQL> set global max_allowed_packet=67108864;

【参考资料】

max_allowed_packet值的范围是1024 ..1073741824，单位是字节。

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

You must increase this value if you areusing large BLOB columnsor long strings. It should be as big as the largest BLOB you want touse. The protocol limit for max_allowed_packet is 1GB. The value should be amultiple of 1024; nonmultiples are rounded down to the nearest multiple.

 

Whenyou change the message buffer size by changing the value of themax_allowed_packet variable, you should also change the buffer size on theclient side if your client program permits it. The default max_allowed_packetvalue built in to the client library is 1GB, but individual client programsmight override this. For example, mysql and mysqldump have defaults of 16MB and24MB, respectively. They also enable you to change the client-side value bysetting

【后续】可以测试一下 long strings 和 BLOB column