python - Why does mysql connector break ("Lost connection to MySQL server during query" error) -

python - Why does mysql connector break ("Lost connection to MySQL server during query" error) -

when run big queries (queries returning many rows), lost connection mysql server during query error, , cannot see wrong. utilize "new" mysql driver mysql.com (not "old" mysqldb), , mysql version bundled mamp. python 2.7. table not corrupted, analyze table nrk2013b_tbl; returns status ok. here's illustration breaks:

#!/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 nrk2013b_tbl main_news_category = 'sport'""" cur.execute(sql) rows = cur.fetchall() print rows sys.exit(0)

this results in error of time:

traceback (most recent phone 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 mysql server during query

line 20 rows = cur.fetchall()

if limit query result fewer result select id nrk2013b_tbl main_news_category = 'sport' limit 10 well. want work larger result sets. ad-hoc problem solving have moved limit , broken downwards info wanted smaller batches, keeps popping problem.

in order take connect-timeout, , max_allowed_packet, etc account, have my.cnf-file: file: /applications/mamp/conf/my.cnf

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

this not seem create difference (i'm not sure if mysql recognises these settings). when run queries terminal or sequel pro, works fine. through python mysql.connector these errors.

any ideas?

ps: i've temporarily given up, , changed pymysql instead of of oracle mysql.connector. changing this, problems seems disappear (and conclude myself problem in oracle mysql connector).

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

i had switch pymysql. running pip 1.5.6, python 2.7.8, , tried mysql-connector 2.0.1

i able run query within sequel pro no problems, python query fail error described in question after returning subset of results.

switched pymysql , things work expected.

https://github.com/pymysql/pymysql

in virtualenv:

pip install pymysql

in code:

import pymysql connection = pymysql.connect(user='x', passwd='x', host='x', database='x') cursor = connection.cursor() query = ("myquery") cursor.execute(query) item in cursor: print item

definitely bug in mysql-connector-python.

python mysql mamp