Aborted connections

[William Edwards]

Hello,

Since implementing SQLAlchemy, my logs have been filling up with 'Aborted connection' messages.

--

Aborted connection 294656 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets)

--

However, according to the documentation, my code should make SQLAlchemy close the connection neatly:

--

with self.engine.engine.connect() as connection:

    self.result = connection.execute(self.query)

--

self.engine.engine is whatever 'sa.create_engine' returns.

Why are these connections kept open anyway?

Thank you.

[Mike Bayer]

hi  -

*many* details are not here, database in use, driver, versions, etc.    Most people won't be able to help with a problem like this without basic details.

As it turns out I can tell you are using MySQL or MariaDB and the error is described at https://dev.mysql.com/doc/refman/5.7/en/communication-errors.html .

SQLAlchemy uses a connection pool, which is documented at https://docs.sqlalchemy.org/en/13/core/pooling.html .   so when you "close" a connection, it's usually just returned to the connection pool.    SQLAlchemy itself never just lets a connection fall away unless special steps are taken.   However, if the Python process is killed, then all those connections will produce this error on the server.     Additionally, if the engine itself that you got from create_engine() is allowed to be garbage collected (which is not a normal pattern), that will cause this to happen also.    if you are disposing an engine (which usually you shouldn't, unless your application handles an arbitrary number of distinct URLs), you want to call dispose() on it.

This message can also occur if you are using a service like HAProxy to proxy connections, it will produce this error in the server log when it drops connections since it doesn't speak the MySQL protocol.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f2107381-6e2b-4f2a-b28f-8b5c7eee8029n%40googlegroups.com.

[William Edwards]

Thanks for your response. So, when calling close() on an SQLAlchemy connection, it is returned to the connection pool, but the MySQL connection is not actually closed. That makes sense.

Could you elaborate on your comment about garbage collection? My application is short-lived: it sets up the engine, executes a few queries, and the script -and thus the Python process- ends. I'm not even sure pooling makes sense for my use case, but just so I understand: why shouldn't I call dispose() on the engine to close the MySQL connections, and instead rely on MySQL to close those connections? Isn't MySQL's connection close logic meant for misbehaving applications that don't properly close connections?

[Mike Bayer]

On Wed, Mar 10, 2021, at 5:39 AM, William Edwards wrote:

Thanks for your response. So, when calling close() on an SQLAlchemy connection, it is returned to the connection pool, but the MySQL connection is not actually closed. That makes sense.

Could you elaborate on your comment about garbage collection? My application is short-lived: it sets up the engine, executes a few queries, and the script -and thus the Python process- ends.

so yes you probably want to call .dispose() on the engine if you want to prevent these errors in your MySQL logs.  

I think MySQL is a bit unreasonable in that it logs these trivial disconnects so in our environments where we are using mariadb , we are turning down the log_warnings variable (https://mariadb.com/kb/en/error-log/#configuring-the-error-log-verbosity)  back to 1 which they changed in 10.2.4, but I understand that solution is not for everyone.   We are using HAProxy so we don't have too much option.

Another approach since your script is short lived is to disable pooling using NullPool: https://docs.sqlalchemy.org/en/14/core/pooling.html#switching-pool-implementations  that will just close out the connection when returned to the pool and won't pool anything.

I'm not even sure pooling makes sense for my use case, but just so I understand: why shouldn't I call dispose() on the engine to close the MySQL connections, and instead rely on MySQL to close those connections? Isn't MySQL's connection close logic meant for misbehaving applications that don't properly close connections?

probably more of a philosophical issue.  IMO it's not practical to consider idle connections that aren't in a transaction state or anything else to be "misbehaving" if they cleanly disconnect from a TCP perspective but don't emit special MySQL commands first.    But engine.dispose() at the end is certainly the most legit way to make sure the pool is closed out.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CALC%3Dt6-VW0kKh3sR3W0doP9mRjxgGsdd9LY7sgM7daEbtwCr5g%40mail.gmail.com.