id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34217	Migration removing a CheckConstraint results in ProgrammingError using MySQL < 8.0.16.	Max Fisco	Bhuvnesh	"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!

"	Bug	closed	Migrations	4.1	Normal	fixed		David Wobrock	Ready for checkin	1	0	0	0	0	0