Bot运行5-6小时后无法执行MySQL查询命令排查求助

Bot启动后初期运行正常，所有需连接数据库的命令均可执行，但运行5-6小时后，这类数据库相关命令完全失效，本地命令仍能正常工作。数据库采用连接池方式管理连接，每次命令执行完毕后已将连接归还至连接池。

• 连接池重复实例化问题：当前getConnection()函数每次调用都会新建MySQLConnectionPool实例，而非复用已创建的连接池。这会导致连接管理混乱，旧连接池的连接无法被正确复用，最终引发连接耗尽或无效连接堆积。
• 连接有效性未校验：MySQL服务器会通过wait_timeout或interactive_timeout主动关闭闲置连接，但从连接池获取连接时未校验连接是否有效，导致拿到已失效的连接。
• 连接归还不彻底：检查业务代码中是否存在未调用connection_object.close()归还连接的情况，若连接未归还，连接池会被快速耗尽，后续请求无法获取可用连接。
• 异步框架与同步连接池兼容性：Bot基于discord.py异步框架，而mysql.connector的连接池是同步实现，异步环境下可能出现连接阻塞或状态异常，需确认异步操作与同步数据库连接的适配性。
• MySQL服务器配置限制：检查MySQL的max_connections、wait_timeout等参数，若连接池大小超过服务器允许的最大连接数，或连接闲置时间超过超时阈值，会导致连接被强制关闭。
• 网络与DNS解析问题：MySQL日志中存在大量IP/主机名解析警告，虽暂时未直接影响连接，但长期可能导致连接建立失败，建议直接使用IP地址而非主机名连接数据库，同时检查Bot服务器与MySQL服务器的网络稳定性。

数据库连接实现 #

import mysql.connector
from bestconfig import Config
from mysql.connector import pooling
from mysql.connector import Error

config = Config() #config['version']

def getConnection(): 
    try:
        connection_pool = pooling.MySQLConnectionPool(pool_name="discord_pool",
                                                    pool_size=2,
                                                    pool_reset_session=True,
                                                    host=config['host'],
                                                    database=config['database'],
                                                    user=config['user'],
                                                    password=config['password'])

        # Get connection object from a pool
        connection_object = connection_pool.get_connection()

        if connection_object.is_connected():
            db_Info = connection_object.get_server_info()
            print("Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info)

            cursor = connection_object.cursor()
            cursor.execute("select database();")
            record = cursor.fetchone()
            print("Your connected to - ", record)
        else:
            connection_object.reconnect(attempts=1, delay=0)

    except Error as e:
        print("Error while connecting to MySQL using Connection pool ", e)
    return connection_object

Python运行日志 #

2022-12-25 05:52:00,155 INFO Shard ID None has successfully RESUMED session 5a6cfa0f2871c73636a54087ddb35c3a.
2022-12-25 07:09:37,816 ERROR Attempting a reconnect in 1.11s
Traceback (most recent call last):
  File "/usr/lib/python3.11/asyncio/tasks.py", line 490, in wait_for
    return fut.result()
           ^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/discord/gateway.py", line 372, in from_client
    socket = await client.http.ws_connect(str(url))
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/discord/http.py", line 536, in ws_connect
    return await self.__session.ws_connect(url, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/aiohttp/client.py", line 779, in _ws_connect
    resp = await self.request(
           ^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/aiohttp/client.py", line 536, in _request
    conn = await self._connector.connect(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/aiohttp/connector.py", line 540, in connect
    proto = await self._create_connection(req, traces, timeout)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/aiohttp/connector.py", line 901, in _create_connection
    _, proto = await self._create_direct_connection(req, traces, timeout)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/aiohttp/connector.py", line 1175, in _create_direct_connection
    transp, proto = await self._wrap_create_connection(
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/aiohttp/connector.py", line 980, in _wrap_create_connection
    return await self._loop.create_connection(*args, **kwargs)  # type: ignore[return-value]  # noqa
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/asyncio/base_events.py", line 1063, in create_connection
    sock = await self._connect_sock(
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/asyncio/base_events.py", line 967, in _connect_sock
    await self.sock_connect(sock, address)
  File "/usr/lib/python3.11/asyncio/selector_events.py", line 634, in sock_connect
    return await fut
           ^^^^^^^^^
asyncio.exceptions.CancelledError

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.11/dist-packages/discord/client.py", line 624, in connect
    self.ws = await asyncio.wait_for(coro, timeout=60.0)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/asyncio/tasks.py", line 492, in wait_for
    raise exceptions.TimeoutError() from exc
TimeoutError
2022-12-25 07:09:39,518 INFO Shard ID None has successfully RESUMED session 5a6cfa0f2871c73636a54087ddb35c3a.
2022-12-25 07:27:23,366 INFO Shard ID None has successfully RESUMED session 5a6cfa0f2871c73636a54087ddb35c3a.
2022-12-25 07:44:19,708 INFO Shard ID None has successfully RESUMED session 5a6cfa0f2871c73636a54087ddb35c3a.

MySQL服务器日志 #

2022-12-24T23:31:25.904328Z 718 [Warning] [MY-010055] [Server] IP address '2.57.122.23' could not be resolved: Name or service not known
2022-12-25T00:43:30.330033Z 719 [Warning] [MY-010058] [Server] Hostname 'zg-1220a-127.stretchoid.com' does not resolve to '192.241.224.10'.
2022-12-25T03:47:37.608995Z 723 [Warning] [MY-010055] [Server] IP address '46.216.30.14' could not be resolved: Name or service not known
2022-12-25T03:47:37.609192Z 722 [Warning] [MY-010055] [Server] IP address '46.216.30.14' could not be resolved: Name or service not known
2022-12-25T05:31:12.652320Z 772 [Warning] [MY-010055] [Server] IP address '206.189.208.4' could not be resolved: Name or service not known
2022-12-25T06:19:41.620580Z 773 [Warning] [MY-010055] [Server] IP address '92.255.85.233' could not be resolved: Name or service not known
2022-12-25T07:45:01.564618Z 774 [Warning] [MY-010056] [Server] Host name 'pool-5-206-44-143.is74.ru' could not be resolved: Name or service not known
2022-12-25T07:45:01.565053Z 775 [Warning] [MY-010056] [Server] Host name 'pool-5-206-44-143.is74.ru' could not be resolved: Name or service not known
2022-12-25T07:48:46.702665Z 824 [Warning] [MY-010058] [Server] Hostname 'zg-1220a-17.stretchoid.com' does not resolve to '192.241.230.5'.
2022-12-25T08:52:12.885725Z 825 [Warning] [MY-010057] [Server] IP address '35.216.148.48' has been resolved to the host name '48.148.216.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2022-12-25T08:52:12.971229Z 826 [Warning] [MY-010057] [Server] IP address '35.216.148.48' has been resolved to the host name '48.148.216.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2022-12-25T08:52:13.079504Z 827 [Warning] [MY-010057] [Server] IP address '35.216.148.48' has been resolved to the host name '48.148.216.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2022-12-25T08:52:13.249122Z 828 [Warning] [MY-010057] [Server] IP address '35.216.148.48' has been resolved to the host name '48.148.216.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2022-12-25T08:52:13.418399Z 829 [Warning] [MY-010057] [Server] IP address '35.216.148.48' has been resolved to the host name '48.148.216.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2022-12-25T08:52:13.591984Z 830 [Warning] [MY-010057] [Server] IP address '35.216.148.48' has been resolved to the host name '48.148.216.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2022-12-25T08:52:13.763566Z 831 [Warning] [MY-010057] [Server] IP address '35.216.148.48' has been resolved to the host name '48.148.216.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2022-12-25T08:52:13.934613Z 832 [Warning] [MY-010057] [Server] IP address '35.216.148.48' has been resolved to the host name '48.148.216.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2022-12-25T08:52:14.112660Z 833 [Warning] [MY-010057] [Server] IP address '35.216.148.48' has been resolved to the host name '48.148.216.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2022-12-25T08:52:14.287298Z 834 [Warning] [MY-010057] [Server] IP address '35.216.148.48' has been resolved to the host name '48.148.216.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2022-12-25T08:52:14.461324Z 835 [Warning] [MY-010057] [Server] IP address '35.216.148.48' has been resolved to the host name '48.148.216.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2022-12-25T08:52:14.634360Z 836 [Warning] [MY-010057] [Server] IP address '35.216.148.48' has been resolved to the host name '48.148.216.35.bc.googleusercontent.com', which resembles IPv4-address itself.
2022-12-25T08:52:14.809811Z 837 [Warning] [MY-010057] [Server] IP address '35.216.148.48' has been resolved to the host name '48.148.216.35.bc.googleusercontent.com', which resembles IPv4-address itself.

内容的提问来源于stack exchange，提问作者EliteNoob