Python 为什么 mysql 连接器中断（“在查询过程中丢失与 MySQL 服务器的连接”错误）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26510114/
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
Why does mysql connector break ("Lost connection to MySQL server during query" error)
提问by Eiriks
When I run large queries (queries returning many rows), I get the Lost connection to MySQL server during queryerror, and I cannot see what I do wrong. I use the "new" mysql driver from mysql.com (not the "old" MySQLdb), and the mysql version that is bundled with MAMP. Python 2.7. Table is not corrupted, analyze table nrk2013b_tbl;returns status ok. Here's an example that breaks:
当我运行大型查询（查询返回多行）时，我收到 Lost connection to MySQL server during query错误消息，并且看不到我做错了什么。我使用来自 mysql.com 的“新”mysql 驱动程序（不是“旧”MySQLdb），以及与 MAMP 捆绑的 mysql 版本。蟒蛇 2.7。表未损坏，analyze table nrk2013b_tbl;返回状态正常。这是一个中断的示例：
#!/usr/bin/python2.7
# coding: utf-8
import sys
import mysql.connector # version 2.0.1
connection = mysql.connector.connect(
unix_socket="/Applications/MAMP/tmp/mysql/mysql.sock",
user="dbUsernam",
passwd="dbUserPassword",
db="nrk",
charset = "utf8",
use_unicode = True)
cur = connection.cursor()
cur.execute("USE nrk;")
sql = """SELECT id FROM nrk2013b_tbl WHERE main_news_category = 'Sport'"""
cur.execute(sql)
rows = cur.fetchall()
print rows
sys.exit(0)
This results in the error I get most of the time:
这导致我大部分时间得到的错误：
Traceback (most recent call last):
File "train_trainer_test.py", line 20, in <module>
remaining_rows = cur.fetchall()
File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 823, in fetchall
(rows, eof) = self._connection.get_rows()
File "/Library/Python/2.7/site-packages/mysql/connector/connection.py", line 669, in get_rows
rows = self._protocol.read_text_result(self._socket, count)
File "/Library/Python/2.7/site-packages/mysql/connector/protocol.py", line 309, in read_text_result
packet = sock.recv()
File "/Library/Python/2.7/site-packages/mysql/connector/network.py", line 226, in recv_plain
raise errors.InterfaceError(errno=2013)
mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query
Line 20 is the rows = cur.fetchall()
第 20 行是 rows = cur.fetchall()
If I limit the query to result fewer result SELECT id FROM nrk2013b_tbl WHERE main_news_category = 'Sport' LIMIT 10all is well. But I do want to work with larger result sets. For some ad-hoc problem solving I have moved the limit and broken down the data I wanted into smaller batches, but this keeps popping up as a problem.
如果我将查询限制为产生更少的结果，SELECT id FROM nrk2013b_tbl WHERE main_news_category = 'Sport' LIMIT 10一切都很好。但我确实想处理更大的结果集。对于一些临时解决问题，我已经移动了限制并将我想要的数据分解成更小的批次，但这一直作为一个问题出现。
In order to take connect-timeout, and max_allowed_packet, etc into account, I have this my.cnf-file: File: /Applications/MAMP/conf/my.cnf
为了考虑连接超时和 max_allowed_packet 等，我有这个 my.cnf 文件： File: /Applications/MAMP/conf/my.cnf
[mysqld]
max_allowed_packet = 64M
wait_timeout = 28800
interactive_timeout = 28800
connect-timeout=31536000
This does not seem to make any difference (I'm not even sure if mysql recognises these settings). When I run queries from the terminal or from Sequel Pro, it works fine. It is only through the python mysql.connector I get these errors.
这似乎没有任何区别（我什至不确定 mysql 是否识别这些设置）。当我从终端或 Sequel Pro 运行查询时，它工作正常。只有通过 python mysql.connector 我才会收到这些错误。
Any ideas?
有任何想法吗？
PS: I've temporarily given this up, and changed to PyMySQL instead of of the Oracle mysql.connector. By changing to this, the problems seems to disappear (and I conclude for myself that the problem is in the oracle mysql connector).
PS：我暂时放弃了，改用PyMySQL代替Oracle mysql.connector。通过更改为这个，问题似乎消失了（我自己断定问题出在 oracle mysql 连接器中）。
import pymysql
conn = pymysql.connect(
unix_socket="/Applications/MAMP/tmp/mysql/mysql.sock",
user="dbUsernam",
passwd="dbUserPassword",
db="nrk",
charset = "utf8",
use_unicode = True)
conn.autocommit(True)
cur = conn.cursor()
采纳答案by sheldonkreger
I also had to switch to PyMySQL. I am running pip 1.5.6, Python 2.7.8, and tried mysql-connector 2.0.1
我还不得不切换到 PyMySQL。我正在运行 pip 1.5.6、Python 2.7.8，并尝试了 mysql-connector 2.0.1
I was able to run the query from within Sequel Pro with no problems, but my Python query would fail with the error described in the question after returning just a subset of results.
我能够在 Sequel Pro 中毫无问题地运行查询，但我的 Python 查询将失败，并在返回结果的一个子集后出现问题中描述的错误。
Switched to PyMySQL and things work as expected.
切换到 PyMySQL，事情按预期工作。
https://github.com/PyMySQL/PyMySQL
https://github.com/PyMySQL/PyMySQL
In the virtualenv:
在虚拟环境中：
pip install pymysql
In the code:
在代码中：
import pymysql
connection = pymysql.connect(user='x', passwd='x',
host='x',
database='x')
cursor = connection.cursor()
query = ("MYQUERY")
cursor.execute(query)
for item in cursor:
print item
Definitely a bug in mysql-connector-python.
绝对是 mysql-connector-python 中的一个错误。
回答by Cristian Porta
Try increasing your net_read_timeout(probably a default value of 30secs is too small in your scenario)
尝试增加您的net_read_timeout（可能在您的场景中默认值 30secs 太小）
Ref:
参考：
and in general:
一般来说：
回答by Che
Looks like a bug in MySQL Connector/Python: http://bugs.mysql.com/bug.php?id=74483
看起来像 MySQL 连接器/Python 中的错误：http: //bugs.mysql.com/bug.php?id=74483
Should be fixed in 2.0.3, which is not yet released.
应该在尚未发布的2.0.3中修复。
回答by codebard
Expanding on Christian's answer. Timeout for read queries (select) are set by net_write_timeout. It is a "write" from the perspective of the server.
扩展基督教的答案。读取查询（选择）的超时由net_write_timeout设置。从服务器的角度来看，它是一种“写入”。
回答by user6938211
I encountered similar problems too. In my case it was solved by getting the cursor in this way:
我也遇到过类似的问题。在我的情况下，它是通过以这种方式获取光标来解决的：
cur = connection.cursor(buffered=True)
