[BUG] DB OperationalError

[ebreton]

Describe the bug

The application returns (randomly) 500 errors.

When looking at the logs, we can see some OperationError occuring: sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly

Following the link at the end of the traceback, we have the following information from SQLAlchemy web site:

Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc.

This error is a DBAPI Error and originates from the database driver (DBAPI), not SQLAlchemy itself.

The OperationalError is the most common (but not the only) error class used by drivers in the context of the database connection being dropped, or not being able to connect to the database. For tips on how to deal with this, see the section Dealing with Disconnects.

Here is the full traceback

[2019-05-20 07:46:24 +0000] [12] [INFO] ('10.0.16.163', 56442) - "GET /api/v1/audit_app/audit_groups/me HTTP/1.1" 500
[2019-05-20 07:46:24 +0000] [12] [ERROR] Exception in ASGI application
Traceback (most recent call last):
File "/usr/local/lib/python3.7/site-packages/uvicorn/protocols/http/httptools_impl.py", line 372, in run_asgi
result = await asgi(self.receive, self.send)
File "/usr/local/lib/python3.7/site-packages/starlette/middleware/errors.py", line 125, in asgi
raise exc from None
File "/usr/local/lib/python3.7/site-packages/starlette/middleware/errors.py", line 103, in asgi
await asgi(receive, _send)
File "/usr/local/lib/python3.7/site-packages/starlette/middleware/base.py", line 27, in asgi
response = await self.dispatch_func(request, self.call_next)
File "/app/app/main.py", line 37, in db_session_middleware
response = await call_next(request)
File "/usr/local/lib/python3.7/site-packages/starlette/middleware/base.py", line 44, in call_next
task.result()
File "/usr/local/lib/python3.7/site-packages/starlette/middleware/base.py", line 37, in coro
await inner(request.receive, queue.put)
File "/usr/local/lib/python3.7/site-packages/starlette/exceptions.py", line 74, in app
raise exc from None
File "/usr/local/lib/python3.7/site-packages/starlette/exceptions.py", line 63, in app
await instance(receive, sender)
File "/usr/local/lib/python3.7/site-packages/starlette/routing.py", line 41, in awaitable
response = await func(request)
File "/usr/local/lib/python3.7/site-packages/fastapi/routing.py", line 66, in app
request=request, dependant=dependant, body=body
File "/usr/local/lib/python3.7/site-packages/fastapi/dependencies/utils.py", line 270, in solve_dependencies
background_tasks=background_tasks,
File "/usr/local/lib/python3.7/site-packages/fastapi/dependencies/utils.py", line 279, in solve_dependencies
solved = await run_in_threadpool(sub_dependant.call, **sub_values)
File "/usr/local/lib/python3.7/site-packages/starlette/concurrency.py", line 24, in run_in_threadpool
return await loop.run_in_executor(None, func, *args)
File "/usr/local/lib/python3.7/concurrent/futures/thread.py", line 57, in run
result = self.fn(*self.args, **self.kwargs)
File "/app/app/api/utils/security.py", line 28, in get_current_user
user = crud.user.get(db, user_id=token_data.user_id)
File "/app/app/crud/user.py", line 12, in get
return db_session.query(User).filter(User.id == user_id).first()
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3215, in first
ret = list(self[0:1])
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3007, in __getitem__
return list(res)
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3317, in __iter__
return self._execute_and_instances(context)
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3342, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
distilled_params,
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
e, statement, parameters, cursor, context
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
y3zc2sy
[SQL: SELECT users.name AS users_name, users.password AS users_password, users.id AS users_id, users.email AS users_email, users.is_active AS users_is_active, users.is_superuser AS users_is_superuser, users.created_at AS users_created_at, users.updated_at AS users_updated_at, users.city_id AS users_city_id
FROM users
WHERE users.id = %(id_1)s
LIMIT %(param_1)s]
[parameters: {'id_1': 2, 'param_1': 1}]
(Background on this error at: http://sqlalche.me/e/e3q8)

Expected behavior
No 500 returned to the end user. Connection refreshed if needed.

Additionnal context
The application runs within a container, following the setup from https://dockerswarm.rocks

[tiangolo]

Thanks!

[luyandadhlamini]

For anyone else who is having the same issue, suggested solution also works for MySql Databases.
Error showing up as: 500 Internal Server Error ERROR

Solution: add a pool_pre_ping=True argument to the SQLAlchemy create engine line:

engine = create_engine("mysql+pymysql://user:pw@host/db", pool_pre_ping=True)