#3615: Can't define forward references in fixtures using MySQL with InnoDB
-------------------------------------+-------------------------------------
Reporter: russellm | Owner: nobody
Type: Bug | Status: new
Milestone: | Component: Database layer
Version: SVN | (models, ORM)
Resolution: | Severity: Normal
Triage Stage: Accepted | Keywords: mysql innodb myisam
Needs documentation: 0 | reference fixture
Patch needs improvement: 1 | Has patch: 1
UI/UX: 0 | Needs tests: 0
| Easy pickings: 0
-------------------------------------+-------------------------------------
Changes (by jsdalton):
* cc: jsdalton (added)
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
* ui_ux: => 0
Comment:
It looks like this ticket has a patch that's almost there. The primary
issue is the patch opens the possibility of inserting rows with bad
referential integrity while foreign_key_checks is disabled. Presumably, if
we could find a solution to this problem, the remaining issues Russel
identifies here https://code.djangoproject.com/ticket/3615#comment:8 could
be addressed.
I did a bit of research and there are certainly other people running into
this issue (not in a Django context per se). There are also some suggested
solutions out there, which mostly involve creating MySQL procedures that
run a report of referential integrity issues in the DB. Here's a quick
list of some of what I found:
* http://stackoverflow.com/questions/5014700/in-mysql-can-i-defer-
referential-integrity-checks-until-commit - Same issue, with a reference
in the answers to the MySQL documentation which states that InnoDB does
not implement this part of the SQL standard
* http://stackoverflow.com/questions/2250775/force-innodb-to-recheck-
foreign-keys-on-a-table-tables - Someone looking to do referential
integrity checking after disabling foreign_key_checks, one of the answers
contains a detailed stored procedure that finds issues.
* http://stackoverflow.com/questions/3727905/check-for-referential-
integrity-break - Similar question again, answer points to yet another
stored procedure solution http://forge.mysql.com/tools/tool.php?id=11.
I'm far from being a MySQL expert, but presumably someone with more
expertise could adapt one of the stored procedure solutions above and run
this check before committing the transaction.
This is still not entirely satisfying as a solution, since really the
point is not to identify referential integrity issues in the whole of the
database, but only those that were introduced in the context of the
transaction that is loading the fixture data. I.e. it's only those rows
that we care about. So that's really something that would have to be
addressed.
Here's another thought I would like to contribute in the "so dumb it might
just work" category: What if we loaded fixture data in two rounds? In the
first round, we disable foreign_key_checks, so the records get added to
the db without regard for their referential integrity. When all records
have been loaded, we re-enable foreign_key_checks and walk through each
record a second time, saving it again with the exact same data. Assuming
all of the references are correct, the save should work even for forward
references, since the record was added in the first round. However, if the
key refers to a non-existent reference at this stage, then there is an
underlying referential issue with the fixture data and a referential
integrity error will be triggered. This can then be propagated up and the
whole transaction rolled back.
I'm having a hard time finding something wrong with the above suggestion,
though someone smarter than me can surely an issue I'm overlooking.
Presumably one could finesse the implementation such that the double trip
could only be taken if the foreign key checks were actually being disabled
for a given DB implementation and skip it for those that handle it
properly.
If anyone has any thoughts on either the stored procedure route or the
"double load" idea, let me know. I'd love to assist in pushing this old
ticket toward a resolution and would happily do anything in the scope of
my abilities to further that goal.
--
Ticket URL: <https://code.djangoproject.com/ticket/3615#comment:21>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
