与encode/databases库的DB连接问题

Question

显示错误

pymysql.err.InternalError: Packet sequence number wrong - got 0 expected 1

让我的应用程序空闲(0个请求到DB)几个小时后。我认为，数据库模块创建连接池(它确实)，并在超时后自动重新创建连接，但它没有。

要求- Python 3.8, FastAPI，数据库[mysql].

有什么想法吗?

main.py

.......
@app.on_event("startup")
async def startup():
await db.connect()

@app.on_event("shutdown")
async def shutdown():
await db.disconnect()
.......

db_config.py

import databases
import sqlalchemy
import os
import logging
from functools import wraps
HOST = 'mysql://user:user_passw@{host}:{port}/sw_database'.format(host=os.environ.get("DB_HOST", "127.0.0.1"),
port=os.environ.get("DB_PORT", "3306"))
db = databases.Database(HOST)
metadata = sqlalchemy.MetaData()
logger = logging.getLogger(__name__)

def perform_transaction(foo):
@wraps(foo)
async def decorate(*args, **kwargs):
async with db.connection() as connection:
async with connection.transaction():
try:
res = await foo(*args, **kwargs)
except Exception as e:
logger.error(e)
return []
return res
return decorate

端点例子

from fastapi import APIRouter
from db_config import db, perform_transaction
router = APIRouter()
@router.get('/devices_list', responses=responses_for_devices_table)
@perform_transaction
async def get_devices_list():
query = devices.select()
return await db.fetch_all(query)

Answer 1

您可以使用pool_recycle参数来确保连接池删除或重新连接任何闲置时间过长的连接。设置3600通常对MySQL很好-它会在空闲一小时后回收连接。

您可以将其作为Database(.., pool_recycle=3600)的参数，或作为连接字符串(...?pool_recycle=3600)中的选项:

db = databases.Database(HOST, pool_recycle=3600)

. .或者

HOST = 'mysql://user:user_passw@{host}:{port}/sw_database?pool_recycle=3600'.format(..)