The modeltests/serializer unit test case fails due to missing author record with id=4

[egenix_viktor <viktor@…>]

There is the following test case in modeltests.serializers:

for obj in serializers.deserialize("json", json):
    obj.save()

JSON data (split to multiple lines by hand):

[{"pk": 3, "model": "serializers.article", "fields": {
        "headline": "Forward references pose no problem",
        "pub_date": "2006-06-16 15:00:00",
        "categories": [4, 1],
        "author": 4}},
 {"pk": 4, "model": "serializers.category", "fields": {
        "name": "Reference"}},
 {"pk": 4, "model": "serializers.author", "fields": {
        "name": "Agnes"}}
]

It generates SQL like the following to insert the first record (actual SQL depends on the backend, but that's not relevant to the problem):

INSERT INTO serializers_article
    (id, author_id, headline, pub_date)
VALUES (%s, %s, %s, %s);

Actual parameters passed:

(3, 4, u'Forward references pose no problem', u'2006-06-16 15:00:00')

There is a foreign key constraint for serializers_article.author_id pointing to serializers_author.id. The above SQL tries to insert author_id = 4, but there is no serializers_author record with id = 4. Since there is only 3 authors inserted before, so it is normal that no author exists with id = 4. It causes IntegrityError to be raised if the database server enforces foreign key constraints, otherwise this bug can left unnoticed.

NOTE: The unit test passes without error when using SQLite, since foreign key constaints are note enforced. You need to test with a backend that enforces foreign key constraints to reproduce this bug.

[Russell Keith-Magee]

This is a known problem with the MySQL database backend when using InnoDB tables. For some reason, InnoDB can't defer row referential integrity checks to the end of a transaction boundary, so you can't insert a forward reference. This test case works fine under Oracle and Postgres, which both implement row referential integrity properly.

[egenix_viktor <viktor@…>]

Thanks for your very quick response. :-)

I got this error while using MSSQL2005 through pyodbc as the DB backend (third party), but it is not related to the backend at all. So MSSQL has this problem as well. I might try to figure out if changing a configuration setting or connection option in MSSQL could fix this somehow.

[anonymous]

Replying to russellm:

This is a known problem with the MySQL database backend when using InnoDB tables. For some reason, InnoDB can't defer row referential integrity checks to the end of a transaction boundary, so you can't insert a forward reference. This test case works fine under Oracle and Postgres, which both implement row referential integrity properly.

The bug you referred to is #3615