当我运行大型查询(返回许多行的查询)时,会出现Lost connection to MySQL server during query
错误,并且看不到我做错了什么。我使用mysql.com上的“new”mysql驱动程序(不是“old”MySQLdb),以及与MAMP捆绑在一起的mysql版本。Python2.7。表未损坏,analyze table nrk2013b_tbl;
返回status ok。下面是一个例子:
#!/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)
这会导致我在大多数情况下得到的错误:
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
第20行是rows = cur.fetchall()
如果我将查询限制为结果较少的结果SELECT id FROM nrk2013b_tbl WHERE main_news_category = 'Sport' LIMIT 10
,一切都很好。但我确实想用更大的结果集。对于一些特殊的问题解决,我已经移动了限制,并将我想要的数据分解成更小的批,但这一直是一个问题。
为了考虑连接超时、最大允许包数等因素,我有这个my.cnf文件:File: /Applications/MAMP/conf/my.cnf
[mysqld]
max_allowed_packet = 64M
wait_timeout = 28800
interactive_timeout = 28800
connect-timeout=31536000
这似乎没有任何区别(我甚至不确定mysql是否识别这些设置)。当我从终端或Sequel Pro运行查询时,它工作正常。只有通过python 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) # this I
cur = conn.cursor()
我还不得不切换到PyMySQL。我正在运行pip 1.5.6、Python 2.7.8,并尝试mysql connector 2.0.1
我可以在Sequel Pro中运行查询,没有问题,但是我的Python查询只返回结果的一个子集,就会失败,出现问题中描述的错误。
切换到PyMySQL,一切正常。
https://github.com/PyMySQL/PyMySQL
在虚拟世界里:
在代码中:
mysql连接器python中肯定有一个bug。
相关问题 更多 >
编程相关推荐