MySQL ~ Error Code: 2013. Lost connection to MySQL server during query。

MySQL ~ Error Code: 2013. Lost connection to MySQL server during query。

---

---

使用 MySQL Workbench 向数据库插入大量数据，如 300W 条，报出错误。

Error Code: 2013. Lost connection to MySQL server during query

解决。

• 参考官方文档。

https://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html

MySQL 5.7 Reference Manual / … / Lost connection to MySQL server
B.3.2.3 Lost connection to MySQL server
There are three likely causes for this error message.
　
Usually it indicates network connectivity trouble and you should check the condition of your network if this error occurs frequently. If the error message includes “during query,” this is probably the case you are experiencing.
　
Sometimes the “during query” form happens when millions of rows are being sent as part of one or more queries. If you know that this is happening, you should try increasing net_read_timeout from its default of 30 seconds to 60 seconds or longer, sufficient for the data transfer to complete.
　
More rarely, it can happen when the client is attempting the initial connection to the server. In this case, if your connect_timeout value is set to only a few seconds, you may be able to resolve the problem by increasing it to ten seconds, perhaps more if you have a very long distance or slow connection. You can determine whether you are experiencing this more uncommon cause by using SHOW GLOBAL STATUS LIKE ‘Aborted_connects’. It increases by one for each initial connection attempt that the server aborts. You may see “reading authorization packet” as part of the error message; if so, that also suggests that this is the solution that you need.
　
If the cause is none of those just described, you may be experiencing a problem with BLOB values that are larger than max_allowed_packet, which can cause this error with some clients. Sometime you may see an ER_NET_PACKET_TOO_LARGE error, and that confirms that you need to increase max_allowed_packet.
　
　
PREV HOME UP NEXT

• 修改 MySQL Server 参数。

在 my.ini（Windows）、my.cnf（Linux）配置文件 mysqld 节点下添加

max_allowed_packet = 500M

或更高。

• MySQL Workbench 对大表执行复杂操作时报错如下

Error Code: 2013. Lost connection to MySQL server during query

解决办法：

Edit -> Preference -> SQL Editor

将下图 DBMS connection read time out(in seconds) 适当调大。

或改为 0（）。

执行成功。
467.797 sec