Code

Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#9227 closed (invalid)

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

Reported by: egenix_viktor <viktor@…> Owned by: nobody
Component: Database layer (models, ORM) Version: 1.0
Severity: Keywords: unit test foreign key constraint IntegrityError
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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.

Attachments (0)

Change History (3)

comment:1 follow-up: Changed 6 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to invalid
  • Status changed from new to closed

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.

comment:2 Changed 6 years ago by 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.

comment:3 in reply to: ↑ 1 Changed 6 years ago by 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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.