Monday, 30 August 2021

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

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

---- EDIT 1 ----

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.

---- EDIT 2 ----

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



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

No comments:

Post a Comment