Repository: vantage6/vantage6
Issue: [Bug] Database connections all taken

**Describe the bug**

With busy, long-running servers, the SQLAlchemy error below can show up, saying that the limit of the number of connections to the database is reached. Our suspicion is that somewhere connections are kept that should be discarded as they are finished. 

One of the symptoms of this issue appears to be that nodes no longer pick up new tasks (only after they are restarted).

```
2023-02-14T12:11:41.760271084Z 2023-02-14 12:11:41 - server         - DEBUG    - Received request: OPTIONS organization
2023-02-14T12:11:41.799120227Z 2023-02-14 12:11:41 - server         - DEBUG    - Received request: GET organization

2023-02-14T12:11:50.435861581Z 2023-02-14 12:11:50 - server         - ERROR    - Exception occured during request
2023-02-14T12:11:50.435901981Z Traceback (most recent call last):
2023-02-14T12:11:50.435908281Z   File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1516, in full_dispatch_request
2023-02-14T12:11:50.435912581Z     rv = self.dispatch_request()
2023-02-14T12:11:50.435917181Z   File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1502, in dispatch_request
2023-02-14T12:11:50.435921381Z     return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
2023-02-14T12:11:50.435925381Z   File "/usr/local/lib/python3.7/site-packages/flask_restful/__init__.py", line 467, in wrapper
2023-02-14T12:11:50.435929482Z     resp = resource(*args, **kwargs)
2023-02-14T12:11:50.435933082Z   File "/usr/local/lib/python3.7/site-packages/flask/views.py", line 84, in view
2023-02-14T12:11:50.435936982Z     return current_app.ensure_sync(self.dispatch_request)(*args, **kwargs)
2023-02-14T12:11:50.435940682Z   File "/usr/local/lib/python3.7/site-packages/flask_restful/__init__.py", line 582, in dispatch_request
2023-02-14T12:11:50.435944582Z     resp = meth(*args, **kwargs)
2023-02-14T12:11:50.435965382Z   File "/usr/local/lib/python3.7/site-packages/flask_jwt_extended/view_decorators.py", line 157, in decorator
2023-02-14T12:11:50.435971082Z     verify_jwt_in_request(optional, fresh, refresh, locations, verify_type)
2023-02-14T12:11:50.435976982Z   File "/usr/local/lib/python3.7/site-packages/flask_jwt_extended/view_decorators.py", line 108, in verify_jwt_in_request
2023-02-14T12:11:50.435982282Z     _request_ctx_stack.top.jwt_user = _load_user(jwt_header, jwt_data)
2023-02-14T12:11:50.435988182Z   File "/usr/local/lib/python3.7/site-packages/flask_jwt_extended/view_decorators.py", line 170, in _load_user
2023-02-14T12:11:50.435993482Z     user = user_lookup(jwt_header, jwt_data)
2023-02-14T12:11:50.435998282Z   File "/usr/local/lib/python3.7/site-packages/flask_jwt_extended/internal_utils.py", line 32, in user_lookup
2023-02-14T12:11:50.436003282Z     *args, **kwargs
2023-02-14T12:11:50.436007782Z   File "/vantage6/vantage6-server/vantage6/server/__init__.py", line 355, in user_lookup_loader
2023-02-14T12:11:50.436013382Z     auth = db.Authenticatable.get(identity)
2023-02-14T12:11:50.436018883Z   File "/vantage6/vantage6-server/vantage6/server/model/base.py", line 299, in get
2023-02-14T12:11:50.436022783Z     result = session.query(cls).filter_by(id=id_).one()
2023-02-14T12:11:50.436027583Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3358, in one
2023-02-14T12:11:50.436032883Z     ret = self.one_or_none()
2023-02-14T12:11:50.436037883Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3327, in one_or_none
2023-02-14T12:11:50.436043483Z     ret = list(self)
2023-02-14T12:11:50.436050883Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3403, in __iter__
2023-02-14T12:11:50.436054983Z     return self._execute_and_instances(context)
2023-02-14T12:11:50.436058583Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3425, in _execute_and_instances
2023-02-14T12:11:50.436062283Z     querycontext, self._connection_from_session, close_with_result=True
2023-02-14T12:11:50.436065883Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3440, in _get_bind_args
2023-02-14T12:11:50.436069583Z     mapper=self._bind_mapper(), clause=querycontext.statement, **kw
2023-02-14T12:11:50.436072883Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3418, in _connection_from_session
2023-02-14T12:11:50.436076283Z     conn = self.session.connection(**kw)
2023-02-14T12:11:50.436079483Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1133, in connection
2023-02-14T12:11:50.436083383Z     execution_options=execution_options,
2023-02-14T12:11:50.436087483Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1139, in _connection_for_bind
2023-02-14T12:11:50.436092283Z     engine, execution_options
2023-02-14T12:11:50.436105384Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 432, in _connection_for_bind
2023-02-14T12:11:50.436111684Z     conn = bind._contextual_connect()
2023-02-14T12:11:50.436116184Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2251, in _contextual_connect
2023-02-14T12:11:50.436121184Z     self._wrap_pool_connect(self.pool.connect, None),
2023-02-14T12:11:50.436125984Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2285, in _wrap_pool_connect
2023-02-14T12:11:50.436131184Z     return fn()
2023-02-14T12:11:50.436135984Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 363, in connect
2023-02-14T12:11:50.436140984Z     return _ConnectionFairy._checkout(self)
2023-02-14T12:11:50.436145684Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
2023-02-14T12:11:50.436150784Z     fairy = _ConnectionRecord.checkout(pool)
2023-02-14T12:11:50.436155384Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
2023-02-14T12:11:50.436160484Z     rec = pool._do_get()
2023-02-14T12:11:50.436164884Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/impl.py", line 131, in _do_get
2023-02-14T12:11:50.436169484Z     code="3o7r",
2023-02-14T12:11:50.436174284Z sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 (Background on this error at: http://sqlalche.me/e/3o7r)
```

**Additional context**
Restarting the server is a temporary solution to continue as normal


Changed files:
vantage6-server/vantage6/server/model/base.py, vantage6-server/vantage6/server/websockets.py

diff --git a/vantage6-server/vantage6/server/model/base.py b/vantage6-server/vantage6/server/model/base.py
index 246943b6..4ecb939c 100644
--- a/vantage6-server/vantage6/server/model/base.py
+++ b/vantage6-server/vantage6/server/model/base.py
@@ -252,7 +252,6 @@ class DatabaseSessionManager:
     def new_session():
         # log.critical('Create new DB session')
         if DatabaseSessionManager.in_flask_request():
-
             g.session = Database().session_a
 
             # g.session.refresh()
diff --git a/vantage6-server/vantage6/server/websockets.py b/vantage6-server/vantage6/server/websockets.py
index f47d2ba1..ea95f4bf 100644
--- a/vantage6-server/vantage6/server/websockets.py
+++ b/vantage6-server/vantage6/server/websockets.py
@@ -11,6 +11,7 @@ from vantage6.common.task_status import has_task_failed
 from vantage6.server import db
 from vantage6.server.model.authenticable import Authenticatable
 from vantage6.server.model.rule import Operation, Scope
+from vantage6.server.model.base import DatabaseSessionManager
 
 ALL_NODES_ROOM = 'all_nodes'
 
@@ -89,6 +90,9 @@ class DefaultSocketNamespace(Namespace):
         for room in session.rooms:
             self.__join_room_and_notify(room)
 
+        # cleanup (e.g. database session)
+        self.__cleanup()
+
     @staticmethod
     def _add_node_to_rooms(node: Authenticatable) -> None:
         """
@@ -160,6 +164,9 @@ class DefaultSocketNamespace(Namespace):
 
         self.log.info(f'{session.name} disconnected')
 
+        # cleanup (e.g. database session)
+        self.__cleanup()
+
     def on_message(self, message: str) -> None:
         """
         An incomming message from any client.
@@ -235,6 +242,9 @@ class DefaultSocketNamespace(Namespace):
             }, room=f"collaboration_{collaboration_id}"
         )
 
+        # cleanup (e.g. database session)
+        self.__cleanup()
+
     def __join_room_and_notify(self, room: str) -> None:
         """
         Joins room and notify other clients in this room.
@@ -298,3 +308,8 @@ class DefaultSocketNamespace(Namespace):
                 namespace='/tasks',
                 room=room
             )
+
+    @staticmethod
+    def __cleanup() -> None:
+        """ Cleanup database connections """
+        DatabaseSessionManager.clear_session()