Opened 3 years ago

Closed 3 years ago

#33169 closed Bug (invalid)

Migrations crashes with long identifiers on MySQL (8.0.26 )

Reported by: Awais Qureshi Owned by: nobody
Component: Migrations Version: 3.2
Severity: Normal Keywords: django32, mysql8.0.26
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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

Change History (14)

comment:1 by Mariusz Felisiak, 3 years ago

Resolution: needsinfo
Severity: Release blockerNormal
Status: newclosed
Summary: migrations fails to run on django32 with mysql5.7Migrations crashes with long identifiers on MySQL.
Type: UncategorizedBug

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?

comment:2 by Awais Qureshi, 3 years ago

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.

comment:3 by Mariusz Felisiak, 3 years ago

... 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?

comment:4 by Jacob Walls, 3 years ago

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.

comment:5 by Awais Qureshi, 3 years ago

Description: modified (diff)

comment:6 by Awais Qureshi, 3 years ago

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.

comment:7 by Awais Qureshi, 3 years ago

Description: modified (diff)
Keywords: mysql8.0.26 added
Summary: Migrations crashes with long identifiers on MySQL.Migrations crashes with long identifiers on MySQL (8.0.26 )

comment:8 by Awais Qureshi, 3 years ago

Description: modified (diff)

comment:9 by Awais Qureshi, 3 years ago

Resolution: needsinfo
Status: closednew

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

comment:10 by Mariusz Felisiak, 3 years ago

Resolution: needsinfo
Status: newclosed

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.

comment:11 by Awais Qureshi, 3 years ago

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.

Last edited 3 years ago by Tim Graham (previous) (diff)

comment:12 by Awais Qureshi, 3 years ago

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.

Last edited 3 years ago by Awais Qureshi (previous) (diff)

comment:13 by Awais Qureshi, 3 years ago

Resolution: needsinfo
Status: closednew

comment:14 by Mariusz Felisiak, 3 years ago

Resolution: invalid
Status: newclosed

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.

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