[Django] #34217: Migration removing a CheckConstraint results in ProgrammingError using MySQL (5.7.31)

[Django]

#34217: Migration removing a CheckConstraint results in ProgrammingError using
MySQL (5.7.31)
--------------------------------------+------------------------
Reporter: Max F. | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 4.1
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
--------------------------------------+------------------------
We are working on migrating from MySQL to PostgreSQL and a prerequisite
step is to fix a `CheckConstraint` on an existing model:

{{{

class MyModel(models.Model):
...
effective_date = models.DateField(verbose_name="Effective Date")
expiration_date = models.DateField(verbose_name="Expiration Date")

class Meta:
constraints = [
models.CheckConstraint(
check=models.Q(expiration_date__gt=models.F("effective_date")),
name="check_effective_date_before_expiration_date",
),
...
]

}}}

To do so we did the following:
1. Removed the check constraint from the model's definition
2. Made a new migration to drop the constraint
3. Added the "fixed" check constraint to the model's definition
4. Made a new migration to create the new constraint

Since check constraints are ignored on MySQL 5.7.31, we expected these two
new migrations have no effect. Instead, we are encountering the following
error:

{{{
MySQLdb.ProgrammingError: (1064, "You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to
use near 'CHECK `check_effective_date_before_expiration_date`' at line
1")
}}}

The sql code generated by the migration removing the constraint (`/.
manage.py sqlmigrate myapp 0002`) produces the following:

{{{
-- Remove constraint check_effective_date_before_expiration_date from
model mymodel
--
ALTER TABLE `myapp_mymodel` DROP CHECK
`check_effective_date_before_expiration_date`;
}}}

So it seems that the migration is trying to drop the constraint even
though it doesn't exist in the database. As a workaround, we can fake the
"constraint removal" migration (`./manage.py migrate --fake myapp 0002` )
but could this be a bug or are is there something we are missing?

Thanks!

--
Ticket URL: <https://code.djangoproject.com/ticket/34217>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.