[Django] #26171: Where's the index on a foreign key with db_constraint=false, when using MySQL?
Django
MySQL?
-------------------------------+--------------------
Reporter: jeroenp | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.9
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26171>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
----------------------------+------------------------------------
Reporter: jeroenp | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.9
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: => 0
* component: Uncategorized => Migrations
* needs_tests: => 0
* needs_docs: => 0
* type: Uncategorized => Bug
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/26171#comment:1>
Django
----------------------------+------------------------------------
Type: Bug | Status: new
Component: Migrations | Version: 1.9
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by wdoekes):
Interestingly we've run into a bug caused on the same spot.
We run a test case that check whether there are any unmigrated apps. In
both the non-DB (SimpleTestCase) and SQLite-DB driven and REUSE_DB test it
succeeds, but in the (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 weird 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, '...')
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26171#comment:2>
Django
Reporter: jeroenp | Owner: aaronelliotross
Type: Bug | Status: assigned
Component: Migrations | Version: 1.9
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by aaronelliotross):
* owner: nobody => aaronelliotross
* status: new => assigned
* has_patch: 0 => 1
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26171#comment:3>
Django
----------------------------+-------------------------------------------
Reporter: jeroenp | Owner: aaronelliotross
Type: Bug | Status: assigned
Component: Migrations | Version: 1.9
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* keywords: => mysql
--
Ticket URL: <https://code.djangoproject.com/ticket/26171#comment:4>
Django
----------------------------+-------------------------------------------
Reporter: jeroenp | Owner: aaronelliotross
Component: Migrations | Version: 1.9
Severity: Normal | Resolution: fixed
Keywords: mysql | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"6bf7964023487f2a352084e74aca27aecb354d6c" 6bf79640]:
{{{
#!CommitTicketReference repository=""
revision="6bf7964023487f2a352084e74aca27aecb354d6c"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26171#comment:5>
Django
----------------------------+-------------------------------------------
Reporter: jeroenp | Owner: aaronelliotross
Type: Bug | Status: closed
Component: Migrations | Version: 1.9
Severity: Normal | Resolution: fixed
Keywords: mysql | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"198128684bb0c47e9ea2900a92d192794d62791f" 19812868]:
{{{
#!CommitTicketReference repository=""
revision="198128684bb0c47e9ea2900a92d192794d62791f"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26171#comment:6>