Python and Django OperationalError (2006, 'MySQL server has gone away')

Question

Original: I have recently started getting MySQL OperationalErrors from some of my old code and cannot seem to trace back the problem. Since it was working before, I thought it may have been a software update that broke something. I am using python 2.7 with django runfcgi with nginx. Here is my original code:

views.py

DBNAME = "test"
DBIP = "localhost"
DBUSER = "django"
DBPASS = "password"
db = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
cursor = db.cursor()

def list(request):
    statement = "SELECT item from table where selected = 1"
    cursor.execute(statement)
    results = cursor.fetchall()

I have tried the following, but it still does not work:

views.py

class DB:
    conn = None
    DBNAME = "test"
    DBIP = "localhost"
    DBUSER = "django"
    DBPASS = "password"
def connect(self):
    self.conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
def cursor(self):
    try:
        return self.conn.cursor()
    except (AttributeError, MySQLdb.OperationalError):
        self.connect()
        return self.conn.cursor()

db = DB()
cursor = db.cursor()

def list(request):
    cursor = db.cursor()
    statement = "SELECT item from table where selected = 1"
    cursor.execute(statement)
    results = cursor.fetchall()

Currently, my only workaround is to do MySQLdb.connect() in each function that uses mysql. Also I noticed that when using django's manage.py runserver, I would not have this problem while nginx would throw these errors. I doubt that I am timing out with the connection because list() is being called within seconds of starting the server up. Were there any updates to the software I am using that would cause this to break/is there any fix for this?

Edit: I realized that I recently wrote a piece of middle-ware to daemonize a function and this was the cause of the problem. However, I cannot figure out why. Here is the code for the middle-ware

def process_request_handler(sender, **kwargs):
    t = threading.Thread(target=dispatch.execute,
        args=[kwargs['nodes'],kwargs['callback']],
        kwargs={})
    t.setDaemon(True)
    t.start()
    return
process_request.connect(process_request_handler)

Solution 1

As per the MySQL documentation, your error message is raised when the client can't send a question to the server, most likely because the server itself has closed the connection. In the most common case the server will close an idle connection after a (default) of 8 hours. This is configurable on the server side.

The MySQL documentation gives a number of other possible causes which might be worth looking into to see if they fit your situation.

An alternative to calling connect() in every function (which might end up needlessly creating new connections) would be to investigate using the ping() method on the connection object; this tests the connection with the option of attempting an automatic reconnect. I struggled to find some decent documentation for the ping() method online, but the answer to this question might help.

Note, automatically reconnecting can be dangerous when handling transactions as it appears the reconnect causes an implicit rollback (and appears to be the main reason why autoreconnect is not a feature of the MySQLdb implementation).

Solution 2

Sometimes if you see "OperationalError: (2006, 'MySQL server has gone away')", it is because you are issuing a query that is too large. This can happen, for instance, if you're storing your sessions in MySQL, and you're trying to put something really big in the session. To fix the problem, you need to increase the value of the max_allowed_packet setting in MySQL.

The default value is 1048576.

So see the current value for the default, run the following SQL:

select @@max_allowed_packet;

To temporarily set a new value, run the following SQL:

set global max_allowed_packet=10485760;

To fix the problem more permanently, create a /etc/my.cnf file with at least the following:

[mysqld]
max_allowed_packet = 16M

After editing /etc/my.cnf, you'll need to restart MySQL or restart your machine if you don't know how.

Solution 3

This might be due to DB connections getting copied in your child threads from the main thread. I faced the same error when using python's multiprocessing library to spawn different processes. The connection objects are copied between processes during forking and it leads to MySQL OperationalErrors when making DB calls in the child thread.

Here's a good reference to solve this: Django multiprocessing and database connections

Solution 4

Check if you are allowed to create mysql connection object in one thread and then use it in another.

If it's forbidden, use threading.Local for per-thread connections:

class Db(threading.local):
    """ thread-local db object """
    con = None

    def __init__(self, ...options...):
        super(Db, self).__init__()
        self.con = MySQLdb.connect(...options...)

db1 = Db(...)


def test():
    """safe to run from any thread"""
    cursor = db.con.cursor()
    cursor.execute(...)

Solution 5

For me this was happening in debug mode.

So I tried Persistent connections in debug mode, checkout the link: Django - Documentation - Databases - Persistent connections.

In settings:

'default': {
       'ENGINE': 'django.db.backends.mysql',
       'NAME': 'dbname',
       'USER': 'root',
       'PASSWORD': 'root',
       'HOST': 'localhost',
       'PORT': '3306',
       'CONN_MAX_AGE': None
    },

View more solutions