DB connection issue with encode/databases library

Question

I have an error

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

after leaving my application idle (0 requests to DB) for few hours. I thought, databases module creates connection pool (it does) and recreates connections after some timeout automatically, but it doesn't.

Requirements - Python 3.8, FastAPI, databases[mysql].

Any ideas ?

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

endpoint example

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

You can use the pool_recycle parameter to make sure the connection pool drops or reconnects any connections that have been lingering as idle for too long. A setting of 3600 usually works fine with MySQL - it'll recycle the connection after being idle for an hour.

You can either give it as a parameter to Database(.., pool_recycle=3600), or as an option in the connection string (...?pool_recycle=3600):

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

.. or

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