StaleDataError/ObjectDeletedError

[lars van gemerden]

Hi, all

I am running into these 2 errors and have run out of ideas what to do about it (also because i don't what they mean); They seem to happen in exactly the same circumstances.

Traceback (most recent call last):

  File "C:\python27\lib\site-packages\bottle.py", line 781, in _handle

    return route.call(**args)

  File "C:\python27\lib\site-packages\bottle.py", line 1592, in wrapper

    rv = callback(*a, **ka)

  File "d:\Documents\Code\python\floware\server\webserver.py", line 202, in post_task

    task.from_json(request.json, path = pathlist([task._id_]))

  File "C:\python27\lib\contextlib.py", line 24, in __exit__

    self.gen.next()

  File "d:\Documents\Code\python\floware\models\flow\tasks.py", line 55, in scoped_session

    session.commit()

  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 721, in commit

    self.transaction.commit()

  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 354, in commit

    self._prepare_impl()

  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 334, in _prepare_impl

    self.session.flush()

  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 1818, in flush

    self._flush(objects)

  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 1936, in _flush

    transaction.rollback(_capture_exception=True)

  File "build\bdist.win32\egg\sqlalchemy\util\langhelpers.py", line 58, in __exit__

    compat.reraise(exc_type, exc_value, exc_tb)

  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 1900, in _flush

    flush_context.execute()

  File "build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py", line 372, in execute

    rec.execute(self)

  File "build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py", line 525, in execute

    uow

  File "build\bdist.win32\egg\sqlalchemy\orm\persistence.py", line 59, in save_obj

    mapper, table, update)

  File "build\bdist.win32\egg\sqlalchemy\orm\persistence.py", line 514, in _emit_update_statements

    (table.description, len(update), rows))

StaleDataError: UPDATE statement on table 'Company' expected to update 1 row(s); 0 were matched.

OR

File "d:\Documents\Code\python\floware\models\flow\processes.py", line 333, in run
  self.execute(input, output)
File "d:\Documents\Code\python\floware\toolshed\logs.py", line 55, in wrapper
  f_result = func(*v, **k)
File "d:\Documents\Code\python\floware\models\flow\libraries\basic.py", line 159, in execute
  print "%s %s" % (self.cursor, str(i.item))
File "d:\Documents\Code\python\floware\models\data\database.py", line 282, in __str__
  return '{%s-> %s}' %(type(self).__name__, ', '.join(['%s: %s' % (str(k), repr(getattr(self, k))) for k in self._entity_]))
File "C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\attributes.py", line 316, in __get__
File "C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\attributes.py", line 611, in get
File "C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\state.py", line 380, in __call__
File "C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\loading.py", line 606, in load_scalar_attributes
sqlalchemy.orm.exc.ObjectDeletedError: Instance '<Company at 0x5e0d550>' has been deleted, or its row is otherwise not present.

What i do is:

1 make a new object (mapped)-> obj1

2 add it to a session -> session1 

3 start another session -> session2 

4 do a get(id) on session2 (which should result in access to the database, since i just started the session) -> obj2 

5 close session2

6 do obj1.someattr.append(obj2)

7 do session1.commit()

8 get the first ERROR above 

OR

9 close session1

10 add obj1 to another session -> session3

11 do the returnb the string from __str__: return '{%s-> %s}' %(type(self).__name__, ', '.join(['%s: %s' % (str(k), repr(getattr(self, k))) for k in self._entity_])) (see stacktrace)

12 get the second ERROR above

I think thats all that could be relevant; if i don't do steps 3-6 there is no problem!

Can anyone point me in the right direction of what this error might mean, how to find the error or how to solve the problem?

Why would the first error not occur in all cases?

Cheers, Lars

[Michael Bayer]

On Jan 31, 2014, at 5:24 PM, lars van gemerden <la...@rational-it.com> wrote:

Hi, all

I am running into these 2 errors and have run out of ideas what to do about it (also because i don't what they mean); They seem to happen in exactly the same circumstances.

    mapper, table, update)

  File "build\bdist.win32\egg\sqlalchemy\orm\persistence.py", line 514, in _emit_update_statements

    (table.description, len(update), rows))

StaleDataError: UPDATE statement on table 'Company' expected to update 1 row(s); 0 were matched.

this means an object was meant to be UPDATEed via the ORM, however the row which is the target of the UPDATE is missing.  Either the primary key of this row changed somehow, or the row was deleted, *or* the row is not visible to your transaction (this seems to be your case).

Here’s the DELETE case:

sess = Session()

m1 = MyObject()

sess.add(m1)

sess.flush()

# out of band DELETE, ORM has no clue

sess.execute(“DELETE FROM my_object WHERE id=:id”, {“id”: m1.id})

# modify object

m1.foo = ‘bar’

# row is gone, boom

sess.flush()

File "C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\loading.py", line 606, in load_scalar_attributes

sqlalchemy.orm.exc.ObjectDeletedError: Instance '<Company at 0x5e0d550>' has been deleted, or its row is otherwise not present.

same idea, object is expired:

m1 = MyObject()

sess.add(m1)

# flush m1, also expire it

sess.commit()

# out of band DELETE, ORM has no clue

sess.execute(“DELETE FROM my_object WHERE id=:id”, {“id”: m1.id})

# row is gone, boom

print m1.foo

What i do is:

1 make a new object (mapped)-> obj1

2 add it to a session -> session1 

3 start another session -> session2 

4 do a get(id) on session2 (which should result in access to the database, since i just started the session) -> obj2 

5 close session2

6 do obj1.someattr.append(obj2)

7 do session1.commit()

8 get the first ERROR above 

this description isn’t specific enough to understand the issue.  What does “add it to a session” mean, did you flush that session?  was an INSERT emitted?  did you commit the transaction?  When you attempt to use “obj2” with obj1, you mean you are using the detached obj2 ?   What is the “id” you’re using get() on, is that the “id” that you know was created in #2 ?

Basically your problem comes down to sharing rows between transactions where those transactions aren’t aware of the rows you’re referring to (which is due to transaction isolation, see http://en.wikipedia.org/wiki/Transaction_isolation).  When you move an object between sessions, you should generally use merge() which will emit a SELECT for that row first.

[lars van gemerden]

Hi Michael,

With some more detail:

What i do is:

1 make a new object (mapped)-> obj1

2 create a scoped session (with context manager)-> session1

3 do session1.add(obj) 

4 create another scoped session -> session2 

5 do session2.query(someclass).get(some_id)->obj2 

6 close session2, no commit, no flush -> obj2 is detached (right?)

7 do obj1.someattr.append(obj2)

8 do session1.commit()

9 get the first ERROR above 

basically i use

    def Session( objs):

        session = session_maker()

        for obj in objs:

            if object_session(obj) is None:

                session.add(obj)

            else:

                session.merge(obj)

        return session

        

    @contextmanager

    def scoped_session(objs = [], commit = True):

        session = Session(objs)

        try:

            yield session

            if commit:

                session.commit()

        except:

            session.rollback()

            raise

        finally:

            session.close()

and essentially code description (1-8) above comes down to:

obj1 = cls1()

with scoped_session([obj1]) as session1:

        obj1.somefield = "somevalue"

        with scoped_session(commit = False) as session2:

                obj2 = session2.query(cls2).get(some_id)

        obj1.someattr.append(obj2)

        

if i just do:

with scoped_session([obj1]) as session1:

        obj1.somefield = "somevalue"

there is no problem.

Also:

"this means an object was meant to be UPDATEed via the ORM, however the row which is the target of the UPDATE is missing.  Either the primary key of this row changed somehow, or the row was deleted, *or* the row is not visible to your transaction (this seems to be your case)."

-  could the error also occur when the object was never committed to the database (which seems to be the case; the commit where the error occurs should be the first time the Company object is committed to the database)?

-  this seems to suggest that it is possible that a row is in the database, but that it is not visible to a transaction; is that possible?

As far as i know in the code that causes the problem, i do not do any deletes and i do not call flush myself.

Doing some more testing, now i get more of the second error in:

    def __str__(self): #in mapped class

        print object_session(self) is not None, has_identity(self) # True, True, <= OK

        print self.id #<= ERROR

        ......

with trace:

File "d:\Documents\Code\python\floware\models\flow\processes.py", line 333, in run
  self.execute(input, output)
File "d:\Documents\Code\python\floware\toolshed\logs.py", line 55, in wrapper
  f_result = func(*v, **k)
File "d:\Documents\Code\python\floware\models\flow\libraries\basic.py", line 159, in execute
  print "%s %s" % (self.cursor, str(i.item))

File "d:\Documents\Code\python\floware\models\data\database.py", line 281, in __str__
  print object_session(self), has_identity(self), self.id

File "C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\attributes.py", line 316, in __get__
File "C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\attributes.py", line 611, in get
File "C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\state.py", line 380, in __call__

File "C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\loading.py", line 606, in load_scalar_attributes

sqlalchemy.orm.exc.ObjectDeletedError: Instance '<Company at 0x5e4a3f0>' has been deleted, or its row is otherwise not present.

CL

[Michael Bayer]

On Jan 31, 2014, at 8:11 PM, lars van gemerden <la...@rational-it.com> wrote:

"this means an object was meant to be UPDATEed via the ORM, however the row which is the target of the UPDATE is missing.  Either the primary key of this row changed somehow, or the row was deleted, *or* the row is not visible to your transaction (this seems to be your case)."

-  could the error also occur when the object was never committed to the database (which seems to be the case; the commit where the error occurs should be the first time the Company object is committed to the database)?

sure

-  this seems to suggest that it is possible that a row is in the database, but that it is not visible to a transaction; is that possible?

absolutely, the link http://en.wikipedia.org/wiki/Transaction_isolation is relevant here

[lars van gemerden]

Thanks, this helps some to narrow it down.

Trying to zoom in:

- why would sqla try to UPDATE (instead of INSERT) a row in the database, when the row/object was never committed before?

- when you flush an object to the database and then close the session that flushed (no commit), what happens to the flushed data?

- if an object is in a session and it has_identity, why would accessing obj.id (id is the primary key) fail (see above)? 

- Is there (in principle) a problem with:

         + having an object of a mapped class which was never committed (but maybe was added to a session and flushed, after which the session was closed)

         + setting an attribute of that object with another object that was queried from the database

         + committing the first object to the database?

Cheers, Lars

[lars van gemerden]

Oh, on more question:

might there be anything inherently wrong with the scoped_session approach that i showed in the code snippets above?

CL

[Michael Bayer]

On Feb 1, 2014, at 7:25 AM, lars van gemerden <la...@rational-it.com> wrote:

Thanks, this helps some to narrow it down.

Trying to zoom in:

- why would sqla try to UPDATE (instead of INSERT) a row in the database, when the row/object was never committed before?

UPDATE is emitted for an object that is in the “persistent” state, that is, has an identity key.  you can view this key via inspect(myobject).key as well as inspect(myobject).has_identity.

- when you flush an object to the database and then close the session that flushed (no commit), what happens to the flushed data?

if you close the session, the session doesn’t commit the transaction, it releases the connection to the connection pool, and assuming the pool is used with its default settings a rollback() is emitted on that connection.

- if an object is in a session and it has_identity, why would accessing obj.id (id is the primary key) fail (see above)? 

i really don’t know, the code snippets you’re sending are long yet are not complete and therefore not runnable, and omit details that may be important.  if you want me to step through exactly what’s happening you need to give me a complete and self contained (and succinct!) test case.

- Is there (in principle) a problem with:

         + having an object of a mapped class which was never committed (but maybe was added to a session and flushed, after which the session was closed)

         + setting an attribute of that object with another object that was queried from the database

         + committing the first object to the database?

yeah, if that first object was flushed, then its session would have considered it to be persistent, yet if the session were not committed but the now detached object still used, it essentially represents a bogus row.

[Michael Bayer]

On Feb 1, 2014, at 9:01 AM, lars van gemerden <la...@rational-it.com> wrote:

> Oh, on more question:
>
> might there be anything inherently wrong with the scoped_session approach that i showed in the code snippets above?

the code which illustrates the @contextmanager and the “def Session()” looks error-prone and entirely awkward, and the rationale for such a context manager isn’t apparent.

It appears to be mixing the intent of wishing to share random rows between multiple sessions (a bad idea) while at the same time trying to conceal the details of how a delicate operation like that is performed (it guesses whether add() or merge() should be used, etc). It also seems to mix the concerns of dealing with object mechanics and session creation at the same time which are typically two different concerns, not to mention that it has a complex system of committing or not committing using flags which makes it unsurprising that you’re seeing non-existent rows show up in other transactions.

So yeah, if it were me, I’d definitely try to approach whatever the problem is it’s trying to solve in a different way, one which preferably sticks to the patterns outlined in the ORM tutorial as much as possible (e.g. one session at a time, load/manipulate objects, commit(), throw everything away). Those points at which an application actually uses two sessions at once, or transfers objects between them, should be very isolated cases with very explicit mechanics and clear rationale why this operation is needed in this specific case (where typical cases are: sending objects into worker threads or processes, moving objects in and out of caching layers, or running two transactions simultaneously so that one can commit and the other roll back, such as a transaction writing to an application history table). The app wouldn’t have a generic “here’s one of those places we need to use two sessions with a specific target object to pull out of one of them” use case such that a context manager is needed, there should be extremely few places where that kind of thing goes on.

The kind of issue you’re hitting is exactly the one I talk about in detail in my talk, “The SQLAlchemy Session in Depth”: http://techspot.zzzeek.org/2012/11/14/pycon-canada-the-sqlalchemy-session-in-depth/ Page 18 of the PDF illustrates a slide “Lack of Behavioral Constraints Creates Confusion” illustrating an anti-pattern similar to the one I think we’re seeing here.

[lars van gemerden]

Ok, Michael,

That helped a lot, what i have done is (for future reference/others);

1) turned of autoflush on all sessions,

2) shortened the lifespan of session to a minimum,

3) removed the "if object_session(obj): session.merge(obj)" option in the Session() function,

This seems to have solved the problem for now (needs more testing)!

Thanks for the link to the talk as well.

Cheerio, Lars

[Staszek]

On 2014-02-01 00:09, Michael Bayer wrote:
>
> On Jan 31, 2014, at 5:24 PM, lars van gemerden <la...@rational-it.com

> <mailto:la...@rational-it.com>> wrote:
>
>> Hi, all
>>
>> I am running into these 2 errors and have run out of ideas what to do
>> about it (also because i don't what they mean); They seem to happen in
>> exactly the same circumstances.
>>
>> mapper, table, update)
>> File "build\bdist.win32\egg\sqlalchemy\orm\persistence.py", line
>> 514, in _emit_update_statements
>> (table.description, len(update), rows))
>> StaleDataError: UPDATE statement on table 'Company' expected to update
>> 1 row(s); 0 were matched.
>
> this means an object was meant to be UPDATEed via the ORM, however the
> row which is the target of the UPDATE is missing. Either the primary
> key of this row changed somehow, or the row was deleted, *or* the row is
> not visible to your transaction (this seems to be your case).

I am running into something very similar:

StaleDataError: UPDATE statement on table 'buildings' expected to update

1 row(s); 0 were matched.

However this case is very simple, just a single row in a table, and it
looks to get selected just before the update (in the same transaction).
Nothing is being deleted.

The update:

UPDATE buildings SET glatitude=%s, glongitude=%s WHERE buildings.id = %s

The columns:

id = Column(BigInteger, autoincrement=True, primary_key=True)
glatitude = Column(Numeric(precision=11, scale=8))
glongitude = Column(Numeric(precision=11, scale=8))

What's interesting is that the error occurs only if glatitude and
glongitude are being updated to the same values that are already in the
database (i.e.: no real update).
When the old values are NULL or otherwise different than the new values,
everything works fine!

Is this a bug?

This is SQLAlchemy 0.9.1:

File "[...]/sqlalchemy/orm/session.py", line 765, in commit
self.transaction.commit()
File "[...]/sqlalchemy/orm/session.py", line 370, in commit
self._prepare_impl()
File "[...]/sqlalchemy/orm/session.py", line 350, in _prepare_impl
self.session.flush()
File "[...]/sqlalchemy/orm/session.py", line 1879, in flush
self._flush(objects)
File "[...]/sqlalchemy/orm/session.py", line 1997, in _flush
transaction.rollback(_capture_exception=True)
File "[...]/sqlalchemy/util/langhelpers.py", line 57, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "[...]/sqlalchemy/orm/session.py", line 1961, in _flush
flush_context.execute()
File "[...]/sqlalchemy/orm/unitofwork.py", line 370, in execute
rec.execute(self)
File "[...]/sqlalchemy/orm/unitofwork.py", line 523, in execute
uow
File "[...]/sqlalchemy/orm/persistence.py", line 59, in save_obj
mapper, table, update)
File "[...]/sqlalchemy/orm/persistence.py", line 528, in
_emit_update_statements
(table.description, len(update), rows))
StaleDataError: UPDATE statement on table 'buildings' expected to update

1 row(s); 0 were matched.

--
http://people.eisenbits.com/~stf/
http://www.eisenbits.com/

OpenPGP: 80FC 1824 2EA4 9223 A986 DB4E 934E FEA0 F492 A63B

[Mike Bayer]

It's unlikely to be a SQLAlchemy bug, but questions to be answered
include: Can you produce a self-contained test case? Have you tried
SQLAlchemy 0.9.6 rather than 0.9.1? Also what backend is this, MySQL?
MySQL has had some bugs where the rowcount is not reported correctly.

[Staszek]

Not really, no, yes.

The problem seems to have been with assignment of float values. It was
like this:

base.py:847: UPDATE buildings SET glatitude=%s, glongitude=%s WHERE
buildings.id = %s
base.py:849: (52.0210673, 20.2152834, 1)

It can't be seen here, but then I guess those float values were being
converted by SQLAlchemy to decimals like:

Decimal('52.021067299999998567727743647992610931396484375')

which was not equal to the value stored in the column because it has
lower precision.

However after converting those new values to Decimal *and* quantizing:

d = Decimal('0.00000001')
glat = Decimal.from_float(glat).quantize(d)
glon = Decimal.from_float(glon).quantize(d)

the new Decimals are equal to the old Decimals and I can see no UPDATE
in the log.

Not sure how it exactly works on SQLAlchemy side but perhaps it could
make sense to take the column precision into consideration when
comparing decimals on assignment/update.

[Michael Bayer]

On Jul 12, 2014, at 4:49 AM, Staszek <stf.lis...@eisenbits.com> wrote:

>
> Not really, no, yes.
>
> The problem seems to have been with assignment of float values. It was
> like this:
>
> base.py:847: UPDATE buildings SET glatitude=%s, glongitude=%s WHERE
> buildings.id = %s
> base.py:849: (52.0210673, 20.2152834, 1)
>
> It can't be seen here, but then I guess those float values were being
> converted by SQLAlchemy to decimals like:
>
> Decimal('52.021067299999998567727743647992610931396484375’)

the Numeric type deals with Decimal objects. add usedecimal=False to deal with floats. It only does a float <-> Decimal conversion if the backend doesn’t support it.

>
> which was not equal to the value stored in the column because it has
> lower precision.
>
> However after converting those new values to Decimal *and* quantizing:
>
> d = Decimal('0.00000001')
> glat = Decimal.from_float(glat).quantize(d)
> glon = Decimal.from_float(glon).quantize(d)
>
> the new Decimals are equal to the old Decimals and I can see no UPDATE
> in the log.
>
> Not sure how it exactly works on SQLAlchemy side

it doesn’t emit an UPDATE when the value that was loaded matches the value that it was modified to, using ==.

> but perhaps it could
> make sense to take the column precision into consideration when
> comparing decimals on assignment/update.

See decimal_return_scale at http://docs.sqlalchemy.org/en/rel_0_9/core/types.html?highlight=numeric#sqlalchemy.types.Numeric.__init__.

However, consider that the MySQL driver can be involved in decimal conversion operations as well; I’m not sure offhand which side does what in the case of MySQL.

If decimal accuracy is of any concern, you should be using Decimal objects fully, never a Python float, and if you are using a Numeric without asdecimal=False this is more appropriate.

None of this has anything to do with your original issue with the UPDATE not matching any rows, however. The UPDATE here is not matching on the integer primary key.

[Michael Bayer]

On Jul 12, 2014, at 8:17 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:

> If decimal accuracy is of any concern, you should be using Decimal objects fully, never a Python float, and if you are using a Numeric without asdecimal=False this is more appropriate.
>

sorry, asdecimal=True, use Decimal objects, asdecimal=False, use float objects.