为什么mysql连接器中断（“查询期间与mysql服务器的连接丢失”错误）

Question

当我运行大型查询（返回许多行的查询）时，我会得到在查询期间失去与MySQL server的连接的错误，而且我看不出我做错了什么。我使用来自mysql.com的“新”mysql驱动程序（而不是“旧”的MySQLdb)，以及与mamp捆绑在一起的mysql版本。Python 2.7。表未损坏，分析表NRK2013B_TBL；返回状态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行是行=cur.fetchall()

[mysqld]
max_allowed_packet = 64M
wait_timeout = 28800
interactive_timeout = 28800
connect-timeout=31536000

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()

Answer 1

我还不得不切换到PyMySQL。我运行的是pip 1.5.6、Python 2.7.8，并尝试了mysql-connector 2.0.1

我能够在Sequel Pro中毫无问题地运行查询，但是我的Python查询在只返回一个结果子集后会出现问题中描述的错误。

切换到PyMySQL，一切按预期工作。

pip install pymysql

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