Note also the objects can be copied using copy.deepcopy(), or pickle
loads()/dumps(), before sending them to the other Session. This
might not have worked in older Python / SQLAlchemy versions but
deepcopy() seems to rely upon the pickle internals e.g. __setstate__,
which in SQLAlchemy will correctly construct the copied instance not
linked to any Session.
Here's a demo:
import copy
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import object_session
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(String)
bs = relationship("B")
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey("a.id"))
data = Column(String)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s1 = Session(e, expire_on_commit=False)
a1 = A(data='olddata', bs=[B(data='olddata')])
s1.add(a1)
s1.commit()
a2 = copy.deepcopy(a1)
assert a1 in s1
assert a1.bs[0] in s1
assert a2 not in s1
assert a2.bs[0] not in s1
assert object_session(a2) is None
assert object_session(a2.bs[0]) is None
s2 = Session(e)
s2.add(a2)
a2.data = 'new data'
a2.bs[0].data = 'new data'
s2.commit()
On Tue, Apr 16, 2019 at 9:41 AM Mike Bayer <[email protected]> wrote:
>
> On Tue, Apr 16, 2019 at 4:49 AM tonthon <[email protected]> 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 <[email protected]> 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 <[email protected]> wrote:
> >
> > Le 10/04/2019 à 17:12, Mike Bayer a écrit :
> >
> > On Wed, Apr 10, 2019 at 9:23 AM tonthon <[email protected]> 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 [email protected].
> > To post to this group, send email to [email protected].
> > 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 [email protected].
> > To post to this group, send email to [email protected].
> > 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 [email protected].
> > To post to this group, send email to [email protected].
> > 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 [email protected].
> > To post to this group, send email to [email protected].
> > 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
