MySQL----MySQL数据库出现Lost connection to MySQL server during query错误的解决办法

【原文链接】MySQL----MySQL数据库出现Lost connection to MySQL server during query错误的解决办法

问题描述

Mysql数据库在查询数据库的时候回报出了如下异常：Lost connection to MySQL server during query，具体异常信息如下：

Traceback (most recent call last):
  File "/opt/mugen/run_mugen.py", line 883, in run_testsuit
    env = get_env(testsuit_name)
  File "/opt/mugen/run_mugen.py", line 751, in get_env
    row = session.query(Testsuit).filter(Testsuit.name == testsuit_name).first()
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2752, in first
    return self.limit(1)._iter().first()  # type: ignore
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2855, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2229, in execute
    return self._execute_internal(
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2114, in _execute_internal
    conn = self._connection_for_bind(bind)
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1981, in _connection_for_bind
    return trans._connection_for_bind(engine, execution_options)
  File "<string>", line 2, in _connection_for_bind
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/orm/state_changes.py", line 137, in _go
    ret_value = fn(self, *arg, **kw)
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1108, in _connection_for_bind
    conn = bind.connect()
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3251, in connect
    return self._connection_cls(self)
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 147, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2413, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3275, in raw_connection
    return self.pool.connect()
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 455, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 1271, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 725, in checkout
    rec._checkin_failed(err, _fairy_was_created=False)
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 722, in checkout
    dbapi_connection = rec.get_connection()
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 842, in get_connection
    self.__connect()
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 906, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 901, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 640, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 580, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "/usr/local/python3/lib/python3.9/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/usr/local/python3/lib/python3.9/site-packages/pymysql/connections.py", line 633, in connect
    self._request_authentication()
  File "/usr/local/python3/lib/python3.9/site-packages/pymysql/connections.py", line 907, in _request_authentication
    auth_packet = self._read_packet()
  File "/usr/local/python3/lib/python3.9/site-packages/pymysql/connections.py", line 692, in _read_packet
    packet_header = self._read_bytes(4)
  File "/usr/local/python3/lib/python3.9/site-packages/pymysql/connections.py", line 748, in _read_bytes
    raise err.OperationalError(
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')

解决办法：

1、在数据库中查看如下变量的值

可以看到这里的net_read_timeout和net_write_timeout分别为30和60秒，首先将这两个参数修改为更大一点的值。比如修改为300和900。如下所示，这里需要注意的是修改的时候需要分别设置global和session级别的，然后才会生效。