Code

Opened 8 years ago

Closed 6 years ago

#2720 closed defect (fixed)

Wrong syntax generated for foreign keys under MySQL

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version:
Severity: normal Keywords: innodb foreign key
Cc: plesur@…, freakboy@…, not.com@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: UI/UX:

Description (last modified by adrian)

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.

Attachments (3)

constraints.diff (3.3 KB) - added by Joakim Sernbrant <serbaut@…> 8 years ago.
I think this patch should work.
ticket-2720.diff (3.3 KB) - added by Michael Radziej <mir@…> 7 years ago.
Updated patch, tested for postgresql8.1 and mysql4.1
any-order2720.diff (7.2 KB) - added by yary h <spm-django@…> 7 years ago.
works for any ordering and for M2M references across different apps

Download all attachments as: .zip

Change History (21)

comment:1 Changed 8 years ago by plesur@…

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

comment:2 Changed 8 years ago by ubernostrum

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).

comment:3 Changed 8 years ago by 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.

comment:4 Changed 8 years ago by 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.

comment:5 Changed 8 years ago by anonymous

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

Changed 8 years ago by Joakim Sernbrant <serbaut@…>

I think this patch should work.

comment:6 Changed 8 years ago by Joakim Sernbrant <serbaut@…>

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

comment:7 Changed 8 years ago by plesur@…

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

comment:8 Changed 8 years ago by adrian

  • Description modified (diff)

Fixed formatting in description.

comment:9 Changed 7 years ago by Michael Radziej <mir@…>

  • Cc plesur@… added
  • Has patch set
  • Summary changed from Wrong syntax generated for foreign keys under MySQL/InnoDB 5.0.22 to Wrong syntax generated for foreign keys under MySQL
  • Triage Stage changed from Unreviewed to Ready for checkin

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@…!

comment:10 Changed 7 years ago by Michael Radziej <mir@…>

  • Patch needs improvement set
  • Triage Stage changed from Ready for checkin to Accepted

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")
);

Changed 7 years ago by Michael Radziej <mir@…>

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

comment:11 Changed 7 years ago by russellm

  • Cc freakboy@… added

comment:12 follow-up: Changed 7 years ago by 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...

comment:13 in reply to: ↑ 12 Changed 7 years ago by 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.

Changed 7 years ago by yary h <spm-django@…>

works for any ordering and for M2M references across different apps

comment:14 Changed 7 years ago by yary h <spm-django@…>

  • Cc not.com@… added

_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.

comment:15 Changed 7 years ago by PhiR

#4193 is also related to foreign keys.

comment:16 Changed 7 years ago by brosner

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.

comment:17 Changed 6 years ago by 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?

comment:18 Changed 6 years ago by russellm

  • Resolution set to fixed
  • Status changed from new to closed

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

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.