Opened 4 months ago

Last modified 4 months ago

#31503 new Bug

Moving a unique constraint from unique_together to Field.unique generate an invalid migration.

Reported by: Xiang Wang Owned by: nobody
Component: Migrations Version: 3.0
Severity: Normal Keywords: unique_together unique migrations
Cc: ramwin@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

You can see a demo example to show the bug at [github](https://github.com/ramwin/testunique/).
I met a problem when I convert a unique_together to the unique=True attribute.

first commit, everything is ok
I create a model file.

// testapp/models.py
class MyModel(models.Model):
  name = models.CharField(max_length=32)
  class Meta:
      unique_together = ("name", )

the migrations file looks like this.

// testapp/migrations/0001_initial.py
# Generated by Django 3.0.5 on 2020-04-22 12:47
from django.db import migrations, models
class Migration(migrations.Migration):
  dependencies = [
      ('testapp', '0001_initial'),
  ]
  operations = [
      migrations.AlterField(
          model_name='mymodel',
          name='name',
          field=models.CharField(max_length=32, unique=True),
      ),
      migrations.AlterUniqueTogether(
          name='mymodel',
          unique_together=set(),
      ),
  ]

second commit: then I remove the unique_together and add unique=True to the field MyModel.name

model file
class MyModel(models.Model):
  name = models.CharField(max_length=32, unique=True)
  class Meta:
     pass
     # unique_together = ("name", )
0002 migrations file
class Migration(migrations.Migration):
  dependencies = [
      ('testapp', '0001_initial'),
  ]
  operations = [
      migrations.AlterField(
          model_name='mymodel',
          name='name',
          field=models.CharField(max_length=32, unique=True),
      ),
      migrations.AlterUniqueTogether(
          name='mymodel',
          unique_together=set(),
      ),
  ]

However, when I apply the migrations, an error occurs;

wangx@aliyun:~/testunique$ python3 manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, testapp
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying sessions.0001_initial... OK
  Applying testapp.0001_initial... OK
  Applying testapp.0002_auto_20200422_1247...Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/mysql/base.py", line 74, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/local/lib/python3.6/dist-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1061, "Duplicate key name 'testapp_mymodel_name_ba5e2bd2_uniq'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 21, in <module>
    main()
  File "manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.6/dist-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.6/dist-packages/django/core/management/__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.6/dist-packages/django/core/management/base.py", line 328, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.6/dist-packages/django/core/management/base.py", line 369, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.6/dist-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/django/core/management/commands/migrate.py", line 233, in handle
    fake_initial=fake_initial,
  File "/usr/local/lib/python3.6/dist-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.6/dist-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.6/dist-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python3.6/dist-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/usr/local/lib/python3.6/dist-packages/django/db/migrations/operations/fields.py", line 249, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/base/schema.py", line 565, in alter_field
    old_db_params, new_db_params, strict)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/base/schema.py", line 745, in _alter_field
    self.execute(self._create_unique_sql(model, [new_field.column]))
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/mysql/base.py", line 74, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/local/lib/python3.6/dist-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1061, "Duplicate key name 'testapp_mymodel_name_ba5e2bd2_uniq'")

I check the sql for these migrations, it shows:

wangx@aliyun:~/testunique$ python3 manage.py sqlmigrate testapp 0001
--
-- Create model MyModel
--
CREATE TABLE `testapp_mymodel` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `name` varchar(32) NOT NULL);
ALTER TABLE `testapp_mymodel` ADD CONSTRAINT `testapp_mymodel_name_ba5e2bd2_uniq` UNIQUE (`name`);
wangx@aliyun:~/testunique$ python3 manage.py sqlmigrate testapp 0002
--
-- Alter field name on mymodel
--
ALTER TABLE `testapp_mymodel` ADD CONSTRAINT `testapp_mymodel_name_ba5e2bd2_uniq` UNIQUE (`name`);
--
-- Alter unique_together for mymodel (0 constraint(s))
--
ALTER TABLE `testapp_mymodel` DROP INDEX `testapp_mymodel_name_ba5e2bd2_uniq`;

it looks like django will

  • first create the index for unique=True
  • second drop the index for unique_together=('name', )

but the program for creating index name generates the same index name :testapp_mymodel_name_ba5e2bd2_uniq, so when django create the same index, Duplicate key name error occurs.

Attachments (1)

testunique.tar.gz (29.3 KB) - added by Xiang Wang 4 months ago.
this is a small project which can repeat the bug.

Download all attachments as: .zip

Change History (4)

Changed 4 months ago by Xiang Wang

Attachment: testunique.tar.gz added

this is a small project which can repeat the bug.

comment:1 Changed 4 months ago by Simon Charette

Summary: I met a problem when I convert a unique_together to the unique=True attribute.Moving a unique constraint from unique_together to Field.unique generate an invalid migration.
Triage Stage: UnreviewedAccepted

Thank your for your report.

I guess this is a bug in the auto-detector where the AlterUniqueTogether should appear before the AlterField that adds the Field.unique=True. I assume this is the case because generate_altered_unique_together is run after generate_altered_fields and the former doesn't add any dependencies on ensure operations are properly re-ordered.

Not sure it's worth adjusting AddConstraint ordering as well since the chance of adding a UniqueConstraint with a colliding .name are really slim.

Xiang, can you confirm that re-ordering your operations so that AlterUniqueTogether is performed before AlterField addresses your issue?

comment:2 Changed 4 months ago by Xiang Wang

I think changing the format of index name if the CONSTRAINT is create by the unique_together will work either.
e.g.:

ALTER TABLE `testapp_mymodel` ADD CONSTRAINT `testapp_mymodel_name_ba5e2bd2_uniq_by_unique_together` UNIQUE (`name`);
ALTER TABLE `testapp_mymodel` ADD CONSTRAINT `testapp_mymodel_name_ba5e2bd2_uniq_by_field` UNIQUE (`name`);

I tried the sql in reverse order. It works.

wangx@aliyun:~/testunique$ python3 manage.py migrate testapp 0002
Operations to perform:
  Target specific migration: 0002_auto_20200422_1247, from testapp
Running migrations:
  Applying testapp.0002_auto_20200422_1247...Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/mysql/base.py", line 74, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/local/lib/python3.6/dist-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1061, "Duplicate key name 'testapp_mymodel_name_ba5e2bd2_uniq'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 21, in <module>
    main()
  File "manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.6/dist-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.6/dist-packages/django/core/management/__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.6/dist-packages/django/core/management/base.py", line 328, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.6/dist-packages/django/core/management/base.py", line 369, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.6/dist-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/django/core/management/commands/migrate.py", line 233, in handle
    fake_initial=fake_initial,
  File "/usr/local/lib/python3.6/dist-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.6/dist-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.6/dist-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python3.6/dist-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/usr/local/lib/python3.6/dist-packages/django/db/migrations/operations/fields.py", line 249, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/base/schema.py", line 565, in alter_field
    old_db_params, new_db_params, strict)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/base/schema.py", line 745, in _alter_field
    self.execute(self._create_unique_sql(model, [new_field.column]))
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/mysql/base.py", line 74, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/local/lib/python3.6/dist-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1061, "Duplicate key name 'testapp_mymodel_name_ba5e2bd2_uniq'")


wangx@aliyun:~/testunique$ python3 manage.py sqlmigrate testapp 0002
--
-- Alter field name on mymodel
--
ALTER TABLE `testapp_mymodel` ADD CONSTRAINT `testapp_mymodel_name_ba5e2bd2_uniq` UNIQUE (`name`);
--
-- Alter unique_together for mymodel (0 constraint(s))
--
ALTER TABLE `testapp_mymodel` DROP INDEX `testapp_mymodel_name_ba5e2bd2_uniq`;


wangx@aliyun:~/testunique$ mysql -u test  -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use testunique;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> ALTER TABLE `testapp_mymodel` DROP INDEX `testapp_mymodel_name_ba5e2bd2_uniq`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `testapp_mymodel` ADD CONSTRAINT `testapp_mymodel_name_ba5e2bd2_uniq` UNIQUE (`name`);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `testapp_mymodel` ADD CONSTRAINT `testapp_mymodel_name_ba5e2bd2_uniq_by_unique_together` UNIQUE (`name`);
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 1

comment:3 Changed 4 months ago by Xiang Wang

I think we can consider limit the length of unique_together. If a user set unique_together containing only one field, we can raise an error and ask him to use unique=True instead.

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