Django, after upgrade: MySQL server has gone away

I have a running background process rqworker which executes separate jobs to refresh some data after some user actions.

I always get OperationalError: (2006, 'MySQL server has gone away') if there were no user action during more than wait_timeout seconds. Even if I set CONN_MAX_AGE less than MySQL wait_timeout.

As I understand, changing of CONN_MAX_AGE could help if Django checked and close its connections automatically by this timeout. But Django 1.7.x checks it before and after each request only (see django/db/init.py#L101-L112 ).

As described in Django ticket 15119, we can see that Django was making a ping to validate if the connection was alive before executing every query. This behavior was fixed in commit 282b2f4.

Django developers gave one short answer for all questions like this in https://code.djangoproject.com/ticket/21597#comment:29

Therefore my rqworker process has to validate connection itself for each new job. (Note: if we close a connection then Django will create a new one).

I’m going to use Django per request approach for jobs and call django.db.close_old_connections() before and after every job. And yes, CONN_MAX_AGE should be less than MySQL wait_timeout, because Django doesn't check if MySQL server has gone away in django.db.close_old_connections() method.

On Django 1.9: I had an ongoing Django Shell running in a unix screen left unattended for over 48 hours. When I returned back to it and ran a <some_model>.objects.filter it threw OperationalError: (2006, 'MySQL server has gone away')

A quick import django.db; django.db.close_old_connections() did the trick for me.

I couldn't find documentation for close_old_connections() on Django Docs for 1.9 however here's the direct link to its implementation in Django Codebase on Github

The reason of such behavior is persistent connect to database, which was introduced in Django 1.6.

To prevent connection timeout error you should set CONN_MAX_AGE in settings.py to value which is less than wait_timeout in MySQL config (my.cnf). In that case Django detects that connection need to be reopen earlier than MySQL throws it. Default value for MySQL 5.7 is 28800 seconds.

settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'CONN_MAX_AGE': 3600,
        <other params here>
    }
}

Documentation: https://docs.djangoproject.com/en/1.7/ref/settings/#conn-max-age

my.cnf:

wait_timeout = 28800

Documentation: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout

In django 1.6, when the wait_timeout passed (of mysql), then DB access cause the (2006, 'MySQL server has gone away') error. This was not the case in django 1.5.1

I've noticed this error when using workers that run the django code (using gearman).

To reproduce:

Set the timeout to low value by editing /etc/mysql/my.cnf add the following under [mysqld]

wait_timeout = 10
interactive_timeout = 10

Then

% python manage.py shell

>>> # access DB 
>>> import django.contrib.auth.models
>>> print list(django.contrib.auth.models.User.objects.all())
>>> import time
>>> time.sleep(15)
>>> print list(django.contrib.auth.models.User.objects.all())

Now you get the error.

Simple solution I found on the web is to call django.db.close_connection() before the access

>>> import django.db
>>> django.db.close_connection()
>>> print list(django.contrib.auth.models.User.objects.all())

works ok.