Migrations crashes with long identifiers on MySQL (8.0.26 )

[Awais Qureshi]

I am trying to upgrade the project from django22 to 32.
Migrate command gives me this error

django.db.utils.OperationalError: (1059, "Identifier name 'blackboard_blackboardlearnerassessmentdatatransmissionaudit_chk_1' is too long")

This issue appeared in django30 also.

I did't find any information related to this change in django32 release notes.

Important point: class BlackboardLearnerAssessmentDataTransmissionAudit(models.Model) is model name and app name is blackboard also.

OS details
mysql Ver 8.0.26-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
Python 3.8
Django 30,31,32

[Mariusz Felisiak]

Thanks for this report, however I don't think you've explained the issue in enough detail to confirm a bug in Django. blackboard_blackboardlearnerassessmentdatatransmissionaudit_chk_1 is longer than the maximum identifier name on MySQL. Are you using the built-in backend for MySQL? Can you provide a minimum project to reproduce?

I didn't find any information related to this change in django32 release notes.

Have you checked release notes for Django 3.0 and 3.1?

[Awais Qureshi]

I am using mysqlclient==2.0.3 and all django defaults packages/backends.

Yes I have checked django30 and 31 release notes, but no information available with this change. Same migration is working fine in django22.

blackboard_blackboardlearnerassessmentdatatransmissionaudit_chk_1 it has 65 characters but seems fine with django22.

[Mariusz Felisiak]

... it has 65 characters but seems fine with django22.

This error is not raised by Django, but by MySQL. Can you provide a minimum project to reproduce?

[Jacob Walls]

Have you verified the model field referred to is still in your project? I can produce your error if I'm executing a previously written migration but where the field is no longer in my project. Instead, if the field is in my project, I get this upon migrate:

SystemCheckError: System check identified some issues:

ERRORS:
x.xxxx: (models.E018) Autogenerated column name too long for field "blackboard_blackboardlearnerassessmentdatatransmissionaudit_chk_1". Maximum length is "64" for database "default".
        HINT: Set the column name manually using 'db_column'.

This system check predates 2.2, so if the field is in your project and no system check is raised, that would be a false negative to address. But we would need a minimal project to verify that the field exists.

[Awais Qureshi]

My information was wrong. On githubactions by default mysql is 8.0.26 but on production we have mysql5.7. That was the root cause of the failure.

Field is available and every thing is fine but mysql8 triggers this error and it started appearing in django30 and upper versions.

Thanks for debugging it helps me a-lot to find out the actual root cause. Downgrading mysql to 5.7 fixed the problem.
Updating the ticket description also.

[Awais Qureshi]

I have added all required information. You can consider re-opening this issue.

[Mariusz Felisiak]

Awais, I cannot reproduce this issue in MySQL 8 or 5.7 you also didn't provide a minimum project to reproduce. I don't think you've explained the issue in enough detail to confirm a bug in Django. Please reopen the ticket if you can debug your issue and provide details about why and where Django is at fault.

[Awais Qureshi]

It's not a django issue. as per mysql8.0 ​https://dev.mysql.com/doc/refman/8.0/en/identifier-length.html

For constraint definitions that include no constraint name, the server internally generates a name derived from the associated table name. For example, internally generated foreign key and CHECK constraint names consist of the table name plus _ibfk_ or _chk_ and a number. If the table name is close to the length limit for constraint names, the additional characters required for the constraint name may cause that name to exceed the limit, resulting in an error.

My model is as follows

class BlackboardLearnerAssessmentDataTransmissionAudit(models.Model):
    enterprise_course_enrollment_id = models.PositiveIntegerField(
        blank=False,
        null=False,
        db_index=True
    )

In mysql8 it generate this query with a check enterprise_cou_enrollment_id integer UNSIGNED NOT NULL CHECK (enterprise_cou_enrollment_id >= 0) having this name blackboard_blackboardlearnerassessmentdatatransmissionaudit_chk_1 ( 65 characters ) and it throws the error.

[Awais Qureshi]

I have created a simple app to reproduce this issue. You can see three github checks are running and django30 and django32 are showing error. Since this feature was introduced in django30.

This change is related with Added support for check constraints on MySQL 8.0.16+. ​https://github.com/django/django/pull/11743

Sample project ​https://github.com/awais786/mysite/pull/5 with long table name where mysql8 and django is generating internal checks if model has PositiveIntegerField.

Possible solution:
In case of makemigrations trigger some error with max length or truncate the check name.
In case of upgrading existing project from mysql57 to mysql80 show some valid error message during migrate command.

[Mariusz Felisiak]

Thanks for extra details and a sample project.

I was able to reproduce this error, however Django is not at fault, it seems to be an issue in MySQL itself. Django executes:

CREATE TABLE `ticket_33169_whiteboxstudentsexaminationdatatransferauditionf7bf` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `unique_student_enrollment_id` integer UNSIGNED NOT NULL CHECK (`unique_student_enrollment_id` >= 0)
)

(I called my app ticket_33169) so ticket_33169_whiteboxstudentsexaminationdatatransferauditionf7bf_chk_1 is a internal name generated by MySQL. Surprisingly, MySQL doesn't respect its own limitations.