Running on the server Python backstage , Every time the next day, the background management account cannot be logged in . Check the background log and find that there is a problem in the database , At first, I thought that the database was inaccessible . Because there are running tasks in the background , I often visit the database and find that there are no database errors reported , Only HTTP The requested thread is inaccessible .
(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>]
peewee Each thread is assigned a database link , When the page is not received for a long time HTTP When requesting data to operate the database , Handle HTTP The database connection of the requested thread will fail , All the pages that will appear cannot be logged in , The tasks running in the background often access the database , All connections will not fail .
The first 1 Step modify database wait_timeout Time
Make connections fail faster , The timeout is set to 3 second .
# view the database wait_timeout
show global variables like 'wait_timeout';
# Set up the database wait_time
set global wait_timeout=3;
The first 2 Step execute the test code
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]
Execution results
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')
Use ReconnectMixin, When the database connection fails, it will be automatically reconnected .
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]
