Django - OperationalError: (2006, 'MySQL server has gone away')

Question

Bottom line first: How do you refresh the MySQL connection in django?

Following a MySQL server has gone away error I found that MySQL documentation and other sources (here) suggest increasing the wait_timeout MySQL parameter. To me this seems like a workaround rather than a solution. I'd rather keep a reasonable wait_timeout and refresh the connection in the code.

The error:

  File "C:\my_proj\db_conduit.py", line 147, in load_some_model
    SomeModel.objects.update()
  File "C:\Python26\lib\site-packages\django-1.3-py2.6.egg\django\db\models\manager.py", line 177, in update
    return self.get_query_set().update(*args, **kwargs)
  File "C:\Python26\lib\site-packages\django-1.3-py2.6.egg\django\db\models\query.py", line 469, in update
    transaction.commit(using=self.db)
  File "C:\Python26\lib\site-packages\django-1.3-py2.6.egg\django\db\transaction.py", line 142, in commit
    connection.commit()
  File "C:\Python26\lib\site-packages\django-1.3-py2.6.egg\django\db\backends\__init__.py", line 201, in commit
    self._commit()
  File "C:\Python26\lib\site-packages\django-1.3-py2.6.egg\django\db\backends\__init__.py", line 46, in _commit
    return self.connection.commit()
OperationalError: (2006, 'MySQL server has gone away')

Setup: Django 1.3.0 , MySQL 5.5.14 , innodb 1.1.8 , Python 2.6.6, Win7 64bit

Answer 1

The idea of the solution is clear: reconnect to mysql if the current connection is broken.

Please check this out:

def make_sure_mysql_usable():
    from django.db import connection, connections
    # mysql is lazily connected to in django.
    # connection.connection is None means
    # you have not connected to mysql before
    if connection.connection and not connection.is_usable():
        # destroy the default mysql connection
        # after this line, when you use ORM methods
        # django will reconnect to the default mysql
        del connections._connections.default

Answer 2

having the same issue. I need idea how to check connection state for MySQLdb connection in django. i guess it can be achieved by

try:
    cursor.execute(sql)
catch OperationalError:
    reconnect

is anybody have a better idea?

UPDATE

my decision

self.connection.stat()
if self.connection.errno()!=0:

check state of mysqldb connection if error recreate connection

UPDATE AGAIN

you also need to serve case if connection is close

if self.connection.open:
    self.connection.stat()

refresh connection is just recreating it

    db_settings = settings.DATABASES['mysql_db']
    try:
        self.connection = MySQLdb.connect(host=db_settings['HOST'],port=int(db_settings['PORT']),db=db_settings['NAME'],user=db_settings['USER'],passwd=db_settings['PASSWORD'])
    except MySQLdb.OperationalError, e:
        self.connection = None

Answer 3

Since Django 1.6, you can use

import django.db

django.db.close_old_connections()

This does basically the same thing as adamsmith's answer except that it handles multiple databases and also honors the CONN_MAX_AGE setting. Django calls close_old_connections() automatically before and after each request, so you normally don't have to worry about it unless you have some long-running code outside of the normal request/response cycle.

Answer 4

The main reason that leads to this exception is mostly due to client ideal longer than wait_timeout on mysql server.

In order to prevent that kind of error, django supports an option named CONN_MAX_AGE which allow django to recreate new connection if old connections are ideal too long. So you should make sure that CONN_MAX_AGE value is smaller than wait_timout value.

One important thing is that, django with wsgi handles checking CONN_MAX_AGE every requests by calling close_old_connections. So you mainly don't need to care about that. However if you are using django in standard alone application, there is no trigger to run that function. So you have to call it manually. So let call close_old_connections in your code base.

Note: close_old_connections will keep old connections if they're not expired yet. Your connections are still reused in case of high frequency query.

Answer 5

This way can also close the idle connections and make things good.

So before you need to make a query after a long time, running the below lines will work:

from django.db import close_old_connections

# To prevent the error if possible.
close_old_connections()
# Then the following sentence should be always ok.
YourModel.objects.all()

Answer 6

If you need Django to auto-refresh database connection (for Django >= 4.1) you can enable persistent connections with health-checks

DATABASES = {
   'default': {
       'CONN_HEALTH_CHECKS': True,
       'CONN_MAX_AGE': 60,  # <= Mysql `interactive_timeout`/`wait_timeout`  
   }
}

As result for each db request Django will ping database to check "if connection is alive" (DatabaseWrapper.is_usable) and restart connection if needed