mysql | 记录排查 Lost connection to MySQL server during query

参考资料

在量化代码的运行中，出现了账目不对的情况。

在查询日志的过程中出现了下面的错误

[2023-05-22 09:32:34.393] SQLComponents.py line:58 ERROR   Traceback (most recent call last):
  File "/home/ubuntu/.local/lib/python3.9/site-packages/peewee.py", line 3246, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/cursors.py", line 158, in execute
    result = self._query(query)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/cursors.py", line 325, in _query
    conn.query(q)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/connections.py", line 548, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/connections.py", line 797, in _execute_command
    raise err.InterfaceError(0, "")
pymysql.err.InterfaceError: (0, '')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/ubuntu/.local/lib/python3.9/site-packages/BottomShort/Component/SQLComponents.py", line 56, in update_order_one
    return self.sql_srvice.get("Order").update_one(rhino_order)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/BottomShort/Service/SQL/Order.py", line 104, in update_one
    n = query.execute()
  File "/home/ubuntu/.local/lib/python3.9/site-packages/peewee.py", line 1966, in inner
    return method(self, database, *args, **kwargs)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/peewee.py", line 2037, in execute
    return self._execute(database)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/peewee.py", line 2555, in _execute
    cursor = database.execute(self)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/peewee.py", line 3254, in execute
    return self.execute_sql(sql, params)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/peewee.py", line 3246, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/ubuntu/.local/lib/python3.9/site-packages/peewee.py", line 3014, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/peewee.py", line 192, in reraise
    raise value.with_traceback(tb)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/peewee.py", line 3246, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/cursors.py", line 158, in execute
    result = self._query(query)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/cursors.py", line 325, in _query
    conn.query(q)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/connections.py", line 548, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/connections.py", line 797, in _execute_command
    raise err.InterfaceError(0, "")
peewee.InterfaceError: (0, '')

然后发现日志中出现了大量的相似报警。查询这是因为数据库连接断开。

这个时候排查一下情况

查看数据库运行总时长

mysql> show global status like 'uptime';

发现数据库在运行期间没有宕机

查看行为记录

mysql> show global status like 'com_kill';

查看数据库是否主动或者人为的杀死进程。

发现没有。

查看设置

mysql> show global variables like '%timeout';

设置如下

+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| connect_timeout                   | 10       |
| delayed_insert_timeout            | 300      |
| have_statement_timeout            | YES      |
| innodb_flush_log_at_timeout       | 1        |
| innodb_lock_wait_timeout          | 50       |
| innodb_rollback_on_timeout        | OFF      |
| interactive_timeout               | 28800    |
| lock_wait_timeout                 | 31536000 |
| mysqlx_connect_timeout            | 30       |
| mysqlx_idle_worker_thread_timeout | 60       |
| mysqlx_interactive_timeout        | 28800    |
| mysqlx_port_open_timeout          | 0        |
| mysqlx_read_timeout               | 30       |
| mysqlx_wait_timeout               | 28800    |
| mysqlx_write_timeout              | 60       |
| net_read_timeout                  | 30       |
| net_write_timeout                 | 60       |
| replica_net_timeout               | 60       |
| rpl_stop_replica_timeout          | 31536000 |
| rpl_stop_slave_timeout            | 31536000 |
| slave_net_timeout                 | 60       |
| ssl_session_cache_timeout         | 300      |
| wait_timeout                      | 28800    |
+-----------------------------------+----------+

比较重要的就是

connect_timeout : 10
- 创建连接时间
net_write_timeout : 60
- 写时间
wait_timeout : 28800
- 等待时间

因为创建连接没有报错和数据量非常小，不会超过 10s 中，所以，不是前两个的错。

等待时间值得是，如果，28800 秒「8 小时」没有操作数据库，则数据库就会断开连接。

所以，我又查询了日志做开始出现错误的时间。

[2023-05-22 00:22:11.226] RestClient.py line:119 ERROR   400 {"code":-2010,"msg":"Account has insufficient balance for requested action."}
[2023-05-22 09:32:34.391] SQLComponents.py line:58 ERROR   Traceback (most recent call last):
  File "/home/ubuntu/.local/lib/python3.9/site-packages/peewee.py", line 3246, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/cursors.py", line 158, in execute
    result = self._query(query)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/cursors.py", line 325, in _query
    conn.query(q)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/connections.py", line 549, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/connections.py", line 779, in _read_query_result
    result.read()
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/connections.py", line 1157, in read
    first_packet = self.connection._read_packet()
  File "/home/ubuntu/.local/lib/python3.9/site-packages/pymysql/connections.py", line 705, in _read_packet
    raise err.OperationalError(
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

日志是 5-22 00:22 出现了输出。但是，8 小时后，才出现了 2013 错误。

最后，查询了币安的下单信息，确实是长达 8 小时没有订单变化。

修改情况待定。