Why am I getting the "MySQL server has gone away" exception in Django?

Question

I'm working with Django 2.2.6.

The same system that runs my django project also has a background service running, listening on a unix socket for requests. In Django Admin, if a user hits a button, Django sends a request on the unix socket, and the background service does something.

My background service has full access to Django's ORM. It imports models from my project's models.py, and can query the database without any issues.

The problem is that if I leave my django, and my background service running overnight, login to Django Admin, and hit the button, my background service throws an exception:

django.db.utils.OperationalError: (2006, 'MySQL server has gone away')

It appears that this is because the MySQL database has a timeout period, called wait_timeout. If a connection to the database isn't active for that long, MySQL will disconnect it. Unfortunately, Django doesn't notice, and tries to use it, throwing the error.

Fortunately, Django has its own built-in CONN_MAX_AGE variable for each database defined in settings.py. If a database connection is older than the CONN_MAX_AGE, it shuts it down before a request and starts up a new one.

Looking at my MySQL database:

> show session variables where variable_name = "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

Looking at my Django's CONN_MAX_AGE variable:

# ./manage.py shell
>>> from django.conf import settings
>>> settings.DATABASES['default']['CONN_MAX_AGE']
0

Note: the 'default' database is the only one I have defined in my settings.py

Also note that both my MySQL wait_timeout, and my Django's CONN_MAX_AGE are default values - I haven't changed them.

According to the Django docs here, a CONN_MAX_AGE value of 0 means:

close database connections at the end of each request

If django is meant to close the database connection after every request, why than am I running into this error? Why isn't it closing old connections the second I'm done running my query, and starting a new connection when I do a new query, hours later?

Edit:

Right now, my solution is to have my background service do a heartbeat. Once per hour seems to work fine. The heartbeat is just a simple, low-resource-consumption MySQL command like MyDjangoModel.objects.exists(). As long as it does a MySQL query to refresh the MySQL timeout, it works. Having this does add some complexity to my background service, as in one case, my otherwise single-threaded background service needed a background thread whose only job was to do heartbeats.

If there's a simpler solution to this, or at least an explanation for why this happens, I'd like to hear it.

Answer 1

I had exactly the same issue than yours. I implemented a monitoring script using watchdogs library, and, by the end of "wait_timeout", MySQL error would be raised.

After a few tries with "django.db.close_old_connections()" function, it still did not work, but I was attempting to close old connections every defined time interval, which was not working. I changed the close command to run only before the call of my custom management command (which is the command that will interact with db and used to crash with MySQL error) and it started to work.

Apparently from this page, the reason why that happen is because the "close_old_connection" function is linked only to HTTP request signals, so it will not be fired in specific custom scripts. The documentation of Django doesn't tell that, and I honestly also understood things the same way you were understanding.

So, what you can try to do is to add the call to close old connection before interacting with db:

from django.db import close_old_connections
close_old_connections()
do_something_with_db()