it's likely that a database connection is being returned to the pool in an invalid state.
Switching to NullPool temporarily might reveal that this solves all the issues ; at the very least, I would try setting pool_recycle to a low number, like 5 minutes, however this won't prevent the problem, just make it less likely. What you do need to find are stack traces that precede the error, to give a clue why a connection would be placed in the pool in a bad state. The other possibility is that your application is actually sharing a single connection across threads in some way which would be a different problem though with a lot of similar behaviors. Are there any global in-memory caches being used of objects where an ORM object might be shared out among threads, or a background worker thread of some kind, anything like that ? Does the application use a "scoped_session" pattern and maybe the session being passed around in some cases isn't actually scoped? On Fri, Apr 12, 2019 at 4:03 AM tonthon <tontho...@gmail.com> wrote: > > > Le 10/04/2019 à 17:12, Mike Bayer a écrit : > > On Wed, Apr 10, 2019 at 9:23 AM tonthon <tontho...@gmail.com> wrote: > > Hi, > > We're using sqlalchemy in a Pyramid Web Application. > > We use the ZopeTransactionExtension and our session factory is initialized > this way : > > DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) > > We use the pyramid_tm that wraps each web requests in a transaction. > > Our services are served through apache and mod_wsgi (1 process, 10 threads). > > > We recently faced the following error : > > 193.253.40.35 - 2019-04-08 09:29:23,054 ERROR Exception during reset or > similar ([sqlalchemy.pool.QueuePool._finalize_fairy:721]) > Traceback (most recent call last): > File > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", > line 712, in _finalize_fairy > fairy._reset(pool) > File > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", > line 881, in _reset > self._reset_agent.rollback() > File > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 1632, in rollback > self._do_rollback() > File > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 1670, in _do_rollback > self.connection._rollback_impl() > File > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 706, in _rollback_impl > self._handle_dbapi_exception(e, None, None, None, None) > File > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 1413, in _handle_dbapi_exception > exc_info > File > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", > line 265, in raise_from_cause > reraise(type(exception), exception, tb=exc_tb, cause=cause) > File > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > line 704, in _rollback_impl > self.engine.dialect.do_rollback(self.connection) > File > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", > line 1804, in do_rollback > dbapi_connection.rollback() > ProgrammingError: (_mysql_exceptions.ProgrammingError) (2014, "Commands out > of sync; you can't run this command now") (Background on this error at: > http://sqlalche.me/e/f405) > > > I can't reproduce this problem that seems to happen randomly. > > Could anyone help me giving some points to investigate ? > > Is there some tuning to do (I already set the pool_recycle var) ? > > the most important thing is what driver are you using and what version? > > are you doing anything with SAVEPOINTs or greenlets / eventlet ? > > is the web application experiencing timeouts of some kind, such that > transactions are being dropped ? > > is the above error only showing up in logs or is it occurring > synchronously with a web request and causing the request to fail ? > > > We use mysqlclient 1.4.1. > > We don't use any greenlet or eventlet nor savepoints. > > I havn't seen any timeout happening so far. > > The given error is happening during the request's lifecycle resulting in a > HTTP 500 error code > > After the error, the user refreshes and it works. > > I don't know if it could be related, but we also see things like > "NoSuchColumnError: "Could not locate column in row for column 'count(*)'". > > > > Thanks in advance > > Best regards > > Gaston > > -- > 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+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > > -- > 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+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
