MySQL/InnoDB has integrity problems with new deletion code
|Reported by:||Russell Keith-Magee||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||1.2|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
It looks like MySQL/InnoDB's inability to defer constraints poses problems
The problem is revealed by the queries.SubclassFKTests regression test. In that test:
TVChef -- is a -- Celebrity -- has many -- fans.
The test deletes a TVChef. In deleting the TVChef, the deletion code deletes the fan objects, then the celebrity... which leaves TVChef as a dangling reference. Under Postgres, the reference check is deferred until the end of the transaction (as it should be); but InnoDB throws a hissy because it reference checks after every statement.
In this case, the problem can be resolved by deleting in a different order. However, I'm not sure if that will yield a general solution.