Database loss of connection after extended period of inactivity. #1524
-
First Check
Commit to Help
Example Codefrom 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 teamsDescriptionAfter an period of inactivity (have not yet isolated how long, roughly a few mins however) the following error will come up.
It appears to overcome this in SQLAlchemy, during engine creation you add engine = create_engine("mysql://user:pass@localhost/db", pool_recycle=1800)However if you do the same in SQLmodel the following error occurs. Operating SystemLinux Operating System DetailsFedora KDE 34 SQLModel Version0.0.4 Python VersionPython 3.9.6 Additional ContextNo response |
Beta Was this translation helpful? Give feedback.
Replies: 5 comments
-
|
Another option is using SQLmodel codeengine = create_engine("mysql://user:pass@localhost/db", pool_pre_ping=True)Demo code app/db/session.pyfrom 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. |
Beta Was this translation helpful? Give feedback.
-
|
Are you passing |
Beta Was this translation helpful? Give feedback.
-
|
Yes, I am as passing db_url = "mysql://root:[email protected]: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 sessionYah 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 |
Beta Was this translation helpful? Give feedback.
-
|
You know what... I think I screwed up... I re-ran the code and now its working as expected with I will test further and report back in a few hours. |
Beta Was this translation helpful? Give feedback.
-
|
I just double-checked, 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 |
Beta Was this translation helpful? Give feedback.
I just double-checked,
pool_pre_ping=Trueworks: