Why do I get 'MySQL server has gone away' after running a Telegram bot for some hours?

Question

I'm building a Django (ver. 3.0.5) app that uses mysqlclient (ver. 2.0.3) as the DB backend. Additionally, I've written a Django command that runs a bot written using the python-telegram-bot API, so the mission of this bot is to run indefinitely, as it has to answer to commands anytime.

Problem is that approximately 24hrs. after running the bot (not necessarily being idle all the time), I get a django.db.utils.OperationalError: (2006, 'MySQL server has gone away') exception after running any command.

I'm absolutely sure the MySQL server has been running all the time and is still running at the time I get this exception. The MySQL server version is 5.7.35.

My assumption is that some MySQL threads get aged out and get closed, so after reusing them they won't get renewed.

Has anyone bumped into this situation and knows how to solve it?

Traceback (most recent call last):
  File "/opt/django/gip/venv/lib/python3.6/site-packages/telegram/ext/dispatcher.py", line 555, in process_update
    handler.handle_update(update, self, check, context)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/telegram/ext/handler.py", line 198, in handle_update
    return self.callback(update, context)
  File "/opt/django/gip/gip/hospital/gipcrbot.py", line 114, in ayuda
    perfil = get_permiso_efectivo(update.message.from_user.id)
  File "/opt/django/gip/gip/hospital/telegram/funciones.py", line 33, in get_permiso_efectivo
    u = Telegram.objects.get(idtelegram=userid)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/django/db/models/query.py", line 411, in get
    num = len(clone)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/django/db/models/query.py", line 258, in __len__
    self._fetch_all()
  File "/opt/django/gip/venv/lib/python3.6/site-packages/django/db/models/query.py", line 1261, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/opt/django/gip/venv/lib/python3.6/site-packages/django/db/models/query.py", line 57, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1151, in execute_sql
    cursor.execute(sql, params)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/django/gip/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 74, in execute
    return self.cursor.execute(query, args)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/opt/django/gip/venv/lib/python3.6/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (2006, 'MySQL server has gone away')

Things I have tried

I already tried changing the Django settings.py file so I set an explicit value for CONN_MAX_AGE, and I also set a value for the MySQL client wait_timeout parameter, being CONN_MAX_AGE lower than wait_timeout.

settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {
            'read_default_file': '/opt/django/gip/gip/gip/my.cnf',
        },
        'CONN_MAX_AGE': 3600,
    }
}

my.cnf:

[client]
...
wait_timeout = 28800

Unfortunately, the behavior is exactly the same: I get an exception approximately 24hrs. after running the bot.

---

Setting CONN_MAX_AGE to None won't make any difference either.

---

I installed the mysql-server-has-gone-away python package as proposed by @r-marolahy, but it won't make a difference either. After nearly 24hours after running it the "gone away" message shows again.

---

I also tried the approach of closing old connections:

from django.db import close_old_connections

try:
    #do your long running operation here
except django.db.utils.OperationalError:
    close_old_connections()
    #do your long running operation here

Still getting the same result.

Answer 1

This error happened to django when MySQL closed the connection because of the server timed out. To enable persistent connections, set CONN_MAX_AGE to a positive integer of seconds or set it to None for unlimited persistent connections (source).

Update1: If the proposed solution above didn't work, you may want to try mysql-server-has-gone-away package. I haven't tried it yet but it might help in this situation.

Update2: another attempt is to try to use try/except statement to catch this OperationalError and reset the connection with close_old_connections.

from django.db import close_old_connections

try:
    #do your long running operation here
except django.db.utils.OperationalError:
    close_old_connections()
    #do your long running operation here

update3: as described here

The Django ORM is a synchronous piece of code, and so if you want to access it from asynchronous code you need to do special handling to make sure its connections are closed properly.

However, it seams that Django ORM uses asgiref.sync.sync_to_async adapter which works only until MySQL closed the connection. In this case using channels.db.database_sync_to_async (which is SyncToAsync version that cleans up old database connections when it exits) might solve this issue.

You can use it like the following (source):

from channels.db import database_sync_to_async

async def connect(self):
    self.username = await database_sync_to_async(self.get_name)()

def get_name(self):
    return User.objects.all()[0].name

or use it as a decorator:

@database_sync_to_async
def get_name(self):
    return User.objects.all()[0].name

Make sure to follow the installation instruction here first.