Flask-SQLAlchemy TimeoutError
Clash Royale CLAN TAG#URR8PPPFlask-SQLAlchemy TimeoutError
My backend configuration is :
I've got this error message:
TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30
Do I need to close explicitly the db.session? Shouldn't be the connection back to pool when session goes out of scope?
4 Answers
4
This could happen if you are using debug=True in your application and you have loaded several pages or API endpoints which errored out.
debug=True
The reason is that running the debug version of the app keeps a live debugger open in the error page. This live debugger keeps around all the resources from processing the request, so that you can examine them. This means that the database connection can't be recycled.
You shouldn't use debug mode for the production version of your app (apart from problems like this it is a security risk), and the debugger often won't work anyway (it is designed to work with the flask test server, not with gunicorn). Therefore in prod the solution is to turn off debug.
If you have this problem in dev using debug mode - this is a limitation. You shouldn't be hitting the dev server so hard, or you can increase the limit. Be aware that 15 connections are usually plenty to serve a large number of concurrent requests when they are being recycled properly. It's only in debug that they tend to run out.
@bmjjr Post your code in a different question and I'll take a look at it.
– jwg
Jun 18 '17 at 10:32
thank you, I finally figured out a lot more about what was going on with this error, in my case when I queried posgresql with
SELECT * FROM pg_stat_activity; I found a lot of connections with idle in transaction states that were linked to role/ability access checks, mainly from my templates. I use flask-login and what solved the problem was actively caching the flask-login user_loader User object. Maybe there is a simpler solution but this seems to work well and eliminates the TimeoutErrors in my case: stackoverflow.com/a/44612181/1493069– bmjjr
Jun 18 '17 at 10:41
SELECT * FROM pg_stat_activity;
idle in transaction
Flask-SQLAlchemy manages the connection pool for you, so in general, it should not be necessary to do this. However, there are some instances in which it cannot control this, specifically if you are executing queries outside a request context or using with app.app_context() somewhere.
with app.app_context()
When I paired Flask-SQLAlchemy with apscheduler, I found myself having to explicitly close sessions in the jobs the scheduler executed, or after several hours of running I would get this error.
Note: I know this question is two years old, but it is the question you reach from Google if you search for this error wrt Flask-SQLAlchemy, and the available information is out of date (i.e., the other answer is wrong).
– Two-Bit Alchemist
Feb 18 '15 at 3:03
@app.teardown_request
def checkin_db(exc):
try:
g.db.close()
except AttributeError:
pass
This is the way to do it for default SQLAlchemy, but Flask-SQLAlchemy does this for you. This should not be necessary if you are using Flask-SQLAlchemy.
– Two-Bit Alchemist
Feb 18 '15 at 2:59
I had to add the @app.teardown_request method, as well:
@app.teardown_request
@app.teardown_request
def checkin_db(exc):
user_store.db_session.remove()
I followed the "official" FlaskSecurity Basic SQLAlchemy Application with session quick start and after a couple of requests got the "sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 error". Adding the above code seems to have fixed the issue:
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
I have this same problem but changing debug=False does not help me avoid a Timeout Error. If I simply reload the page 15 times it will crash. I'm using Flask-Sqlalchemy in a factory pattern and don't seem to be doing anything out of the ordinary. But at this point, my app can crash simply by reloading pages.
– bmjjr
Jun 17 '17 at 3:54