Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#24757 closed Bug (fixed)

Removing unique_together constraint make Django 1.8 migration fail with MySQL

Reported by: Thomas Recouvreux Owned by: Tim Graham
Component: Migrations Version: 1.8
Severity: Release blocker Keywords: migrations mysql
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Migrations containing migrations.AlterUniqueTogether(name='xxx', unique_together=set([]),) may fail on Django 1.8 but not on Django 1.7 if using the backend mysql.

Given the database engine is mysql and the following models.py:

from django.db import models


class MyModelA(models.Model):
    s = models.CharField(max_length=120)


class MyModelB(models.Model):
    a = models.ForeignKey(MyModelA)
    b = models.IntegerField()

And the 2 following migrations:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations


class Migration(migrations.Migration):

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='MyModelA',
            fields=[
                ('id', models.AutoField(serialize=False, auto_created=True, verbose_name='ID', primary_key=True)),
                ('s', models.CharField(max_length=120)),
            ],
        ),
        migrations.CreateModel(
            name='MyModelB',
            fields=[
                ('id', models.AutoField(serialize=False, auto_created=True, verbose_name='ID', primary_key=True)),
                ('b', models.IntegerField()),
                ('a', models.ForeignKey(to='mysite.MyModelA')),
            ],
        ),
        migrations.AlterUniqueTogether(
            name='mymodelb',
            unique_together=set([('a', 'b')]),
        ),
    ]
# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations


class Migration(migrations.Migration):

    dependencies = [
        ('mysite', '0001_initial'),
    ]

    operations = [
        migrations.AlterUniqueTogether(
            name='mymodelb',
            unique_together=set([]),
        ),
    ]

Running ./manage.py migrate works fine on Django 1.7, but on Django 1.8 I get the following error:

$ ./manage.py migrate                
Operations to perform:
  Synchronize unmigrated apps: staticfiles, messages
  Apply all migrations: contenttypes, auth, mysite, sessions, admin
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
  Installing custom SQL...
Running migrations:
  Rendering model states... DONE
  [..]
  Applying mysite.0001_initial... OK
  Applying mysite.0002_auto...Traceback (most recent call last):
  [..]
django.db.utils.OperationalError: (1553, "Cannot drop index 'mysite_mymodelb_a_id_b53c781c93aab9a_uniq': needed in a foreign key constraint")

The same migrations work on psql, I did not have the chance to test it against sqlite.

Inspecting the generated sql I found Django 1.7 add an index in addition to the unique constraint and Django 1.8 does not.

On Django 1.7

BEGIN;
CREATE TABLE `mysite_mymodela` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `s` varchar(120) NOT NULL);
CREATE TABLE `mysite_mymodelb` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `b` integer NOT NULL, `a_id` integer NOT NULL);
ALTER TABLE `mysite_mymodelb` ADD CONSTRAINT `mysite_mymodelb_a_id_48275010b87402ed_uniq` UNIQUE (`a_id`, `b`);
ALTER TABLE `mysite_mymodelb` ADD CONSTRAINT `mysite_mymodelb_a_id_4981d3920f0fef1e_fk_mysite_mymodela_id` FOREIGN KEY (`a_id`) REFERENCES `mysite_mymodela` (`id`);
CREATE INDEX `mysite_mymodelb_e2b453f4` ON `mysite_mymodelb` (`a_id`);

COMMIT;

Note the CREATE INDEX `mysite_mymodelb_e2b453f4` ON `mysite_mymodelb` (`a_id`);.

And on Django 1.8

BEGIN;
CREATE TABLE `mysite_mymodela` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `s` varchar(120) NOT NULL);
CREATE TABLE `mysite_mymodelb` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `b` integer NOT NULL, `a_id` integer NOT NULL);
ALTER TABLE `mysite_mymodelb` ADD CONSTRAINT `mysite_mymodelb_a_id_784bb266d0e363ab_uniq` UNIQUE (`a_id`, `b`);
ALTER TABLE `mysite_mymodelb` ADD CONSTRAINT `mysite_mymodelb_a_id_50892c78e36678b8_fk_mysite_mymodela_id` FOREIGN KEY (`a_id`) REFERENCES `mysite_mymodela` (`id`);

COMMIT;

I joined the full traceback.

Attachments (2)

trace.txt (7.2 KB) - added by Thomas Recouvreux 4 years ago.
24757.diff (1.2 KB) - added by Tim Graham 4 years ago.

Download all attachments as: .zip

Change History (12)

Changed 4 years ago by Thomas Recouvreux

Attachment: trace.txt added

comment:1 Changed 4 years ago by Simon Charette

Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

Managed to reproduce against stable/1.8.x and master.

comment:2 Changed 4 years ago by Tim Graham

Owner: changed from nobody to Tim Graham
Status: newassigned

comment:3 Changed 4 years ago by Tim Graham

Bisected to 2ceb10f3b02cbebad6ed908880f49a7c3e901d12. Claude, can you advise what to do? Attaching a regression test.

Changed 4 years ago by Tim Graham

Attachment: 24757.diff added

comment:4 Changed 4 years ago by Claude Paroz

Wow, MySQL awesomeness!
Read http://bugs.mysql.com/bug.php?id=37910

So we have now the choice of reverting [2ceb10f3b02cbeb] and accepting that some indexes are duplicated, or try to be clever and recreate "implicit" indexes when dropping other indexes. Frankly, I now tend to choose the revert!

comment:5 Changed 4 years ago by Tim Graham

Okay with me, although it would be nice to also recommend a solution for users who have already created their schema with the current code and run into this error.

comment:6 Changed 4 years ago by Tim Graham

Has patch: set

PR with revert and regression test.

comment:7 Changed 4 years ago by Claude Paroz

Another PR which *tries* to be clever and recreate the missing index.

comment:8 Changed 4 years ago by Tim Graham

Triage Stage: AcceptedReady for checkin

comment:9 Changed 4 years ago by Claude Paroz <claude@…>

Resolution: fixed
Status: assignedclosed

In ae635cc:

Fixed #24757 -- Recreated MySQL index when needed during combined index removal

Thanks Thomas Recouvreux for the report and Tim Graham for the tests and
review.

comment:10 Changed 4 years ago by Claude Paroz <claude@…>

In 6a0d9f0:

[1.8.x] Fixed #24757 -- Recreated MySQL index when needed during combined index removal

Thanks Thomas Recouvreux for the report and Tim Graham for the tests and
review.
Backport of ae635cc36 from master.

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