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

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.

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

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
    },

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

I’ve been struggling with this issue too. I don’t like the idea of increasing timeout on mysqlserver. Autoreconnect with CONNECTION_MAX_AGE doesn’t work either as it was mentioned. Unfortunately I ended up with wrapping every method that queries the database like this

def do_db( callback, *arg, **args):
    try:
        return callback(*arg, **args)
    except (OperationalError, InterfaceError) as e:  # Connection has gone away, fiter it with message or error code if you could catch another errors
        connection.close()
        return callback(*arg, **args)

do_db(User.objects.get, id=123)  # instead of User.objects.get(id=123)

As you can see I rather prefer catching the exception than pinging the database every time before querying it. Because catching an exception is a rare case. I would expect django to reconnect automatically but they seemed to refused that issue.

The most common issue regarding such warning, is the fact that your application has reached the wait_timeout value of MySQL.

I had the same problem with a Flask app.

Here’s how I solved:

$ grep timeout /etc/mysql/mysql.conf.d/mysqld.cnf 
# https://support.rackspace.com/how-to/how-to-change-the-mysql-timeout-on-a-server/
# wait = timeout for application session (tdm)
# inteactive = timeout for keyboard session (terminal)
# 7 days = 604800s / 4 hours = 14400s 
wait_timeout = 604800
interactive_timeout = 14400

Observation: if you search for the variables via MySQL batch mode, the values will appear as it is. But If you perform SHOW VARIABLES LIKE 'wait%'; or SHOW VARIABLES LIKE 'interactive%';, the value configured for interactive_timeout, will appear to both variables, and I don’t know why, but the fact is, that the values configured for each variable at ‘/etc/mysql/mysql.conf.d/mysqld.cnf’, will be respected by MySQL.

This error is mysterious because MySQL doesn’t report why it disconnects, it just goes away.

It seems there are many causes of this kind of disconnection. One I just found is, if the query string too large, the server will disconnect. This probably relates to the max_allowed_packets setting.

This error may occur when you try to use the connection after a time-consuming operation that doesn’t go to the database. Since the connection is not used for some time, MySQL timeout is hit and the connection is silently dropped.

You can try calling close_old_connections() after the time-consuming non-DB operation so that a new connection is opened if the connection is unusable. Beware, do not use close_old_connections() if you have a transaction.

How old is this code? Django has had databases defined in settings since at least .96. Only other thing I can think of is multi-db support, which changed things a bit, but even that was 1.1 or 1.2.

Even if you need a special DB for certain views, I think you’d probably be better off defining it in settings.

SQLAlchemy now has a great write-up on how you can use pinging to be pessimistic about your connection’s freshness:

http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic

From there,

from sqlalchemy import exc
from sqlalchemy import event
from sqlalchemy.pool import Pool

@event.listens_for(Pool, "checkout")
def ping_connection(dbapi_connection, connection_record, connection_proxy):
    cursor = dbapi_connection.cursor()
    try:
        cursor.execute("SELECT 1")
    except:
        # optional - dispose the whole pool
        # instead of invalidating one at a time
        # connection_proxy._pool.dispose()

        # raise DisconnectionError - pool will try
        # connecting again up to three times before raising.
        raise exc.DisconnectionError()
    cursor.close()

And a test to make sure the above works:

from sqlalchemy import create_engine
e = create_engine("mysql://scott:tiger@localhost/test", echo_pool=True)
c1 = e.connect()
c2 = e.connect()
c3 = e.connect()
c1.close()
c2.close()
c3.close()

# pool size is now three.

print "Restart the server"
raw_input()

for i in xrange(10):
    c = e.connect()
    print c.execute("select 1").fetchall()
    c.close()

I had this problem and did not have the option to change my configuration. I finally figured out that the problem was occurring 49500 records in to my 50000-record loop, because that was the about the time I was trying again (after having tried a long time ago) to hit my second database.

So I changed my code so that every few thousand records, I touched the second database again (with a count() of a very small table), and that fixed it. No doubt “ping” or some other means of touching the database would work, as well.

Firstly, You should make sure the MySQL session & global enviroments wait_timeout and interactive_timeout values. And secondly Your client should try to reconnect to the server below those enviroments values.