Telegram API Question posted in
A comprehensive official documentation can be found here.

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

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.

Tags:

6

Answers


  1. Chosen as BEST ANSWER
    0

    I ended up scheduling a DB query every X hours (in this case, 6h) in the bot. The python-telegram-bot has a class called JobQueue which has a method called run_repeating. This will run a task every n seconds. So I declared:

    def check_db(context):
    # Do the code for running "SELECT 1" in the DB
    return

updater.job_queue.run_repeating(check_db, interval=21600, first=21600)

    After this change I haven't had the same problem again.

    Also, calling the mostly undocumented close_if_unusable_or_obsolete() Django method from time to time works as well in my case.

    from django.db import connection

connection.close_if_unusable_or_obsolete()

  2. 0

    This is usually because of server side wait_timeout. Server is closing connection after wait_timeout seconds of inactivity.
    You should either increase timeout:

    SET SESSION wait_timeout = ...

    Or deal with this sort of error and reconnect and retry when it happens.

    Another option is to ping server with query (like select 1) in regular intervals (wait_timeout - 1)

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
  4. 0

    The connection will break for any of a number of reasons, not just timeouts.
    So, simply plan for it breaking.

    Plan A (most robust solution):

    Whenever running a query, check for errors and have code to reconnect and rerun the query (or transaction).

    Plan B (risky for transactions):

    Turn on auto-reconnect. This is bad if you ever use multi-statement transactions. Auto-reconnect in the middle of a transaction can lead to a corrupt dataset (by violating the semantics of a "transaction".) This is because the first part of the transaction is ROLLBACK'd (due to the disconnect) and the rest is COMMITted.

    Plan C (straight-forward):

    Connect when a message comes in; do your work; then disconnect. That is, be disconnected most of the time. This approach is necessary if you could have lots of clients connecting (but rarely doing anything).

    Plan D (not worth considering):

    Increase various timeouts. Why bother solving one problem (low timeout) when other other problems go unsolved (network hiccup).

    My preference? C is easy and nearly always sufficient. A takes more code, but is "best". Both C and A is possibly even better.

    Login or Signup to reply.
  5. 0

    The reason why that happen is because the close_old_connection function.

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

    Example Code:

    from django.db import close_old_connections
close_old_connections()
# do some db actions, it will reconnect db

    Please let me know if it not resolve your issue.

    Login or Signup to reply.
  6. 0

    I had this problem too.
    The (2006, 'MySQL server has gone away') can happen for various reasons, among:

    • Too long requests

    Solution is to fine tune MySQL / MariaDB to allow bigger requests:

    In /etc/mysql/mariadb.conf.d/50-server.cnf

    [mysqld]
...
max_allowed_packet=128M
innodb_log_file_size = 128M # Fix kopano-server: SQL [00000088] info: MySQL server has gone away. Reconnecting, see https://jira.kopano.io/browse/KC-1053
    • No interaction with the client for a couple of hours

    You can use whatever other solutions are posted here.
    Solution can be to set the timeout to something very big (80 hours in my setup)

    In /etc/mysql/mariadb.conf.d/50-server.cnf

    [mysqld]
...
wait_timeout = 288000 # Increase timeout to 80h before Mysql server will also go away
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search