Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#30591 closed Bug (fixed)

MySQL: 1215 - "Cannot add foreign key constraint" when altering type of unique field referenced by ForeignKey.

Reported by: Cornelis Poppema Owned by: Adnan Umer
Component: Migrations Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I have two models and a ForeignKey. In this project I'm still using django 1.11 (still busy migrating to python3) but I have reproduced this in django 2.2.2

Models are Call and Client. Call has a ForeignKey to a PositiveIntegerField on Client. I'm changing the type of this field to a CharField, but django/mysql cannot drop or add constraints.

I found this issue: https://code.djangoproject.com/ticket/28305. I had hoped I was still using a version of 1.11 that didn't include the backport but unfortunately for me I did.
Before creating this ticket I also came across https://code.djangoproject.com/ticket/30152 which is also related to my initial findings. As https://code.djangoproject.com/ticket/30152#comment:10 says, the internals are checking for relations that might have constraints, but in 30152's and my case it doesn't find the relation. As a result the foreign key constraint isn't dropped resulting in the error:

django.db.utils.OperationalError: (1833, "Cannot change column 'code': used in a foreign key constraint 'myapp_call_clientcode_907d4acf_fk_myapp_client_code' of table 'test_test.myapp_call'")

After either applying the patch https://code.djangoproject.com/attachment/ticket/30152/testcase_and_rough_fix.patch or simply (temporarily) removing related_name='+' I get past this OperationError. Instead I am confronted with:

django.db.utils.IntegrityError: (1215, 'Cannot add foreign key constraint')

From what I've read about MySQL constraints this is caused by the different field types. Changing the type of the original field doesn't change the type of the foreign key field and the constraint cannot be re-added after dropping it.

I created a project on github: https://github.com/cpoppema/django-migrate-constraint-bug, ignore the master-branch for now.

Branch with original issue: https://github.com/cpoppema/django-migrate-constraint-bug/tree/cannot-change-column-used-in-a-foreign-key-constraint
Branch with issue without related_name='+': https://github.com/cpoppema/django-migrate-constraint-bug/tree/cannot-add-foreign-key-constraint

Known workaround that I've come up with is:

run a migration to change ForeignKey on Call to PositiveIntegerField
run a migration to change the PositiveIntegerField on Client to CharField
run a migration to change the PositiveIntegerField on Call to ForeignKey

With this, both fields end up as a CharField. I'd rather have Django generate either multiple migrations or do an ALTER TABLE for related fields.

Attachments (1)

ticket-30591.diff (2.5 KB ) - added by Mariusz Felisiak 5 years ago.

Download all attachments as: .zip

Change History (12)

comment:1 by Mariusz Felisiak, 5 years ago

Summary: bug in manage.py migrate when changing the field type of a foreign key with a constraintMySQL: 1215 - "Cannot add foreign key constraint" when altering type of unique field referenced by ForeignKey.
Triage Stage: UnreviewedAccepted
Version: 2.2master

Thanks for detailed report. #30152 is related but it doesn't fix the main issue. I attached tests for both scenarios.

Reproduced at 8454f6dea49dddb821bfcb7569ea222bb487dfd1.

by Mariusz Felisiak, 5 years ago

Attachment: ticket-30591.diff added

comment:2 by Adnan Umer, 5 years ago

Owner: changed from nobody to Adnan Umer
Status: newassigned

comment:3 by Adnan Umer, 5 years ago

Has patch: set

comment:4 by Mariusz Felisiak, 5 years ago

Patch needs improvement: set

comment:5 by GitHub <noreply@…>, 5 years ago

In 579909a1:

Refs #30591 -- Fixed introspection of check and unique column constraints on MariaDB.

Unnamed unique and check columns constraints have the same name as
a column. Ensure uniqueness by using custom names.

Thanks Adnan Umer for the report.

comment:6 by Adnan Umer, 5 years ago

Patch needs improvement: unset

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

Resolution: fixed
Status: assignedclosed

In 241deed2:

Fixed #30591 -- Fixed recreation of foreign key constraints on MySQL when altering type of referenced unique field.

Thanks Mariusz Felisiak for tests and Matthijs Kooijman for
investigation and initial patch.

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

In acb1172:

[3.0.x] Fixed #30591 -- Fixed recreation of foreign key constraints on MySQL when altering type of referenced unique field.

Thanks Mariusz Felisiak for tests and Matthijs Kooijman for
investigation and initial patch.

Backport of 241deed2590bcb1d8c45271d44c86eaedfb57119 from master

comment:9 by GitHub <noreply@…>, 5 years ago

In 937ddaff:

Refs #30591 -- Fixed too long identifier crash in migrations.test_operations on MySQL 8.0.16+.

comment:10 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

In 8ce932a:

[3.0.x] Refs #30591 -- Fixed too long identifier crash in migrations.test_operations on MySQL 8.0.16+.

Backport of 937ddaff55efbfb4a9560603bbc8c5b17d9e1364 from master

comment:11 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

In 5220ca8:

Refs #30591 -- Adjusted table rebuild for non-pk relationship on SQLite.

The existing code was only accounting for primary key changes and not
all unique key fields that can be referenced.

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