B
B
BuBux2019-12-19 11:48:52
MySQL
BuBux, 2019-12-19 11:48:52

sqlalchemy error: sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) MySQL Connection not available. How to decide?

I am writing a bot on discord.py. After contacting the bot, a request is made to the database and information is pulled out. At the first access, everything is fine, but if you write half an hour after the last message, it gives this error. Tried to append after db.session.close() requests, but it doesn't help.
Full error code:

Ignoring exception in on_message
Traceback (most recent call last):
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\engine\base.py", line 1173, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\engine\default.py", line 768, in _init_compiled
    self.cursor = self.create_cursor()
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\engine\default.py", line 1122, in create_cursor
    return self._dbapi_connection.cursor()
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\pool\base.py", line 965, in cursor
    return self.connection.cursor(*args, **kwargs)
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\mysql\connector\connection.py", line 809, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\discord\client.py", line 270, in _run_event
    await coro(*args, **kwargs)
  File "E:/python_projects/discord/app/bot.py", line 114, in on_message
    chat = Chat.query.filter(Chat.user_id == message.author.id).first()
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\orm\query.py", line 3265, in first
    ret = list(self[0:1])
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\orm\query.py", line 3043, in __getitem__
    return list(res)
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\orm\query.py", line 3367, in __iter__
    return self._execute_and_instances(context)
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\orm\query.py", line 3392, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\engine\base.py", line 982, in execute
    return meth(self, multiparams, params)
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\sql\elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\engine\base.py", line 1101, in _execute_clauseelement
    distilled_params,
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\engine\base.py", line 1176, in _execute_context
    e, util.text_type(statement), parameters, None, None
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\engine\base.py", line 1476, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\engine\base.py", line 1173, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\engine\default.py", line 768, in _init_compiled
    self.cursor = self.create_cursor()
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\engine\default.py", line 1122, in create_cursor
    return self._dbapi_connection.cursor()
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\sqlalchemy\pool\base.py", line 965, in cursor
    return self.connection.cursor(*args, **kwargs)
  File "E:\python_projects\bubux_test_bot\env\lib\site-packages\mysql\connector\connection.py", line 809, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) MySQL Connection not available.
[SQL: SELECT chat.id AS chat_id_1, chat.user_id AS chat_user_id, chat.chat_id AS chat_chat_id 
FROM chat 
WHERE chat.user_id = %(user_id_1)s 
 LIMIT %(param_1)s]
[parameters: [immutabledict({})]]
(Background on this error at: http://sqlalche.me/e/e3q8)

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander, 2019-12-19
@BuBux

It is enough to re-read the documentation and write the code correctly:
https://docs.sqlalchemy.org/en/13/dialects/mysql.h...
MySQL by default automatically closes connections if they are idle for a certain time (default 8 hours). To work around this issue, use the create_engine option. pool_recycle , which sets the time after which the connection is refreshed if it has already existed for so many seconds:
To detect other types of breaks, a periodic ping should be used.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question