move of object reference fails when foreign key part of the primary key

In the attached code, each Sample has a Status at a particular timepoint. Status can be accessed from Sample via a column_mapped_collection keyed on the timepoint (e.g. 24 hrs). Trying to set the status initially works fine, something like sample.statuses24.status = foo. But trying to update it later to "bar" fails, because the UPDATE statement includes the new value "bar" in the WHERE clause; since the database still says "foo", this matches zero rows, resulting in a ConcurrentModificationError.

This code works correctly with 0.5.8 and earlier; it fails with 0.6.1, 0.6.2, and 0.6.3. It fails with 0.6.x using MySQL 5.0 and using SQLite.

Logged SQL is below; example code is attached. My email is iwd32900 AT yahoo.com.

Thanks! Ian

2010-07-21 21:15:44,354 INFO sqlalchemy.engine.base.Engine.0x...f9ec PRAGMA table_info("sample")
2010-07-21 21:15:44,354 INFO sqlalchemy.engine.base.Engine.0x...f9ec ()
2010-07-21 21:15:44,355 INFO sqlalchemy.engine.base.Engine.0x...f9ec PRAGMA table_info("status")
2010-07-21 21:15:44,355 INFO sqlalchemy.engine.base.Engine.0x...f9ec ()
2010-07-21 21:15:44,355 INFO sqlalchemy.engine.base.Engine.0x...f9ec PRAGMA table_info("sample__status")
2010-07-21 21:15:44,355 INFO sqlalchemy.engine.base.Engine.0x...f9ec ()
2010-07-21 21:15:44,356 INFO sqlalchemy.engine.base.Engine.0x...f9ec 
CREATE TABLE sample (
    id INTEGER NOT NULL, 
    "index" INTEGER, 
    PRIMARY KEY (id)
)


2010-07-21 21:15:44,356 INFO sqlalchemy.engine.base.Engine.0x...f9ec ()
2010-07-21 21:15:44,357 INFO sqlalchemy.engine.base.Engine.0x...f9ec COMMIT
2010-07-21 21:15:44,357 INFO sqlalchemy.engine.base.Engine.0x...f9ec 
CREATE TABLE status (
    id INTEGER NOT NULL, 
    name VARCHAR(50) NOT NULL, 
    PRIMARY KEY (id), 
    UNIQUE (name)
)


2010-07-21 21:15:44,357 INFO sqlalchemy.engine.base.Engine.0x...f9ec ()
2010-07-21 21:15:44,358 INFO sqlalchemy.engine.base.Engine.0x...f9ec COMMIT
2010-07-21 21:15:44,358 INFO sqlalchemy.engine.base.Engine.0x...f9ec 
CREATE TABLE sample__status (
    sample_id INTEGER NOT NULL, 
    status_id INTEGER NOT NULL, 
    timepoint_hours FLOAT, 
    PRIMARY KEY (sample_id, status_id, timepoint_hours), 
    FOREIGN KEY(sample_id) REFERENCES sample (id) ON DELETE CASCADE, 
    FOREIGN KEY(status_id) REFERENCES status (id) ON DELETE CASCADE
)


2010-07-21 21:15:44,358 INFO sqlalchemy.engine.base.Engine.0x...f9ec ()
2010-07-21 21:15:44,358 INFO sqlalchemy.engine.base.Engine.0x...f9ec COMMIT
2010-07-21 21:15:44,359 INFO sqlalchemy.engine.base.Engine.0x...f9ec CREATE INDEX ix_sample__status_timepoint_hours ON sample__status (timepoint_hours)
2010-07-21 21:15:44,359 INFO sqlalchemy.engine.base.Engine.0x...f9ec ()
2010-07-21 21:15:44,359 INFO sqlalchemy.engine.base.Engine.0x...f9ec COMMIT
2010-07-21 21:15:44,360 INFO sqlalchemy.engine.base.Engine.0x...f9ec CREATE INDEX ix_sample__status_status_id ON sample__status (status_id)
2010-07-21 21:15:44,360 INFO sqlalchemy.engine.base.Engine.0x...f9ec ()
2010-07-21 21:15:44,361 INFO sqlalchemy.engine.base.Engine.0x...f9ec COMMIT
2010-07-21 21:15:44,361 INFO sqlalchemy.engine.base.Engine.0x...f9ec CREATE INDEX ix_sample__status_sample_id ON sample__status (sample_id)
2010-07-21 21:15:44,361 INFO sqlalchemy.engine.base.Engine.0x...f9ec ()
2010-07-21 21:15:44,361 INFO sqlalchemy.engine.base.Engine.0x...f9ec COMMIT
2010-07-21 21:15:44,364 INFO sqlalchemy.engine.base.Engine.0x...f9ec BEGIN
2010-07-21 21:15:44,365 INFO sqlalchemy.engine.base.Engine.0x...f9ec INSERT INTO sample ("index") VALUES (?)
2010-07-21 21:15:44,366 INFO sqlalchemy.engine.base.Engine.0x...f9ec (3,)
2010-07-21 21:15:44,373 INFO sqlalchemy.engine.base.Engine.0x...f9ec INSERT INTO status (name) VALUES (?)
2010-07-21 21:15:44,374 INFO sqlalchemy.engine.base.Engine.0x...f9ec ('A',)
2010-07-21 21:15:44,377 INFO sqlalchemy.engine.base.Engine.0x...f9ec INSERT INTO status (name) VALUES (?)
2010-07-21 21:15:44,377 INFO sqlalchemy.engine.base.Engine.0x...f9ec ('B',)
2010-07-21 21:15:44,377 INFO sqlalchemy.engine.base.Engine.0x...f9ec COMMIT
2010-07-21 21:15:44,378 INFO sqlalchemy.engine.base.Engine.0x...f9ec BEGIN
2010-07-21 21:15:44,379 INFO sqlalchemy.engine.base.Engine.0x...f9ec SELECT sample.id AS sample_id, sample."index" AS sample_index 
FROM sample 
WHERE sample.id = ?
2010-07-21 21:15:44,379 INFO sqlalchemy.engine.base.Engine.0x...f9ec (1,)
2010-07-21 21:15:44,380 INFO sqlalchemy.engine.base.Engine.0x...f9ec SELECT sample__status.sample_id AS sample__status_sample_id, sample__status.status_id AS sample__status_status_id, sample__status.timepoint_hours AS sample__status_timepoint_hours 
FROM sample__status 
WHERE ? = sample__status.sample_id
2010-07-21 21:15:44,380 INFO sqlalchemy.engine.base.Engine.0x...f9ec (1,)
2010-07-21 21:15:44,385 INFO sqlalchemy.engine.base.Engine.0x...f9ec SELECT status.id AS status_id, status.name AS status_name 
FROM status 
WHERE status.id = ?
2010-07-21 21:15:44,386 INFO sqlalchemy.engine.base.Engine.0x...f9ec (1,)
2010-07-21 21:15:44,387 INFO sqlalchemy.engine.base.Engine.0x...f9ec INSERT INTO sample__status (sample_id, status_id, timepoint_hours) VALUES (?, ?, ?)
2010-07-21 21:15:44,387 INFO sqlalchemy.engine.base.Engine.0x...f9ec (1, 1, 0.0)
2010-07-21 21:15:44,387 INFO sqlalchemy.engine.base.Engine.0x...f9ec COMMIT
2010-07-21 21:15:44,388 INFO sqlalchemy.engine.base.Engine.0x...f9ec BEGIN
2010-07-21 21:15:44,397 INFO sqlalchemy.engine.base.Engine.0x...f9ec SELECT status.id AS status_id, status.name AS status_name 
FROM status 
WHERE status.id = ?
2010-07-21 21:15:44,398 INFO sqlalchemy.engine.base.Engine.0x...f9ec (2,)
2010-07-21 21:15:44,399 INFO sqlalchemy.engine.base.Engine.0x...f9ec SELECT sample__status.sample_id AS sample__status_sample_id, sample__status.status_id AS sample__status_status_id, sample__status.timepoint_hours AS sample__status_timepoint_hours 
FROM sample__status 
WHERE sample__status.sample_id = ? AND sample__status.status_id = ? AND sample__status.timepoint_hours = ?
2010-07-21 21:15:44,399 INFO sqlalchemy.engine.base.Engine.0x...f9ec (1, 1, 0.0)
2010-07-21 21:15:44,404 INFO sqlalchemy.engine.base.Engine.0x...f9ec UPDATE sample__status SET status_id=? WHERE sample__status.sample_id = ? AND sample__status.status_id = ? AND sample__status.timepoint_hours = ?
2010-07-21 21:15:44,404 INFO sqlalchemy.engine.base.Engine.0x...f9ec (2, 1, 2, 0.0)
2010-07-21 21:15:44,405 INFO sqlalchemy.engine.base.Engine.0x...f9ec ROLLBACK
Traceback (most recent call last):
  File "sqla_06x_update_bug.py", line 81, in <module>
    sess.commit()
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/session.py", line 653, in commit
    self.transaction.commit()
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/session.py", line 364, in commit
    self._prepare_impl()
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/session.py", line 348, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/session.py", line 1346, in flush
    self._flush(objects)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/session.py", line 1427, in _flush
    flush_context.execute()
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 299, in execute
    rec.execute(self)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 443, in execute
    uow
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/mapper.py", line 1813, in _save_obj
    (rows, len(update)))
sqlalchemy.orm.exc.ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1

Comments (4)