When I run large queries (queries returning many rows), I get the
Lost connection to MySQL server during query error, 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:
#!/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()
If I limit the query to result fewer result
SELECT id FROM nrk2013b_tbl WHERE main_news_category = 'Sport' LIMIT 10 all 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.
In order to take connect-timeout, and max_allowed_packet, etc into account, I have this my.cnf-file:
[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.
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).
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()
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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
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.
Switched to PyMySQL and things work as expected.
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.
Try increasing your
net_read_timeout (probably a default value of 30secs is too small in your scenario)
and in general:
I encountered similar problems too. In my case it was solved by getting the cursor in this way:
cur = connection.cursor(buffered=True)
Looks like a bug in MySQL Connector/Python: http://bugs.mysql.com/bug.php?id=74483
Should be fixed in 2.0.3, which is not yet released.
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.