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 use two ways to avoid this problem: The thorough way: Close all the django connections after each activity. Example:

from django.db import connection
    
something.save()
connection.close()

The quick (and dirty) way: Just modify the django model access where the program crashes (the first one after long inactivity). Example:

from django.db import connection
from django.db.utils import OperationalError  

while True:
  try:
    something.save()
    break
  except OperationalError:
    connection.close()

I was still struggling with this. Finaly: I made a general and convenient solution for my toolbox:

def protected_db(function, args=None, kwargs=None, logger = None):
  while True:
    try:
      function(*args, **kwargs)
      break
    except OperationalError:
      if logger:
        logger.warning('*** Protected DB access failled. Retrying...')
      connection.close()
      sleep(0.1)
 

Answer 2

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

Answer 3

I had the same problem just a few days ago, but without Django. In my case, I was running a script forever, which would connect to the DB, then do its stuff, and to do a DB insert only when needed. Sometimes, the script needed to do its stuff for a days though, and as you stated, the default wait_timeout of MySQL is 28800 seconds or 8 hours.

I am not sure I understood your architecture properly, but I suspect something similar might be going on: You start your server, it connects to the DB (query #1), then you sleep one night (more than 8 hours), try to login (query #2), and voila, the connection has expired.

You can verify if this theory is correct in two easy ways:

1) set wait_timeout to 86400 (24 hours), do the same check you did from evening to morning by trying to login and you should be able to do so without an error.

2) set wait_timeout to a super small value, just a few seconds, and repeat the test - it should crash in a minute, not overnight.

Do not forget to restart MySQL after changing its params.

How did I solve the problem (without Django): Using a simple retry from the tenacity pack + connection restart before the retry.

How could you resolve it: Just found this Django plugin which is supposed to do exactly this. Never used it, but might be worth trying.

Note: While increasing the wait_timeout from MySQL could fix the problem, I wouldn't go for it if I can fix it with such retries. Huge values might be dangerous as stalled connections may start to build up and result in another error: Too many connections.

Answer 4

In general, a connection should do its task, then disconnect. There is no good advantage in keeping a connection alive "forever", which you seem to have.

It appears, as you noted, that a "timeout" has zapped your connection. (There are many timeouts; you discovered one of them.)

Even though you are able to increase the timeout, that would not be a complete solution. Other hiccups could happen, causing a disconnect.

Two solutions; do them both:

• Connect, do the task, disconnect.
• Check errors, if "gone away", then reconnect.