Opened 23 months ago

Closed 23 months ago

Last modified 23 months ago

#34304 closed Bug (fixed)

Adding and removing a conditional UniqueConstraint to ForeignKey multiple times crashes on MySQL

Reported by: Sage Abdullah Owned by: Sage Abdullah
Component: Database layer (models, ORM) Version: 4.2
Severity: Release blocker Keywords: GeyseR
Cc: mysql Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Sage Abdullah)

Adding and removing a UniqueConstraint to ForeignKey with a condition more than once on MySQL will crash the schema editor an OperationalError, e.g. django.db.utils.OperationalError: (1061, "Duplicate key name 'schema_book_author_id_c80c8297'")

Test in tests.schema.tests:


    def test_unique_with_fk_and_condition_multiple_times(self):
        """
        Tests adding and removing a unique constraint to ForeignKey
        with a condition multiple times.
        """
        with connection.schema_editor() as editor:
            editor.create_model(Author)
            editor.create_model(Book)
        constraint = UniqueConstraint(
            "author",
            condition=Q(title__in=["tHGttG", "tRatEotU"]),
            name="book_author_condition_uniq",
        )

        for i in range(2):
            # Add constraint.
            with connection.schema_editor() as editor:
                editor.add_constraint(Book, constraint)
                sql = constraint.create_sql(Book, editor)
            book_table = Book._meta.db_table
            constraints = self.get_constraints(book_table)
            if connection.features.supports_partial_indexes:
                self.assertIn(constraint.name, constraints)
                self.assertIs(constraints[constraint.name]["unique"], True)
                self.assertIn("WHERE %s IN" % editor.quote_name("title"), str(sql))
            else:
                self.assertNotIn(constraint.name, constraints)
                self.assertIsNone(sql)
            # Remove constraint.
            with connection.schema_editor() as editor:
                editor.remove_constraint(Book, constraint)
            self.assertNotIn(constraint.name, self.get_constraints(book_table))

This is a regression in b731e8841558ee4caaba766c83f34ea9c7004f8b.

Use case:
Wagtail supports multiple database backends. We have a migration that adds a unique constraint to a foreign key with a condition. In addition, we also have migrations that alter the field referenced in the condition. Before making an alter field operation, we need to remove the unique constraint to avoid violating the constraint, then re-add the constraint after the field has been altered. We have more than one migrations that do these operations, thus triggering the error. This issue only happens on MySQL, specifically after the above commit was merged into Django. Even though conditional indexes aren't supported on MySQL, I believe it should just be ignored (as with previous releases) instead of crashing the schema editor.

Relevant migrations in Wagtail:

Change History (6)

comment:1 by Sage Abdullah, 23 months ago

Description: modified (diff)

comment:2 by Sage Abdullah, 23 months ago

Description: modified (diff)

comment:3 by Sage Abdullah, 23 months ago

Has patch: set

comment:4 by Mariusz Felisiak, 23 months ago

Cc: mysql added
Component: MigrationsDatabase layer (models, ORM)
Keywords: GeyseR added
Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted

Thanks for the report.

comment:5 by GitHub <noreply@…>, 23 months ago

Resolution: fixed
Status: assignedclosed

In 110b3b83:

Fixed #34304 -- Made MySQL's SchemaEditor.remove_constraint() don't create foreign key index when unique constraint is ignored.

Regression in b731e8841558ee4caaba766c83f34ea9c7004f8b.

comment:6 by Mariusz Felisiak <felisiak.mariusz@…>, 23 months ago

In 5e0be087:

[4.2.x] Fixed #34304 -- Made MySQL's SchemaEditor.remove_constraint() don't create foreign key index when unique constraint is ignored.

Regression in b731e8841558ee4caaba766c83f34ea9c7004f8b.
Backport of 110b3b83567da22f19ec04210db134d0fe83d662 from main

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