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 )
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 , 3 years ago
Resolution: | → needsinfo |
---|---|
Severity: | Release blocker → Normal |
Status: | new → closed |
Summary: | migrations fails to run on django32 with mysql5.7 → Migrations crashes with long identifiers on MySQL. |
Type: | Uncategorized → Bug |
comment:2 by , 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 , 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 , 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 , 3 years ago
Description: | modified (diff) |
---|
comment:6 by , 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 , 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 , 3 years ago
Description: | modified (diff) |
---|
comment:9 by , 3 years ago
Resolution: | needsinfo |
---|---|
Status: | closed → new |
I have added all required information. You can consider re-opening this issue.
comment:10 by , 3 years ago
Resolution: | → needsinfo |
---|---|
Status: | new → closed |
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 , 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.
comment:12 by , 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.
comment:13 by , 3 years ago
Resolution: | needsinfo |
---|---|
Status: | closed → new |
comment:14 by , 3 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
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.
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?Have you checked release notes for Django 3.0 and 3.1?