Migration removing a CheckConstraint results in ProgrammingError using MySQL < 8.0.16.

[Max Fisco]

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!

[Mariusz Felisiak]

Thanks for the report. Agreed, removing a check constraint should be no-op when not supported, the following diff fix it for me:

django/db/backends/base/schema.py

@@ -1667 +1667 @@
         )
 
     def _delete_check_sql(self, model, name):
+        if not self.connection.features.supports_table_check_constraints:
+            return None
         return self._delete_constraint_sql(self.sql_delete_check, model, name)
 
     def _delete_constraint_sql(self, template, model, name):

Would you like to prepare a patch? (a regression test in migrations.test_operations.OperationTests is required.)

[Max Fisco]

Hi Mariusz,

Thanks for the quick response! As far as the patch goes, I'd love to contribute (first time reporting a bug) but I think it'd be better to let someone else pick it up.

I tried to by following along with ​https://docs.djangoproject.com/en/dev/intro/contributing/#writing-your-first-patch-for-django; however, I'm having some difficulty figuring out how to just get the tests to run using MySQL 5.7 in the first place 😕.

  File "/Users/maxwellfisco/django-fork/django/django/db/backends/base/base.py", line 214, in check_database_version_supported
    raise NotSupportedError(
django.db.utils.NotSupportedError: MySQL 8 or later is required (found 5.7.40).

[Mariusz Felisiak]

Replying to Max Fisco:

Hi Mariusz,

Thanks for the quick response! As far as the patch goes, I'd love to contribute (first time reporting a bug) but I think it'd be better to let someone else pick it up.

I tried to by following along with ​https://docs.djangoproject.com/en/dev/intro/contributing/#writing-your-first-patch-for-django; however, I'm having some difficulty figuring out how to just get the tests to run using MySQL 5.7 in the first place 😕.

  File "/Users/maxwellfisco/django-fork/django/django/db/backends/base/base.py", line 214, in check_database_version_supported
    raise NotSupportedError(
django.db.utils.NotSupportedError: MySQL 8 or later is required (found 5.7.40).

Django 4.2 no longer supports MySQL < 8, so you need to use MySQL 8 to test against the current main branch.

[Bhuvnesh]

Hi Max, if you are working on this issue please assign it to yourself, if not, i would like to submit a patch. 🙂

[Max Fisco]

Replying to Bhuvnesh:

Hi Max, if you are working on this issue please assign it to yourself, if not, i would like to submit a patch. 🙂

Hi Bhuvnesh, I apologize for leaving it ambiguous, go ahead and submit the patch.

[Bhuvnesh]

Okay ,Thanks !

[Jacob Walls]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In 68ef274b:

Fixed #34217 -- Fixed migration crash when removing check constraints on MySQL < 8.0.16.

[GitHub <noreply@…>]

In 16c966ff:

Refs #30060, Refs #34217 -- Made SchemaEditor not generate SQL for CheckConstraint if not supported.

The new logic mirrors the logic in SchemaEditor._delete_check_sql()
added in 68ef274bc505cd44f305c03cbf84cf08826200a8.

Thanks Tim Graham for the report.