Opened 3 years ago

Last modified 3 months ago

#25253 assigned Cleanup/optimization

MySQL migrations drop & recreate constraints unnecessarily when changing attributes that don't affect the schema

Reported by: Thomas Recouvreux Owned by: Shun Yu
Component: Migrations Version: 1.8
Severity: Normal Keywords: migrations m2m mysql
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When running a MySQL migration to add blank=True on a ManyToManyField, Django drops the FK constraints on the join table and creates them back.

Here is the changeset for models.py:

from django.contrib.auth.models import User
from django.db import models


class Banana(models.Model):
-    users = models.ManyToManyField(User)
+    users = models.ManyToManyField(User, blank=True)

And here the corresponding migration:

..
    operations = [
        migrations.AlterField(
            model_name='banana',
            name='users',
            field=models.ManyToManyField(to=settings.AUTH_USER_MODEL, blank=True),
        ),
    ]
..

The resulting sql for MySQL backend is:

ALTER TABLE `myproject_banana_users` DROP FOREIGN KEY `myproject_banana_users_user_id_93c9ecfd1bc0dc2_fk_auth_user_id`;
ALTER TABLE `myproject_banana_users` ADD CONSTRAINT `myproject_banana_users_user_id_60a2450416dd445f_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);
ALTER TABLE `myproject_banana_users` DROP FOREIGN KEY `myproject_bana_banana_id_4be506ef34515098_fk_myproject_banana_id`;
ALTER TABLE `myproject_banana_users` ADD CONSTRAINT `myproject_bana_banana_id_43426c15f7d142f5_fk_myproject_banana_id` FOREIGN KEY (`banana_id`) REFERENCES `myproject_banana` (`id`);

I checked on PostgreSQL the resulting sql is empty, which looks good to me since there is no change to issue to the database.

Change History (13)

comment:1 Changed 3 years ago by Tim Graham

I couldn't reproduce this on master or the stable/1.8.x branch. I turned on Django's logging and didn't see this queries among the SQL that is executed. Could you provide any additional information?

comment:2 Changed 3 years ago by Thomas Recouvreux

Hello,

You can find here the test project I built for this ticket : https://github.com/trecouvr/django_25253.

Just run ./manage.py sqlmigrate myproject 0002 to see the mentionned SQL query.

comment:3 Changed 3 years ago by Tim Graham

Did you try with Django 1.8.3 instead of 1.8 as listed in the requirements of that project? There have been a lot of bug fixes since 1.8 (not to mention security fixes!).

comment:4 Changed 3 years ago by Thomas Recouvreux

Yes, I tried with Django 1.8, 1.8.2 & 1.8.3

comment:5 Changed 3 years ago by Tim Graham

Triage Stage: UnreviewedAccepted

I can reproduce using your sample project.

comment:6 Changed 3 years ago by Tim Graham

Type: BugCleanup/optimization

comment:7 Changed 3 years ago by Tim Graham

Summary: Migrations do unecessary work when adding `blank=True` to M2M field on MySQLMigrations do unnecessary work when adding `blank=True` to M2M field on MySQL

comment:8 Changed 3 years ago by Tim Graham

Summary: Migrations do unnecessary work when adding `blank=True` to M2M field on MySQLMySQL migrations drop & recreate constraints unnecessarily when changing attributes that don't affect the schema

#25614 reported the same unnecessary dropping/adding of constraints when changing ForeignKey.on_delete. I assume that's a duplicate of this ticket, but please reopen it if fixing this issue doesn't also fix it.

comment:9 Changed 2 years ago by Benjamin Wohlwend

For what it's worth, I'm also seeing this on PostgreSQL 9.1, Django 1.9.5 and psycopg2 2.6.1. Specifically, adding an on_delete argument to a ForeignKey (but I was also able to reproduce it with just adding blank=True) and then running sqlmigrate on the generated migration results in this output:

BEGIN;
--
-- Alter field last_occurrence on event
--
ALTER TABLE "event_event" DROP CONSTRAINT "last_occurrence_id_refs_id_9b97c921";
ALTER TABLE "event_event" ADD CONSTRAINT "event_event_last_occurrence_id_a6d9e0d9_fk_event_instance_id" FOREIGN KEY ("last_occurrence_id") REFERENCES "event_instance" ("id") DEFERRABLE INITIALLY DEFERRED;

COMMIT;

It might be closer related to #25614 (which is marked as a duplicate of this bug), since it only drops/adds the constraint, but leaves the foreign key itself alone.

Last edited 2 years ago by Benjamin Wohlwend (previous) (diff)

comment:10 Changed 13 months ago by Cameron Dawson

I'm still hitting this with MySQL 5.7 and Django 1.11.3:

--
-- Alter field job on textlogstep
--
ALTER TABLE `text_log_step` DROP FOREIGN KEY `text_log_step_job_id_698f3bfb_fk_job_id`;
ALTER TABLE `text_log_step` ADD CONSTRAINT `text_log_step_job_id_698f3bfb_fk_job_id` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`);
Last edited 13 months ago by Cameron Dawson (previous) (diff)

comment:11 Changed 12 months ago by Shun Yu

Owner: changed from nobody to Shun Yu
Status: newassigned

comment:13 Changed 10 months ago by Shun Yu

comment:14 Changed 3 months ago by Mike Edmunds

Still seems to be an issue in Django 2.0.5.

The source of the problem appears to be BaseDatabaseSchemaEditor._alter_field in django.db.backends.base.schema. It unconditionally drops any existing foreign key constraint(s) and later re-creates it—whether or not anything is changing. (It seems to be more selective about dropping other types of constraints only if the field is changing in a way that impacts them.) The origin of this code is 2012, I think during initial implementation of Django migrations.

Are there non-obvious field changes where the field's FK constraint needs to be dropped and re-created? Or some other reason this code runs unconditionally? Optimization around other field changes?

A fix might be to defer dropping the FK constraint until just before the field's actions/null_actions/post_actions are collected and ready to be applied (around line 650), and then drop it only if there are actually actions to execute.

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