Removing unique_together constraint make Django 1.8 migration fail with MySQL

[Thomas Recouvreux]

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.

[Simon Charette]

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

[Tim Graham]

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

[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!

[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.

[Tim Graham]

​PR with revert and regression test.

[Claude Paroz]

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

[Claude Paroz <claude@…>]

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.

[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.