在查询过程中丢失了与 MySQL 服务器的连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6516943/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Lost connection to MySQL server during query?
提问by Shyam Natraj Kanagasabapathy
Possible Duplicate:
Lost connection to MySQL server during query
可能的重复：
查询期间与 MySQL 服务器的连接丢失
I am importing some data from a large csv to a mysql table. I am losing the connection to the server during the process of importing the file to the table.
我正在将一些数据从大型 csv 导入 mysql 表。在将文件导入表的过程中，我失去了与服务器的连接。
What is going wrong?
出了什么问题？
The error code is 2013: Lost connection to the mySql server during the query.
错误代码是 2013: Lost connection to the mySql server during the query.
I am running these queries from a ubuntu machine remotely on a windows server.
我正在 Windows 服务器上从 ubuntu 机器远程运行这些查询。
采纳答案by Shyam Natraj Kanagasabapathy
The easiest solution I found to this problem was to downgrade the MySql from MySQL Workbench to MySQL Version 1.2.17. I had browsed some MySQL Forums, where it was said that the timeout time in MySQL Workbech has been hard coded to 600 and some suggested methods to change it didn't work for me. If someone is facing the same problem with workbench you could try downgrading too.
我发现这个问题的最简单的解决方案是将 MySql 从 MySQL Workbench 降级到 MySQL 版本 1.2.17。我浏览了一些 MySQL 论坛，据说 MySQL Workbech 中的超时时间已硬编码为 600，一些建议的更改方法对我不起作用。如果有人在工作台上遇到同样的问题，您也可以尝试降级。
回答by Ashwin A
Try the following 2 things...
尝试以下两件事...
1) Add this to your my.cnf / my.iniin the [mysqld]section
1）将此添加到您my.cnf / my.ini的[mysqld]部分
max_allowed_packet=32M
(you might have to set this value higher based on your existing database).
（您可能必须根据现有数据库将此值设置得更高）。
2) If the import still does not work, try it like this as well...
2）如果导入仍然不起作用，也可以这样尝试...
mysql -u <user> --password=<password> <database name> <file_to_import
回答by Augusto
Usually that happens when you exhaust one resource for the db session, such as memory, and mysql closes the connection.
通常，当您耗尽数据库会话的一种资源（例如内存）并且 mysql 关闭连接时，就会发生这种情况。
Can you break the CSV file into smaller ones and process them? or do commit every 100 rows? The idea is that the transaction you're running shouldn't try to insert a large amount of data.
你能把 CSV 文件分解成更小的文件并处理它们吗？还是每 100 行提交一次？这个想法是您正在运行的事务不应该尝试插入大量数据。
I forgot to add, this error is related to the configuration property max_allowed_packet, but I can't remember the details of what to change.
忘记补充了，这个错误和配置属性有关max_allowed_packet，具体改什么地方记不清了。
回答by gmhk
1) you may have to increase the timeout on your connection.
1) 您可能需要增加连接的超时时间。
2)You can get more information about the lost connections by starting mysqldwith the --log-warnings=2 option.
2）您可以通过使用 --log-warnings=2 选项启动mysqld来获取有关丢失连接的更多信息。
This logs some of the disconnected errors in the hostname.errfile
这会在hostname.err文件中记录一些断开连接的错误
You can use that for further investigation
您可以将其用于进一步调查
3) if you are trying to send the data to BLOB columns, check server's max_allowed_packetvariable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in following link, “Packet too large”.
3) 如果您尝试将数据发送到 BLOB 列，请检查服务器的max_allowed_packet变量，该变量的默认值为 1MB。您可能还需要增加客户端的最大数据包大小。有关设置数据包大小的更多信息，请参见以下链接“数据包太大”。
4) you can check the following url link
4）您可以检查以下网址链接
5) you should check your available disk space is bigger than the table you're trying to update link
5）您应该检查您的可用磁盘空间是否大于您尝试更新链接的表
回答by Abhay
You might like to read this - http://dev.mysql.com/doc/refman/5.0/en/gone-away.html- that very well explains the reasons and fixes for "lost connection during query" scenarios.
您可能喜欢阅读此内容 - http://dev.mysql.com/doc/refman/5.0/en/gone-away.html- 很好地解释了“查询期间丢失连接”场景的原因和修复方法。
In your case, it might be because of the max allowed packet sizeas pointed by Augusto. Or if you've verified it isn't the case, then it might be the connection wait timeoutsetting due to which the client is losing connection. However, I do not think latter is true here because it's a CSV file and not containing queries.
在您的情况下，这可能是因为Augusto 指出的最大允许数据包大小。或者，如果您已验证情况并非如此，则可能是由于客户端丢失连接而导致的连接等待超时设置。但是，我认为后者在这里不正确，因为它是一个 CSV 文件并且不包含查询。
回答by vkGunasekaran
I think you can use mysql_ping()function.
我认为您可以使用mysql_ping()函数。
This function checks for connection to the server alive or not. if it fails then you can reconnect and proceed with your query.
此函数检查与服务器的连接是否存在。如果失败，那么您可以重新连接并继续您的查询。
