Django, after upgrade: MySQL server has gone away

Question

I recently upgraded from Django 1.4 to Django 1.7 and since I keep getting the following error message for some scripts, sometimes:

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

The scripts are very long or continuously running tasks that might involve phases of not communicating with the db for several minutes, so the connection times out. However, before I upgraded, that was no problem, as Django seemed to automatically re-establish a connection. Now it doesn't which means the tasks often stop and fail in the middle.

Does anyone know what has changed and how I can fix it?

Is it perhaps related to that ticket/fix: https://code.djangoproject.com/ticket/21463

Thanks a lot!

Answer 1

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

Answer 2

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.