On Tue, Apr 16, 2019 at 4:49 AM tonthon <tontho...@gmail.com> wrote: > > Celery tasks are using the same scoped_session factory, could it cause the > errors we're facing here ?
what's important is if these tasks run in the same process or not ? ( i thought celery runs as a separate service?) If you are passing ORM objects which are associated with a thread local Session in process to a Celery task queue which is running distinct threads in the same process, then yes that will exactly lead to this issue and needs to be fixed. You can't share a persistent (meaning, associated with a Session) ORM loaded object with another thread, because that means you are sharing the whole Session across threads. The object needs to be detached first (e.g. session.expunge(obj), or just session.close()) , or the celery thread needs to get just the primary keys and load the objects on its own. Another option is to merge() the objects into the Session to be used by the celery worker but this also has to be done carefully so that neither Session emits SQL on the wrong thread. > > > Le 15/04/2019 à 15:39, Mike Bayer a écrit : > > On Mon, Apr 15, 2019 at 5:41 AM tonthon <tontho...@gmail.com> wrote: > > I tried to set a lower value for the pool_recycle value and it seems to work. > > There is a celery service running in the background, maybe it could affect > the session management. > > this will reduce the problem but the architectural issue that is > causing it is likely still present. I'd want to look at how the > interaction with Celery is occurring within the same process. > > > Le 12/04/2019 à 15:58, Mike Bayer a écrit : > > 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. > > > -- > 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.
