Python | peewee.InterfaceError

1. 简介

服务器上运行的Python后台，每次到第二天就会出现后台管理账号无法登录情况。查看后台日志发现是数据库方面出现了问题，最先以为是数据库无法访问。因为后台有正在运行的任务，会经常访问数据库发现并没有报数据库方面的错，只有HTTP请求的线程无法访问。

(0, '') <class 'peewee.InterfaceError'>, (0, ''), [<FrameSummary file /flask/router/user.py, line 29 in login>, <FrameSummary file /flask/service/user.py, line 19 in login_user>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 6522 in get>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 6970 in get>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 1918 in inner>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 1989 in execute>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 2162 in _execute>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 3190 in execute>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 3174 in execute_sql>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 2950 in __exit__>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 191 in reraise>, <FrameSummary file /usr/local/lib/python3.10/site-packages/peewee.py, line 3177 in execute_sql>, <FrameSummary file /usr/local/lib/python3.10/site-packages/pymysql/cursors.py, line 148 in execute>, <FrameSummary file /usr/local/lib/python3.10/site-packages/pymysql/cursors.py, line 310 in _query>, <FrameSummary file /usr/local/lib/python3.10/site-packages/pymysql/connections.py, line 547 in query>, <FrameSummary file /usr/local/lib/python3.10/site-packages/pymysql/connections.py, line 793 in _execute_command>]

2. 原因

peewee会为每个线程分配一条数据库链接，当页面长时间没有收到HTTP请求数据操作数据库时，处理HTTP请求的线程的数据库连接就会失效，所有会出现页面无法登录，而后台运行的任务会经常访问数据库，所有连接不会失效。

3. 问题复现

第1步修改数据库wait_timeout时间

让连接更快失效，这里将超时时间设置为3秒。

# 查看数据库wait_timeout
show global variables like 'wait_timeout';

# 设置数据库wait_time
set global wait_timeout=3;

第2步执行测试代码

import time

from peewee import *

db = MySQLDatabase(
    'test',
    user='root',
    password='12345678',
    host='127.0.0.1',
    port=3306,
)


class User(Model):
    username = CharField()
    password = CharField()
    salt = CharField()
    state = SmallIntegerField()

    class Meta:
        database = db


db.create_tables([User])

query = User.select()
ls = [user.username for user in query]

time.sleep(5)
query = User.select()
ls = [user.username for user in query]

执行结果

Traceback (most recent call last):
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 3177, in execute_sql
    cursor.execute(sql, params or ())
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 701, in _read_packet
    raise err.OperationalError(
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/yimt/Code/PycharmProjects/hello-python/work.py", line 31, in <module>
    ls = [user.username for user in query]
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 6960, in __iter__
    self.execute()
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 1918, in inner
    return method(self, database, *args, **kwargs)
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 1989, in execute
    return self._execute(database)
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 2162, in _execute
    cursor = database.execute(self)
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 3190, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 3174, in execute_sql
    with __exception_wrapper__:
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 2950, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 191, in reraise
    raise value.with_traceback(tb)
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/peewee.py", line 3177, in execute_sql
    cursor.execute(sql, params or ())
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/Users/yimt/Code/PycharmProjects/hello-python/venv/lib/python3.10/site-packages/pymysql/connections.py", line 701, in _read_packet
    raise err.OperationalError(
peewee.OperationalError: (2013, 'Lost connection to MySQL server during query')

4. 解决方法

使用ReconnectMixin，当发现数据库连接失效时会自动重新连接。

import time

from playhouse.shortcuts import ReconnectMixin
from peewee import *


class ReconnectMySQLDatabase(ReconnectMixin, MySQLDatabase):
    pass


db = ReconnectMySQLDatabase(
    'test',
    user='root',
    password='12345678',
    host='127.0.0.1',
    port=3306,
)


class User(Model):
    username = CharField()
    password = CharField()
    salt = CharField()
    state = SmallIntegerField()

    class Meta:
        database = db


db.create_tables([User])

query = User.select()
ls = [user.username for user in query]

time.sleep(5)
query = User.select()
ls = [user.username for user in query]

5. 参考

• github-issues-#1546