Database loss of connection after extended period of inactivity.

[alucarddelta]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import List, Optional

from fastapi import Depends, FastAPI, HTTPException, Query
from sqlmodel import Field, Session, SQLModel, create_engine, select

class TeamBase(SQLModel):
    name: str
    headquarters: str

class Team(TeamBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

class TeamCreate(TeamBase):
    pass

class TeamRead(TeamBase):
    id: int

db_url = "mysql://user:pass@localhost/db"

engine = create_engine(db_url)

def create_db_and_tables():
    SQLModel.metadata.create_all(engine)

def get_session():
    with Session(engine) as session:
        yield session

app = FastAPI()

@app.on_event("startup")
def on_startup():
    create_db_and_tables()

@app.post("/teams/", response_model=TeamRead)
def create_team(*, session: Session = Depends(get_session), team: TeamCreate):
    db_team = Team.from_orm(team)
    session.add(db_team)
    session.commit()
    session.refresh(db_team)
    return db_team


@app.get("/teams/", response_model=List[TeamRead])
def read_teams(
    *,
    session: Session = Depends(get_session),
    offset: int = 0,
    limit: int = Query(default=100, lte=100),
):
    teams = session.exec(select(Team).offset(offset).limit(limit)).all()
    return teams

Description

After an period of inactivity (have not yet isolated how long, roughly a few mins however) the following error will come up.

sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query')

It appears to overcome this in SQLAlchemy, during engine creation you add pool_recycle. eg

engine  = create_engine("mysql://user:pass@localhost/db", pool_recycle=1800)

However if you do the same in SQLmodel the following error occurs.

ERROR:    Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 540, in lifespan
    async for item in self.lifespan_context(app):
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 481, in default_lifespan
    await self.startup()
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 518, in startup
    handler()
  File "/home/brentdreyer/Documents/automation/mnf_database_application/./app/main.py", line 24, in on_startup
    create_db_and_tables()
  File "/home/brentdreyer/Documents/automation/mnf_database_application/./app/db/session.py", line 8, in create_db_and_tables
    SQLModel.metadata.create_all(engine, pool_recycle=1800)
TypeError: create_all() got an unexpected keyword argument 'pool_recycle'

Operating System

Linux

Operating System Details

Fedora KDE 34

SQLModel Version

0.0.4

Python Version

Python 3.9.6

Additional Context

No response

[alucarddelta]

Another option is using pool_pre_ping=True. This is used in the older FastAPI/SQLalchemy demo.

SQLmodel code

engine = create_engine("mysql://user:pass@localhost/db", pool_pre_ping=True)

Demo code app/db/session.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from app.core.config import settings

engine = create_engine('mysql://user:pass@localhost/db', pool_pre_ping=True)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

However the same issue occurs when you add it.

ERROR:    Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 540, in lifespan
    async for item in self.lifespan_context(app):
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 481, in default_lifespan
    await self.startup()
  File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 518, in startup
    handler()
  File "/home/brentdreyer/Documents/automation/mnf_database_application/./app/main.py", line 24, in on_startup
    create_db_and_tables()
  File "/home/brentdreyer/Documents/automation/mnf_database_application/./app/db/session.py", line 8, in create_db_and_tables
    SQLModel.metadata.create_all(engine, pool_pre_ping=True)
TypeError: create_all() got an unexpected keyword argument 'pool_pre_ping'

ERROR:    Application startup failed. Exiting.

[rodg]

Are you passing pool_pre_ping=True into SQLModel.metadata.create_all()? I'm trying to recreate your error from adding pool_pre_ping to the create_engine call (albeit I used sqlite) and I'm not getting the TypeError. I also don't get the error when connecting to a postgresql database with pool_pre_ping specified for create_engine, but I've not tried creating tables.

[alucarddelta]

Yes, I am as passing pool_pre_ping=True into SQLModel.metadata.create_all() as I am passing engine object directly into create_all function.

db_url = "mysql://root:password@127.0.0.1:3307/db"

engine = create_engine(db_url, pool_pre_ping=True)

def create_db_and_tables():
    SQLModel.metadata.create_all(engine)

def get_session():
    with Session(engine) as session:
        yield session

Yah I don't know about SQLite or PostgreSQL, as my testing is being done with MariaDB. MariaDB is being ran in a docker container on the same host.

I have isolated the timeout that cases the sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') error to be about 10-15mins from the last query. This is inline with some of the default connection timeout of MariaDB. Which makes sense as the pool_pre_ping would re-establish the connection prior to the query to run on the DB, Thus no error.

[alucarddelta]

You know what... I think I screwed up... I re-ran the code and now its working as expected with pool_pre_ping and I'm now no longer getting the same error... I'm willing to bet @rodg that you are correct and I put in pool_pre_ping in the wrong spot, and when I went to do my demo code I corrected my self with out realising.

I will test further and report back in a few hours.

[YuriiMotov]

I just double-checked, pool_pre_ping=True works:

import time
from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine

db_url = "mysql+pymysql://user:mysecretpassword@localhost/some_db"

engine = create_engine(db_url , pool_pre_ping=True)


class User(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, nullable=False)
    name: str


def main():
    SQLModel.metadata.drop_all(engine)
    SQLModel.metadata.create_all(engine)
    with Session(engine) as session:
        session.add(User(name="user 1"))
        session.commit()

    time.sleep(60*2)

    with Session(engine) as session:
        user = session.get(User, 1)
        assert user.name == "user 1"
        print("works!")


if __name__ == "__main__":
    main()

DB session timeouts configured to 60 seconds:

services:
  mysql-db:
    image: mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: mypwd
      MYSQL_USER: user
      MYSQL_PASSWORD: mysecretpassword
      MYSQL_DATABASE: some_db
    command: --wait_timeout=60 --interactive_timeout=60
    ports:
      - 3306:3306