Opened 20 months ago

Closed 15 months ago

Last modified 15 months ago

#26171 closed Bug (fixed)

ForeignKey with db_constraint=False doesn't generate an index on MySQL

Reported by: Jeroen Pulles Owned by: Aaron Elliot Ross
Component: Migrations Version: 1.9
Severity: Normal Keywords: mysql
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I have a model that does not want to use a constraint on a foreign key, but still use a database index:

class Category(models.Model):
    text = models.CharField(max_length=3)

class Message(models.Model):
    cat = models.ForeignKey(Category, db_constraint=False)

class IndexMessage(models.Model):
    cat = models.ForeignKey(Category, db_constraint=False, db_index=True)

class StrongMessage(models.Model):
    cat = models.ForeignKey(Category)

The SQLite backend generates an index on the FK column for both models:

$ python manage.py sqlmigrate boohoo 0001_initial
BEGIN;
--
-- Create model Category
--
CREATE TABLE "boohoo_category" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "text" varchar(3) NOT NULL);
--
-- Create model IndexMessage
--
CREATE TABLE "boohoo_indexmessage" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "cat_id" integer NOT NULL);
--
-- Create model Message
--
CREATE TABLE "boohoo_message" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "cat_id" integer NOT NULL);
--
-- Create model StrongMessage
--
CREATE TABLE "boohoo_strongmessage" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "cat_id" integer NOT NULL REFERENCES "boohoo_category" ("id"));
CREATE INDEX "boohoo_indexmessage_05e7bb57" ON "boohoo_indexmessage" ("cat_id");
CREATE INDEX "boohoo_message_05e7bb57" ON "boohoo_message" ("cat_id");
CREATE INDEX "boohoo_strongmessage_05e7bb57" ON "boohoo_strongmessage" ("cat_id");

COMMIT;

With the MySQL backend, this does not create an index on the FK:

$ python manage.py sqlmigrate boohoo 0001_initial
BEGIN;
--
-- Create model Category
--
CREATE TABLE `boohoo_category` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `text` varchar(3) NOT NULL);
--
-- Create model IndexMessage
--
CREATE TABLE `boohoo_indexmessage` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `cat_id` integer NOT NULL);
--
-- Create model Message
--
CREATE TABLE `boohoo_message` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `cat_id` integer NOT NULL);
--
-- Create model StrongMessage
--
CREATE TABLE `boohoo_strongmessage` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `cat_id` integer NOT NULL);
ALTER TABLE `boohoo_strongmessage` ADD CONSTRAINT `boohoo_strongmessage_cat_id_c843b68a_fk_boohoo_category_id` FOREIGN KEY (`cat_id`) REFERENCES `boohoo_category` (`id`);

COMMIT;

I would think that specifying db_constraint=false would only leave out the constraint, and not also the index; Especially with <field>.db_index still set to true. Adding db_index=True does not help, probably because that is the default setting on the FK field object.

This also applies to earlier django versions.

A simple workaround is to use index_together = (('cat', ), ) on the models.

I'm inclined to blame this on django.db.backends.mysql.schema.DatabaseSchemaEditor#_model_indexes_sql,
which may need an extra check for db_constraint being used. This fixes my problem (but changes current django behavior):

--- django/db/backends/mysql/schema.py.orig     2016-02-03 12:01:10.000000000 +0100
+++ django/db/backends/mysql/schema.py  2016-02-03 12:00:19.000000000 +0100
@@ -64,7 +64,7 @@
         )
         if storage == "InnoDB":
             for field in model._meta.local_fields:
-                if field.db_index and not field.unique and field.get_internal_type() == "ForeignKey":
+                if field.db_index and not field.unique and field.get_internal_type() == "ForeignKey" and field.db_constraint:
                     # Temporary setting db_index to False (in memory) to disable
                     # index creation for FKs (index automatically created by MySQL)
                     field.db_index = False

Change History (6)

comment:1 Changed 20 months ago by Tim Graham

Component: UncategorizedMigrations
Summary: Where's the index on a foreign key with db_constraint=false, when using MySQL?ForeignKey with db_constraint=False doesn't generate an index on MySQL
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

comment:2 Changed 20 months ago by Walter Doekes

Interestingly we've run into a bug caused on the same spot.

We run a test case that checks whether there are any unmigrated apps. In the (a) non-DB (SimpleTestCase) and (b) SQLite-DB driven and (c) REUSE_DB test it succeeds, but in the (d) (newly created) InnoDB case, it claims that a lot of foreign key indexes are missing exactly because of this InnoDB exception.

I wonder why that check is there. Removing the db_index=False did not seem to have any negative impact, and it causes the unexpected behaviour to go away.


The test in question looks basically like this:

        loader = ModulesMigrationLoader(our_connection, ignore_no_migrations=True)
        
        # Before anything else, see if there's conflicting apps.
        conflicts = loader.detect_conflicts()
        if conflicts:
            raise Exception('Conflicting migrations detected...')
        
        # Set up autodetector.
        autodetector = MigrationAutodetector(
            loader.project_state(),
            ProjectState.from_apps(apps),
            # ^-- the patch impacts this to_state, removing incorrect db_index=False
        )
        
        # Detect changes.
        changed = autodetector.changes(graph=loader.graph)

        self.assertFalse(changed, '...')
Last edited 20 months ago by Walter Doekes (previous) (diff)

comment:3 Changed 16 months ago by Aaron Elliot Ross

Has patch: set
Owner: changed from nobody to Aaron Elliot Ross
Status: newassigned

This code sets db_index = False:

https://github.com/django/django/blob/master/django/db/backends/mysql/schema.py#L61-L63

I don't think the db/backends/mysql/schema.py code should be modifying the attributes of a Field. I agree that the check needs to take db_constraint into consideration too.

Here's a pull request:

https://github.com/django/django/pull/6774

All tests pass using MySQL.

comment:4 Changed 15 months ago by Simon Charette

Keywords: mysql added

comment:5 Changed 15 months ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In 6bf79640:

Fixed #26171 -- Made MySQL create an index on ForeignKeys with db_contraint=False.

Refactored "Prevented unneeded index creation on MySQL-InnoDB" (2ceb10f)
to avoid setting db_index=False.

comment:6 Changed 15 months ago by Tim Graham <timograham@…>

In 19812868:

[1.10.x] Fixed #26171 -- Made MySQL create an index on ForeignKeys with db_contraint=False.

Refactored "Prevented unneeded index creation on MySQL-InnoDB" (2ceb10f)
to avoid setting db_index=False.

Backport of 6bf7964023487f2a352084e74aca27aecb354d6c from master

Note: See TracTickets for help on using tickets.
Back to Top