Delete failing with StaleDataError

[Fayaz Yusuf Khan]

The attached script fails with this:
Traceback (most recent call last):
File "stale_delete.py", line 33, in <module>
session.flush()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py",
line 1559, in flush
self._flush(objects)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py",
line 1630, in _flush
flush_context.execute()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py",
line 331, in execute
rec.execute(self)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py",
line 498, in execute
uow
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line
2507, in _delete_obj
(table.description, len(del_objects), c.rowcount)
sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'A' expected to
delete 2 row(s); 1 were matched.

in MySQL-InnoDB (works in SQLite and Postgres).
Tried versions 0.7.3 and 0.7.5
Python 2.7

I tried manually deleting the rows from the table through MySQL client and
noticed that it's not returning an accurate row count (doesn't
'supports_sane_rowcount') for a table with an adjacency relationship and an
ondelete='CASCADE'.

I'm going to work around this by adding another ondelete='CASCADE' for User->A
--
Fayaz Yusuf Khan
Cloud developer and architect
Dexetra SS, Bangalore, India
fayaz.yusuf.khan_AT_gmail_DOT_com
fayaz_AT_dexetra_DOT_com
+91-9746-830-823

[Michael Bayer]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I ran this and indeed InnoDB appears to be broken here. This is a MySQL bug. It would appear it is running the cascade between the two "A" rows and only considering the lead object to be the one deleted. Can't exactly find it at bugs.mysql.com either, so you'd do everyone a favor if you could create a ticket over there.

SQLAlchemy can try to work around this but for now you can just flip supports_sane_rowcount off for the whole dialect:

engine.dialect.supports_sane_rowcount = engine.dialect.supports_sane_multi_rowcount = False

The only solution I can see is another mapper flag that lets you disable the rowcount check for specific classes (see http://www.sqlalchemy.org/trac/ticket/2403) . Unfortunately I don't see a way to make this very obvious to users not aware of the issue.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (Darwin)
Comment: GPGTools - http://gpgtools.org

iQEcBAEBAgAGBQJPNon9AAoJEDMCOcHE2v7hVeIH/0HFDYCLUiKH6vuRvEvCapjD
XrCwAlpjbu6dCE82YNn00oSvekh0QfFgDgIFafnZzavtE1dn2ve9HvDBviUmmE+h
gpMuhVayAZxZfOAYu8512hSb9rx/ZHQv+wt/e4Rl3pnbBGLq1Jg8bufd+S0Ytkwx
uIktgEQjfEIRJIfBK0/j+xp8y8lVo/2lFA8or/WdLulgepddgOdzEI6RTkUk45pl
Cuq8VVMc49BQaCcgmc0aFmZ9lrhopKii2/31HRca384sk358Otm+9sbomKNdBZbQ
QRh+LhqUL53Rgi+69d2vWPfGZMkvE6q4DI89qVdVlzuh1SXI62vwXTRb/rEuEe4=
=Q+h5
-----END PGP SIGNATURE-----

[Fayaz Yusuf Khan]

On Saturday 11 Feb 2012 10:32:12 AM Michael Bayer wrote:
> I ran this and indeed InnoDB appears to be broken here. This is a MySQL
> bug. It would appear it is running the cascade between the two "A" rows
> and only considering the lead object to be the one deleted. Can't
> exactly find it at bugs.mysql.com either, so you'd do everyone a favor if
> you could create a ticket over there.

I've raised this on lists.mysql.

>
> SQLAlchemy can try to work around this but for now you can just flip
> supports_sane_rowcount off for the whole dialect:

Thanks, that would be a lot better than writing a new migrate script.