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的值，進而導致匯入操作中斷退出。

【解決方案】

調高max_allowed_packet的值：

SQL> set global max_allowed_packet=67108864;

【參考資料】

max_allowed_packet值的範圍是1024 ..1073741824，單位是位元組。

本欄目更多精彩內容：http://www.bianceng.cn/database/MySQL/

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