SQLAlchemy connection dropping

Question

I wonder if someone had this issue before.

I have an application running with Python 3.7.3, SqlAlchemy and Falcon running in a Docker container.

My database is MariaDB also running in a docker container.

I setup SqlAlchemy pool_recycle to 3600 but it still drops connection from time to time.

I can't see anything in the logs but the SQLAlchemy log stating connection has been lost after trying to perform some query.

Where could be the issue? SQLAlchemy configuration? MariaDB? Maybe Docker? How can I inspect that?

My repository:

class NfeRepositorio:

    def __init__(self, db_session: sessionmaker):
        self.session_factory = scoped_session(db_session)
        self._session = None

    def __enter__(self):
        return self

    def __exit__(self, ex_type, ex_value, ex_traceback):
        self.session_factory.remove()

    @property
    def session(self):
        if not self._session:x
            self._session = self.session_factory()
        return self._session

My engine setup:

orm_engine = create_engine(
    get_config().ORM_ENGINE_TEMPLATE.format(
        get_config().DB_PROTOCOL,
        get_config().DB_USERNAME,
        get_config().DB_PASSWORD,
        get_config().DB_HOST,
        get_config().DB_NAME
    ),
    echo=get_config().ORM_ECHO,
    pool_recycle=3600
)

Edit 1: Just found a log entry and did some research on Google. Several people having the same issue. [Warning] Aborted connection to db (Got timeout reading communication packets)

Looks like the issue relates with the difference between SqlAlchemy session and MySql session. The SO post below explains better. How to close a SQLAlchemy session?

I'll give it a try and post the results.

is `NfeRepositorio` resource class? You can use middleware as per https://stackoverflow.com/questions/38863057/sqlalchemy-and-falcon-session-initialization — pnv, May 16 '19 at 11:54
@pnv what do you mean by resource class? I've implemented it as a context manager. Sorry but I didn't get what difference a middleware would make. I'm new to Python so there must be something I don't know yet. — Sidney de Moraes, May 16 '19 at 19:47
`resource` class is something from which you will manipulate data related to said resource. e.g. `TodoResource` class can be used to manipulate `todos`. Its `get` method provides a list of todos, `get/Id` provides a particular todo's details, and `post` method is used for creating a todo, `put` for updating a specific todo. — pnv, May 20 '19 at 13:32
Middleware allows you to determine what to do when a request comes or when a response needs to be returned. (e.g. `handle exception`, `get user session_id`, in your case `create db connection, and remove when request is processed`) — pnv, May 20 '19 at 13:38
@pnv oh ok. My resource imports `NfeRepositorio`. Anyway I'm starting to think that the issue is Docker related. There's nothing wrong with code apparently. — Sidney de Moraes, May 20 '19 at 15:24
@fedorqui that's exactly what was stated on the accepted answer. — Sidney de Moraes, Jun 13 '19 at 13:52
Sidney: yes, I saw it . And this is the reason I suggested to mark this one as a duplicate to the other one. — fedorqui, Jun 13 '19 at 13:53
@fedorqui got it. I added my vote too. Thanks for explaining. I was unaware of that option. — Sidney de Moraes, Jun 13 '19 at 13:54
You are more than welcome. Marking as a duplicate is a good way to centralise questions and their answers, so the info is not distributed through different but similar questions. — fedorqui, Jun 13 '19 at 13:56

score 2 · Accepted Answer · answered May 22 '19 at 15:13

The answer can be found here. There's a difference between a MySql session and a SQLAlchemy session. Closing a SqlAlchemy session doesn't necessarity close the MySql connection. More details can be found on the link provided or at the official documentation.

SQLAlchemy connection dropping

1 Answers1