Cannot reverse MySQL DB migration when model has unique_together and one of the fields has db_index=True

[Andy McCurdy]

I'm seeing errors attempting to rollback a migration when a model field that has db_index=True is also included within a unique_together constraint. From my testing this only happens when the field with the db_index=True attribute is included in the unique_together constraint after another field that does not have db_index specified. In the example below, reversing the order of the fields in the unique_together allows the migration to rollback without issue.

I have only tested this on MySQL. Other backends may or may not be affected as well.

The following models.py and 0001_initial.py files demonstrate the error. I'm using Django 2.1.5 and mysqlclient 1.3.14.

from django.db import models


class TestModel(models.Model):
    field_a = models.IntegerField(db_index=True)
    field_b = models.IntegerField()

    class Meta:
        unique_together = (
            ('field_b', 'field_a'),
        )

This produces the following migration:

# Generated by Django 2.1.5 on 2019-01-10 00:11

from django.db import migrations, models


class Migration(migrations.Migration):

    initial = True

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='TestModel',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('field_a', models.IntegerField(db_index=True)),
                ('field_b', models.IntegerField()),
            ],
        ),
        migrations.AlterUniqueTogether(
            name='testmodel',
            unique_together={('field_b', 'field_a')},
        ),
    ]

The migration is applied cleanly. When rolling it back I get this error:

Operations to perform:
  Unapply all migrations: mysql_error
Running migrations:
  Rendering model states... DONE
  Unapplying mysql_error.0001_initial...Traceback (most recent call last):
  File "./manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/core/management/base.py", line 316, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/core/management/base.py", line 353, in execute
    output = self.handle(*args, **options)
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/core/management/commands/migrate.py", line 203, in handle
    fake_initial=fake_initial,
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/db/migrations/executor.py", line 121, in migrate
    state = self._migrate_all_backwards(plan, full_plan, fake=fake)
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/db/migrations/executor.py", line 196, in _migrate_all_backwards
    self.unapply_migration(states[migration], migration, fake=fake)
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/db/migrations/executor.py", line 262, in unapply_migration
    state = migration.unapply(state, schema_editor)
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/db/migrations/migration.py", line 175, in unapply
    operation.database_backwards(self.app_label, schema_editor, from_state, to_state)
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/db/migrations/operations/models.py", line 518, in database_backwards
    return self.database_forwards(app_label, schema_editor, from_state, to_state)
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/db/migrations/operations/models.py", line 514, in database_forwards
    getattr(new_model._meta, self.option_name, set()),
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/db/backends/base/schema.py", line 356, in alter_unique_together
    self._delete_composed_index(model, fields, {'unique': True}, self.sql_delete_unique)
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/db/backends/mysql/schema.py", line 82, in _delete_composed_index
    return super()._delete_composed_index(model, fields, *args)
  File "/Users/andy/.pyenv/versions/django-test/lib/python3.7/site-packages/django/db/backends/base/schema.py", line 385, in _delete_composed_index
    ", ".join(columns),
ValueError: Found wrong number (0) of constraints for mysql_error_testmodel(field_b, field_a)

[Andy McCurdy]

FYI I just tested on SQLite and could not reproduce this issue.

[Carlton Gibson]

Hi Andy. Do you have capacity to put your test case into a PR on GitHub so that we can see it in the CI? (I can do it if not but you've likely already got it bootstrapped, so that'd save a few cycles. 🙂)

Thanks

[Andy McCurdy]

Hi Carlton. I added a test but couldn't reproduce the error in the test suite against a branch based off master. I went back to the test project I made (where the code above is copied from and using Django 2.1.5) and that too seems to be working now. I'm very confused. The test project is isolated in its own virtualenv and no dependencies have been changed since I created the test project and filed the bug report.

So at this point I'm unable to reproduce the issue. Perhaps we should close this and if I can figure out how to repro it again I'll reopen this or file a new bug.

[Carlton Gibson]

… if I can figure out how to repro it again I'll reopen this …

OK. Lets do that. A test case is obviously best but a test project would do. 👍

Thanks for the follow up Andy.