Database loss of connection after extended period of inactivity.

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

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.

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.

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.

@rodg

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.

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

Navigation Menu

Repository navigation

Database loss of connection after extended period of inactivity. #60

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

SQLmodel code

Demo code app/db/session.py

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Database loss of connection after extended period of inactivity. #60

Description

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Activity

alucarddelta commented on Aug 30, 2021

SQLmodel code

Demo code app/db/session.py

rodg commented on Aug 31, 2021

alucarddelta commented on Sep 1, 2021

alucarddelta commented on Sep 1, 2021

YuriiMotov commented on Aug 15, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions