'MySQL server has gone away' on closing connection

[Tim Tisdall]

Today I found the following traceback in my logs:

2014-03-27 13:55:59,876 ERROR [sqlalchemy.pool.QueuePool _close_connection b'uWSGIWorker2Core14'] Exception closing connection <oursql.Connection object at 0x7fecfdf6a140>

Traceback (most recent call last):

  File "/sites/ColanderAlchemy/SQLAlchemy-0.9.3-py3.3-linux-x86_64.egg/sqlalchemy/pool.py", line 243, in _close_connection

    self._dialect.do_close(connection)

  File "/sites/ColanderAlchemy/SQLAlchemy-0.9.3-py3.3-linux-x86_64.egg/sqlalchemy/engine/default.py", line 401, in do_close

    dbapi_connection.close()

  File "connection.pyx", line 193, in oursql.Connection.close (oursqlx/oursql.c:6143)

  File "connection.pyx", line 240, in oursql.Connection.rollback (oursqlx/oursql.c:6562)

  File "connection.pyx", line 207, in oursql.Connection._raise_error (oursqlx/oursql.c:6317)

oursql.OperationalError: (2006, 'MySQL server has gone away', None)

Shouldn't SQLAlchemy closing a connection catch this type of exception and ignore it?  If the mysql connection "has gone away", can it not be considered "closed"?

-Tim

[Michael Bayer]

there’s a difference between “ignore” and “silent”. Here’s the code:

def _close_connection(self, connection):
self.logger.debug("Closing connection %r", connection)
try:
self._dialect.do_close(connection)
except (SystemExit, KeyboardInterrupt):
raise
except:
self.logger.error("Exception closing connection %r",
connection, exc_info=True)

as you can see, the error is ignored, we just log that something unexpected and possibly problematic has happened. I don’t think this is necessarily a bad thing.

[Tim Tisdall]

Ah, yeah, that seems like a reasonable way of handling it.

I just don't get why I'm getting those exceptions, though, as I have it set to recycle pool connections every 4 hrs where the mysql setting is to expire connections after 8hrs.  As far as I understand it, I shouldn't be getting messages like that.  Or are the pool connections "recycled" just before the next use and not after 4hrs elapse?

-Tim

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/K9Pk2pXbLgQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Michael Bayer]

what this looks like is that the connection is closed by the server due to the MySQL timeout, then SQLAlchemy’s pool, when accessed, goes to recycle those connections, and does a close() on the existing one.  Since they’ve been closed server side, the close() method raises.      The pool recycle feature of course otherwise knows nothing about those “stale” connections and can’t assume they’re already closed, it just knows that connection has to be closed out.

   

I can see how this particular exception would be annoying and I’m not sure if other DBAPIs besides OurSQL raise under this condition.   I have a feeling I’d have heard about this sooner if MySQLdb did it also.   Under the realm of “we’re closing a connection we know to have been invalidated and/or stale”, it seems like we’d totally suppress even any printing of the error because we already know this connection is suspect.   But at the same time, it always makes me nervous to 100% ignore any error message entirely.     Here it might be nice if there wasn’t the whole stack trace, and maybe if the message included context, “exception closing stale/invalidated connection ignored: x”.

if you want to propose a pullreq along those lines that could be helpful (e.g. pass a flag into the _close_connection()  routine indicating the style of error reporting).

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.