Website crash due to SQLAlchemy QueuePool error

[TheOneHoff]

Basic Information

The application crashes when the amount of PostgreSQL sessions is greater then 5. This happens because the app does not close the session when it ends

Other Information

[Are there any additional information that we should know about?]

[frinzekt]

This is an interesting issue. Can you add trace logs or screenshot of this?

I can see this being an issue and there are a couple of ways to resolve it

• find places where db connection is not closed
• transform production stage of app to use gunicorn workers
• increase the connection allowed to the Postgres database

[TheOneHoff]

I think I prematurely wrote this issue. I'm still looking into it

[TheOneHoff]

Log Traceback (most recent call last): File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 2464, in call return self.wsgi_app(environ, start_response) File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 2450, in wsgi_app response = self.handle_exception(e) File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1867, in handle_exception reraise(exc_type, exc_value, tb) File "/usr/local/lib/python3.9/site-packages/flask/_compat.py", line 39, in reraise raise value File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 2447, in wsgi_app response = self.full_dispatch_request() File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1952, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1821, in handle_user_exception reraise(exc_type, exc_value, tb) File "/usr/local/lib/python3.9/site-packages/flask/_compat.py", line 39, in reraise raise value File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1950, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1936, in dispatch_request return self.view_functionsrule.endpoint File "/app_code/app/auth.py", line 25, in admin_login admin = User.query.filter_by(username=admin_username).first() File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2750, in first return self.limit(1)._iter().first() File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2834, in _iter result = self.session.execute( File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1676, in execute conn = self._connection_for_bind(bind, close_with_result=True) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1526, in _connection_for_bind return self._transaction._connection_for_bind( File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 747, in _connection_for_bind conn = bind.connect() File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3166, in connect return self._connection_cls(self, close_with_result=close_with_result) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 96, in init else engine.raw_connection() File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3245, in raw_connection return self._wrap_pool_connect(self.pool.connect, _connection) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3212, in _wrap_pool_connect return fn() File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 301, in connect return _ConnectionFairy._checkout(self) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 761, in _checkout fairy = _ConnectionRecord.checkout(pool) File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 419, in checkout rec = pool._do_get() File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 133, in _do_get raise exc.TimeoutError( sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00 (Background on this error at: http://sqlalche.me/e/14/3o7r)

[TheOneHoff]

From my understanding, this is due to incorrect session handling. SQLalchemy uses a pool to store recently used sessions. However, due to the nature of the site, these sessions cannot be freed due to their constant use

[frinzekt]

I feel the main problem is line 68 https://github.com/UWA-CITS3200-18-2021/ReSQ/blob/main/docker/app/runtime.sh

This should be a gunicorn process so database are automatically closed

[TheOneHoff]

Excerpt from the SQLAlchemy docs:

The application is not returning connections to the pool - This is the next most common reason, which is that the application is making use of the connection pool, but the program is failing to release these connections and is instead leaving them open. The connection pool as well as the ORM Session do have logic such that when the session and/or connection object is garbage collected, it results in the underlying connection resources being released, however this behavior cannot be relied upon to release resources in a timely manner.

A common reason this can occur is that the application uses ORM sessions and does not call Session.close() upon them one the work involving that session is complete. Solution is to make sure ORM sessions if using the ORM, or engine-bound Connection objects if using Core, are explicitly closed at the end of the work being done, either via the appropriate .close() method, or by using one of the available context managers (e.g. “with:” statement) to properly release the resource.

[TheOneHoff]

The possible solution is to make sure to use Session.close() after using a session object

[TheOneHoff]

Goddamit I feel so dumb.

The error is potentially caused by failing to call session.commit() after querying the database in the 'get_queue' route