Why do I keep getting a sqlalchemy timeout using flask-login in my flask app?

Question

I am running a Flask web application in a Docker swarm that utilizes Flask-Login for user authentication. I have a User class modeled, and I use Flask-SQLAlchemy to connect the app to a Postgres database located on an AWS RDS db instance.

Flask-Login expects a function placed in your model class to look up a user, given their ID, defined as follows:

@login.user_loader
def load_user(id):
    return User.query.get(int(id))

Flask-Login then uses this function to call your User model to get info on the current user stored in session.

This all works fine most of the time, but every now and then, I run into an issue where this call runs into the Postgres connection dropping. This happens both when I connect to an AWS postgres instance, or a locally run Docker instance of Postgres.

AWS timeout error:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) SSL SYSCALL error: EOF detected

[SQL: SELECT users.id AS users_id, users.name AS users_name, ***TRIMMED*** 
FROM users 
WHERE users.id = %(param_1)s]
[parameters: {'param_1': 14}]
(Background on this error at: http://sqlalche.me/e/e3q8)

Docker (Local DB) Timeout:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

[SQL: SELECT users.id AS users_id, users.name AS users_name, ***TRIMMED*** 
FROM users 
WHERE users.id = %(param_1)s]
[parameters: {'param_1': 14}]
(Background on this error at: http://sqlalche.me/e/e3q8)

I came across this post that talked about utilizing the pool_pre_ping parameter in SQLAlchemy, which basically makes sqlalchemy always test the connection first before proceeding (read more here). So I updated my instantiation of flask-sqlalchemy to reflect this.

db = SQLAlchemy(engine_options={'pool_pre_ping': True})

I'm still encountering the problem though...albeit not as frequently. Does anyone have any idea? I know this most likely has something to do with the way SQLAlchemy pools connections - but I'm just not sure what.

score 0 · Answer 1 · answered Jan 19 '20 at 17:52

Not sure if this would help, but I do have the following in my SQLAlchemy connect string (notice the pool_recycle) ...

# pool_recycle manages situation where mysql automatically disconnects after 8 hours of inactivity    
qry_engine = create_engine('mysql+mysqlconnector://root:root@localhost:3306/', pool_recycle=3600, echo=False)

I'm pretty much an amateur w/ SQLAlchemy but I got this from documentation a while ago and just put it in as a matter of course. I also just noticed that it may be mySQL-specific. I didn't delve too deeply into what happens after 3600 minutes.

Why do I keep getting a sqlalchemy timeout using flask-login in my flask app?

1 Answers1