Opened 3 years ago

Closed 3 years ago

#33392 closed Bug (duplicate)

Removing composed indexes fails on MySQL

Reported by: Sergey Fursov Owned by: nobody
Component: Migrations Version: 4.0
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Removing composed indexes that are being created using UniqueConstraint or Index added to Meta.contraints/Meta.indexes respectively fails on MySQL due to implicit optimization made by MySQL with omitting indexes for a foreign key that were initially included in the index.

The bug might be considered as a regression for the previously fixed #24757

The initial state of example models

from django.db import models

class ModelA(models.Model):
    pass

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

    class Meta:
        constraints = models.UniqueConstraint(fields=['a', 'b'], name='modelb_uniq')
        indexes = models.Index(fields=['a', 'b'], name='modelb_index')

the final state:

from django.db import models

class ModelA(models.Model):
    pass

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

considering the example states of the models above, MySQL first optimizes schema by omitting index for ModelB.a FK field as it is already included in composed index, and then during removing the constraint or the index migration fails with

Cannot drop index 'modelb_uniq': needed in a foreign key constraint"

or

Cannot drop index 'modelb_index': needed in a foreign key constraint"

Change History (2)

comment:1 by Sergey Fursov, 3 years ago

Summary: Removing or composed indexes fails on MySQLRemoving composed indexes fails on MySQL

comment:2 by Mariusz Felisiak, 3 years ago

Resolution: duplicate
Status: newclosed

Thanks! IMO we can close this as a duplicate of #31335 (see Simon's comment).

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