Wrong syntax generated for foreign keys under MySQL

[anonymous]

Django uses the following syntax to define foreign keys under MySQL/InnoDB:

CREATE TABLE `system_script_queue` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `execution_time` datetime NOT NULL,
    `period` integer NOT NULL,
    `system_script_id` integer NOT NULL REFERENCES `system_script` (`id`)
);

Using that syntax, maybe 1/3 of my foreign key constraints are being ignored by the DB.

If I'm reading this tech note correctly (​http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html), that's the wrong syntax to use. The correct syntax is this one:

CREATE TABLE `system_script_queue` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `system_script_id` integer NOT NULL,
    `execution_time` datetime NOT NULL,
    `period` integer NOT NULL,
    foreign key (`system_script_id`) references `system_script` (`id`)
);

Using this syntax, all of my foreign key constraints seem to be successfully created.

[plesur@…]

(...just adding my email address....)

[James Bennett]

Have you double-checked that the foreign keys aren't being created later on as constraints? IIRC Django often creates the table without FOREIGN KEY constraints, and then adds those later in the run, once all the tables are created (this avoids problems caused by the order in which the tables are created).

[plesur@…]

Would that be evident in "manage.py sqlindexes <appname>"? I'm not seeing any constraints added there.

When I look at the SQL output by "manage.py sql <appname>", I see the first syntax used above; when I drop the table and recreate it with that foreign-key syntax, the table is created with no foreign key (it's consistent for a given table - if it fails once, it always fails).

When I drop the table and recreate it with the second syntax used above, it always succeeds.

[Joakim Sernbrant <serbaut@…>]

With mysql/innodb you need to use the CONSTRAINT... syntax, the first form is just a "comment" (this fact is hidden in the docs somewhere):

CREATE TABLE `test_bar` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `foo_id` integer NOT NULL REFERENCES `test_foo` (`id`),
    CONSTRAINT FOREIGN KEY (`foo_id`) REFERENCES `test_foo` (`id`)
);

I have a patch for this and some other db related things I will submit in the near future.

[anonymous]

Patch: Excellent, thank you! Will the patch be announced here, or should I be watching somewhere else?

[Joakim Sernbrant <serbaut@…>]

I think this patch should work.

[Joakim Sernbrant <serbaut@…>]

Note sure if this is compatible with all the other backends but my guess is that it is.

[plesur@…]

That seems to have done just what I needed - thanks again!

[Adrian Holovaty]

Fixed formatting in description.

[Michael Radziej <mir@…>]

mysql is strange, and this is a real bug in Django. Only foreign keys to models defined later escape this bug, because a different syntax is chosen for these. mysql4 and 5 are affected. The docs in mysql are pretty explicit that the used syntax is more or less read and thrown away (thank you, mysql, what a great idea ...)

Here's a model definition:

from django.db import models

class A(models.Model):
  pass

class B(models.Model):
  a = models.ForeignKey(A)

After syncdb, watch this:

mysql> select * from testapp_a;
Empty set (0.00 sec)

mysql> insert into testapp_b values (1,2);
Query OK, 1 row affected (0.03 sec)

I checked the patch, and it resolves the problem. Thanks, plesur@…!

[Michael Radziej <mir@…>]

Ouch, the patch doesn't work with postgresql. I used 8.1.4:

mir@mahlzahn:testproj$ python manage.py syncdb
Creating table auth_message
Creating table auth_group
Creating table auth_user
Creating table auth_permission
Traceback (most recent call last):
  File "manage.py", line 11, in ?
    execute_manager(settings)
  File "/home/mir/lib/python/django/core/management.py", line 1462, in execute_manager
    execute_from_command_line(action_mapping, argv)
  File "/home/mir/lib/python/django/core/management.py", line 1370, in execute_from_command_line
    action_mapping[action](int(options.verbosity), options.interactive)
  File "/home/mir/lib/python/django/core/management.py", line 511, in syncdb
    cursor.execute(statement)
  File "/home/mir/lib/python/django/db/backends/util.py", line 12, in execute
    return self.cursor.execute(sql, params)
  File "/home/mir/lib/python/django/db/backends/postgresql/base.py", line 43, in execute
    return self.cursor.execute(sql, [smart_basestring(p, self.charset) for p in params])
psycopg.ProgrammingError: FEHLER:  Fehler »Syntaxfehler« bei »FOREIGN« at character 274

CREATE TABLE "auth_group_permissions" (
    "id" serial NOT NULL PRIMARY KEY,
    "group_id" integer NOT NULL REFERENCES "auth_group" ("id"),
    "permission_id" integer NOT NULL REFERENCES "auth_permission" ("id"),
    UNIQUE ("group_id", "permission_id"),
    CONSTRAINT FOREIGN KEY ("group_id") REFERENCES "auth_group" ("id"),
    CONSTRAINT FOREIGN KEY ("permission_id") REFERENCES "auth_permission" ("id")
);

[Michael Radziej <mir@…>]

Updated patch, tested for postgresql8.1 and mysql4.1

[Russell Keith-Magee]

How does this patch interact with #3390 - in particular, the test for forward references in deserialization data? When I worked up that patch, I was surprised that there weren't any MySQL complaints about data consistency, but if I'm understanding this problem correctly, the constraints may not be operating correctly on MySQL...

[Michael Radziej <mir@…>]

Replying to russellm:

How does this patch interact with #3390 - in particular, the test for forward references in deserialization data? When I worked up that patch, I was surprised that there weren't any MySQL complaints about data consistency, but if I'm understanding this problem correctly, the constraints may not be operating correctly on MySQL...

Hmm, our two patches conflict with each other, and I'm too tired now to resolve the conflicts.

I got failures when I tried #3390 (without #2720) on a postgresql backend. You can probably get similar results when you reorder your model classes so that you only have forward references.

[yary h <spm-django@…>]

works for any ordering and for M2M references across different apps

[yary h <spm-django@…>]

_get_many_to_many_sql_for_model was assuming that all referred-to models were already created, that breaks when there's a ManyToMany field referring to a model in a different app. I just uploaded a version of this patch which fixes that.

I'm having troulbe uploading tests, so get the 2k tarball here:​http://yary.ack.org/intrapp.tgz

These tests took a while to get right, and still could use more refinement. They work well to ferret out the problem with MySQL/InnoDB, but will give spurious errors on a database that does not enforce foreign keys.

[Philippe Raoult]

#4193 is also related to foreign keys.

[Brian Rosner]

The patch I posted on #4193 should solve this problem. The issue was that models are cached in a Python dict which can have random orderings when iterated over. By using a SortedDict the ordering is maintained and SQL creation works fine.

[glassfordm]

I believe the fix described in ticket #5729, which has already been accepted, fixes this issue in another way. Should this ticket be closed?

[Russell Keith-Magee]

Closing the ticket due to multiple reports that it has been fixed.