#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
-------------------------------------+-------------------------------------
Comment (by jsdalton):
Okay, I'm uploading a new patch which, while a bit rough around the edges,
solves the core problem of executing constraint checks on table after data
has been loaded to it with foreign_key_checks off. It passes both tests
that I introduced (loading fixtures with future refs work, loading
fixtures with nonexistent references errors) and since it entails just
using a few SELECT statements it should be highly performant.
Patch to follow, but here's a brief rundown.
The basic approach was cribbed from this answer
http://stackoverflow.com/questions/2250775/force-innodb-to-recheck-
foreign-keys-on-a-table-tables . Whoever wrote the answer must be a DBA
who loves wrapping it all up in a neat and tidy stored procedure package,
but it took me about 10 minutes or so to sit down and unpack from all the
abstractions. At its core, all it does is select all the key columns for a
given table, then it does a SELECT on each of those that joins them to
each other when the referring column id is null. This is what that
statement looks like when I use concrete values (for a test table I have):
{{{
SELECT * FROM `fixtures_regress_book` as REFERRING
LEFT JOIN `fixtures_regress_person` as REFERRED
ON (REFERRING.`author_id` = REFERRED.`id`)
WHERE REFERRING.`author_id` IS NOT NULL
AND REFERRED.`id` IS NULL
}}}
Not so bad. Basically, any rows this statement return represent bad data.
Implementing this wasn't too big of a deal. In loaddata, for each model
that was loaded we now run the check on the associated table. The check is
implemented in the MySQL connection class as
`check_foreign_key_constraints_for_table()`. It first grabs all the key
columns via a new method on the MySQL Introspection class, and then
executes a version of the above SQL SELECT on each key column. If any bad
rows are found, it raises an IntegrityError.
This patch still needs work to clean a few up and ensure there is enough
test coverage. Specifically:
* `check_foreign_key_constraints_for_table()` is kind of weak right now.
It probably should not raise IntegrityError directly, but instead just
return an empty list if all goes well and a list of bad rows if not. The
IntegrityError is probably best handled in whatever code is implementing
(i.e. loaddata can figure out what to report)
* We should probably strive to output a meaningful list of bad data.
* The `mysql.DatabaseIntrospectionIntrospection.get_key_columns()` might
need testing. I just extracted it from `get_relations()` which was already
running the correct statement but not returning results in a form that
could be used for this.
* We need to do whatever we need to do elsewhere in the test suite to make
sure MySQL isn't breaking when forward references are loaded (since not
all cases uses loaddata).
I'd really like some feedback before I plunge into any next steps to make
sure this sounds like I'm on the right track. Please do let me know if you
have any thoughts on this approach.
I'm really hopeful that the basic solution has been nailed though.
--
Ticket URL: <https://code.djangoproject.com/ticket/3615#comment:25>
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.
