Code

Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#12421 closed (worksforme)

Foreign Key on Non-Primary Field fails due to lack of Index on Related Field w/ MySQL

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: clouserw@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

SVN Revision 11924

class Relation(models.Model):
    code = models.CharField(max_length=2, db_index=True)
    class Meta:
        db_table = u'relation_codes'
class Source(models.Model):
    code = models.ForeignKey('Relation', to_field='code')
    class Meta:
        db_table = u'source_codes'

When trying to create the database, I get a "_mysql_exceptions.OperationalError (#150)" on a query with the form "ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ... REFERENCES" for my foreign key relationship, checking the obvious problems I find that my table to which I'm trying to make a relationship with has no index on the field I wish to make a foreign key relationship with. Adding an index allows for the problem query to be executed successfully. I am assuming an order problem with the indices for non-primary (or non-integer) fields being created after foreign keys are added leading to the problem.

Attachments (0)

Change History (5)

comment:1 Changed 4 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 4 years ago by ramiro

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

I tset this (using a t12421 app) and MySQL 5.0 by creating only the Relation model, the sqlall output shows the index is correctly being created:

r@host:~/src/dtest08$ PYTHONPATH=~/django/trunk ./manage.py sqlall t12421
BEGIN;
CREATE TABLE `relation_codes` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `code` varchar(2) NOT NULL
)
;
CREATE INDEX `relation_codes_code` ON `relation_codes` (`code`);
COMMIT;

The index is being created.

I suspect you might have added the db_index=True to the code field of Relation after the relation_codes table was created. Remember syncdb works with a model-level granularity to create tables; if you need you will need to devise or use a third-party schema migration tool.

Another probable (although a weird one )reason for the index not being present is if MySQL isn't creating it even if is is being told to do so. But we 'd need more details about the setup.

Closing this as worksforme. Please reopen if you can provide more details and a a detailed sequence to reproduce this, or if I missed something.

comment:3 Changed 4 years ago by clouserw

  • Cc clouserw@… added
  • Resolution worksforme deleted
  • Status changed from closed to reopened

I can reproduce this with the following model:

from django.db import models

class Item(models.Model):
    license = models.ForeignKey('License', to_field="name", null=True)

    class Meta:
        db_table = 'testo_item'

class License(models.Model):
    name = models.PositiveIntegerField(db_index=True)

    class Meta:
        db_table = 'testo_license'

An important point to note is that I'm forcing InnoDB in my settings.py (which is the difference between this failing and not):

DATABASES = { 
    'default': { 
        ...
        'OPTIONS': {'init_command': 'SET storage_engine=InnoDB'},
    },
}

At that point running sqlall will show you the order the commands are executed:

$ ./manage.py sqlall testo
BEGIN;
CREATE TABLE `testo_item` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `license_id` integer UNSIGNED
)
;
CREATE TABLE `testo_license` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` integer UNSIGNED
)
;
ALTER TABLE `testo_item` ADD CONSTRAINT `license_id_refs_name_a4fa988` FOREIGN KEY (`license_id`) REFERENCES `testo_license` (`name`);
CREATE INDEX `testo_item_license_id` ON `testo_item` (`license_id`);
CREATE INDEX `testo_license_name` ON `testo_license` (`name`);
COMMIT;

Note that it's trying to add the foreign key before it creates the indexes. Copying and pasting the above output will work fine, unless your default table type is InnoDB (or you can modify the CREATE TABLE statements and add ENGINE=InnoDB).

comment:4 Changed 4 years ago by erikr

  • Resolution set to worksforme
  • Status changed from reopened to closed

I can not reproduce this with your model with Django trunk, because syncdb/sqlall refuse db_index=True unless I also set unique=True on License.name.

Error: One or more models did not validate:
foo.item: Field 'name' under model 'License' must have a unique=True constraint.

Having written the model like this:

from django.db import models

class Item(models.Model):
    license = models.ForeignKey('License', to_field="name", null=True)

    class Meta:
        db_table = 'testo_item'

class License(models.Model):
    name = models.PositiveIntegerField(db_index=True)

    class Meta:
        db_table = 'testo_license'

The SQL becomes this, which is valid for InnoDB:

BEGIN;
CREATE TABLE `testo_item` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `license_id` integer UNSIGNED
)
;
CREATE TABLE `testo_license` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` integer UNSIGNED NOT NULL UNIQUE
)
;
ALTER TABLE `testo_item` ADD CONSTRAINT `license_id_refs_name_a4fa988` FOREIGN KEY (`license_id`) REFERENCES `testo_license` (`name`);
CREATE INDEX `testo_item_139668fe` ON `testo_item` (`license_id`);
COMMIT;

The code will simply not allow me to set db_index unless I set unique.

Closing this again as worksforme.

comment:5 Changed 4 years ago by clouserw

Indeed, it looks like this won't occur after #11702. I agree this is 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.