Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

python

mysql

mamp

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: 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.

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

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()
like image 746
Eiriks Avatar asked Oct 22 '14 14:10

Eiriks


People also ask

How do I fix the lost connection to MySQL server during query?

Open the MySQL Workbench Preferences. Check if the SSH Timeout and DBMS Timeout value is set to only a few seconds. Try to increase the default value of the connection timeouts. Save the settings, close the MySQL Workbench and reopen the connection to see if you are able to connect to the database.

Why does MySQL keep losing connection?

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection. By default, the server closes the connection after 8 hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld.

How do I fix error code 2013 lost connection to MySQL server during query?

Go to Workbench Edit → Preferences → SQL Editor → DBMS connections read time out : Up to 3000. The error no longer occurred. Save this answer.


2 Answers

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.

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.

like image 138
sheldonkreger Avatar answered Sep 21 '22 11:09

sheldonkreger


Try increasing your net_read_timeout (probably a default value of 30secs is too small in your scenario)

Ref:

net_read_timeout

and in general:

B.5.2.3 Lost connection to MySQL server

like image 38
Cristian Porta Avatar answered Sep 19 '22 11:09

Cristian Porta