#28305: AlterField migration tries to alter column that still has a foreign key
contraint
-------------------------------------+-------------------------------------
Reporter: Andreas Backx | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: master
Severity: Release blocker | Resolution:
Keywords: mysql, | Triage Stage: Accepted
onetoonefield, utf8mb4, foreign |
key |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Markus Holtermann):
Thanks for the report Andreas. I took a shot at this. Here's what I found:
* This issue doesn't seem to manifest on PostgreSQL
* The SQL created in `sqlmigrate` (which you posted above) works (you can
run that manually and the constraints get dropped and recreated as
intended)
* `manage.py migrate` for whatever reason does _not_ run the `ALTER TABLE
myapp_agreement DROP FOREIGN KEY
myapp_agreement_member_id_0dc75c75_fk_myapp_member_id;` on 1.11.x which is
due to `_related_non_m2m_objects()` in
https://github.com/django/django/blob/stable/1.11.x/django/db/backends/base/schema.py#L551
not returning related objects.
{{{
$ python manage.py sqlmigrate myapp 0002 --settings
onetoone.mysql_settings
>
/home/markus/Coding/django/django/db/backends/base/schema.py(549)_alter_field()
548 import ipdb; ipdb.set_trace()
--> 549 if old_field.primary_key and new_field.primary_key and
old_type != new_type:
550 # '_meta.related_field' also contains M2M reverse
fields, these
ipdb> ipdb> tuple(_related_non_m2m_objects(old_field, new_field))
((<ManyToOneRel: myapp.member>, <ManyToOneRel: myapp.member>),)
ipdb> c
ALTER TABLE `myapp_member` DROP FOREIGN KEY
`myapp_member_group_id_31ff18be_fk`; (params ())
ALTER TABLE `myapp_group` MODIFY `id` varchar(191) NOT NULL; (params [])
ALTER TABLE `myapp_member` MODIFY `group_id` varchar(191) NULL; (params
[])
ALTER TABLE `myapp_member` ADD CONSTRAINT
`myapp_member_group_id_31ff18be_fk` FOREIGN KEY (`group_id`) REFERENCES
`myapp_group` (`id`); (params ())
>
/home/markus/Coding/django/django/db/backends/base/schema.py(549)_alter_field()
548 import ipdb; ipdb.set_trace()
--> 549 if old_field.primary_key and new_field.primary_key and
old_type != new_type:
550 # '_meta.related_field' also contains M2M reverse
fields, these
ipdb> tuple(_related_non_m2m_objects(old_field, new_field))
((<OneToOneRel: myapp.agreement>, <OneToOneRel: myapp.agreement>),)
ipdb> c
ALTER TABLE `myapp_agreement` DROP FOREIGN KEY
`myapp_agreement_member_id_0dc75c75_fk_myapp_member_id`; (params ())
ALTER TABLE `myapp_member` MODIFY `id` varchar(191) NOT NULL; (params [])
ALTER TABLE `myapp_agreement` MODIFY `member_id` varchar(191) NULL;
(params [])
ALTER TABLE `myapp_agreement` ADD CONSTRAINT
`myapp_agreement_member_id_0dc75c75_fk` FOREIGN KEY (`member_id`)
REFERENCES `myapp_member` (`id`); (params ())
>
/home/markus/Coding/django/django/db/backends/base/schema.py(549)_alter_field()
548 import ipdb; ipdb.set_trace()
--> 549 if old_field.primary_key and new_field.primary_key and
old_type != new_type:
550 # '_meta.related_field' also contains M2M reverse
fields, these
ipdb> c
ALTER TABLE `myapp_agreement` MODIFY `id` varchar(191) NOT NULL; (params
[])
BEGIN;
--
-- Alter field id on group
--
ALTER TABLE `myapp_member` DROP FOREIGN KEY
`myapp_member_group_id_31ff18be_fk`;
ALTER TABLE `myapp_group` MODIFY `id` varchar(191) NOT NULL;
ALTER TABLE `myapp_member` MODIFY `group_id` varchar(191) NULL;
ALTER TABLE `myapp_member` ADD CONSTRAINT
`myapp_member_group_id_31ff18be_fk` FOREIGN KEY (`group_id`) REFERENCES
`myapp_group` (`id`);
--
-- Alter field id on member
--
ALTER TABLE `myapp_agreement` DROP FOREIGN KEY
`myapp_agreement_member_id_0dc75c75_fk_myapp_member_id`;
ALTER TABLE `myapp_member` MODIFY `id` varchar(191) NOT NULL;
ALTER TABLE `myapp_agreement` MODIFY `member_id` varchar(191) NULL;
ALTER TABLE `myapp_agreement` ADD CONSTRAINT
`myapp_agreement_member_id_0dc75c75_fk` FOREIGN KEY (`member_id`)
REFERENCES `myapp_member` (`id`);
--
-- Alter field id on agreement
--
ALTER TABLE `myapp_agreement` MODIFY `id` varchar(191) NOT NULL;
COMMIT;
}}}
{{{
$ python manage.py migrate myapp 0002 --settings onetoone.mysql_settings
System check identified some issues:
WARNINGS:
?: (mysql.W002) MySQL Strict Mode is not set for database connection
'default'
HINT: MySQL's Strict Mode fixes many data integrity problems in
MySQL, such as data truncation upon insertion, by escalating warnings into
errors. It is strongly recommended you activate it. See:
https://docs.djangoproject.com/en/dev/ref/databases/#mysql-sql-mode
Operations to perform:
Target specific migration: 0002_utf8mb4, from myapp
Running migrations:
Applying myapp.0002_utf8mb4...>
/home/markus/Coding/django/django/db/backends/base/schema.py(549)_alter_field()
548 import ipdb; ipdb.set_trace()
--> 549 if old_field.primary_key and new_field.primary_key and
old_type != new_type:
550 # '_meta.related_field' also contains M2M reverse
fields, these
ipdb> tuple(_related_non_m2m_objects(old_field, new_field))
((<ManyToOneRel: myapp.member>, <ManyToOneRel: myapp.member>),)
ipdb> c
ALTER TABLE `myapp_member` DROP FOREIGN KEY
`myapp_member_group_id_31ff18be_fk`; (params ())
ALTER TABLE `myapp_group` MODIFY `id` varchar(191) NOT NULL; (params [])
ALTER TABLE `myapp_member` MODIFY `group_id` varchar(191) NULL; (params
[])
ALTER TABLE `myapp_member` ADD CONSTRAINT
`myapp_member_group_id_31ff18be_fk` FOREIGN KEY (`group_id`) REFERENCES
`myapp_group` (`id`); (params ())
>
/home/markus/Coding/django/django/db/backends/base/schema.py(549)_alter_field()
548 import ipdb; ipdb.set_trace()
--> 549 if old_field.primary_key and new_field.primary_key and
old_type != new_type:
550 # '_meta.related_field' also contains M2M reverse
fields, these
ipdb> tuple(_related_non_m2m_objects(old_field, new_field))
()
ipdb> c
ALTER TABLE `myapp_member` MODIFY `id` varchar(191) NOT NULL; (params [])
Traceback (most recent call last):
File "/home/markus/Coding/django/django/db/backends/utils.py", line 65,
in execute
return self.cursor.execute(sql, params)
File "/home/markus/Coding/django/django/db/backends/mysql/base.py", line
101, in execute
return self.cursor.execute(query, args)
File "/home/markus/.venvs/django-py35/lib/python3.5/site-
packages/MySQLdb/cursors.py", line 226, in execute
self.errorhandler(self, exc, value)
File "/home/markus/.venvs/django-py35/lib/python3.5/site-
packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorvalue
File "/home/markus/.venvs/django-py35/lib/python3.5/site-
packages/MySQLdb/cursors.py", line 217, in execute
res = self._query(query)
File "/home/markus/.venvs/django-py35/lib/python3.5/site-
packages/MySQLdb/cursors.py", line 378, in _query
rowcount = self._do_query(q)
File "/home/markus/.venvs/django-py35/lib/python3.5/site-
packages/MySQLdb/cursors.py", line 341, in _do_query
db.query(q)
File "/home/markus/.venvs/django-py35/lib/python3.5/site-
packages/MySQLdb/connections.py", line 280, in query
_mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (1833, "Cannot change column 'id':
used in a foreign key constraint
'myapp_agreement_member_id_0dc75c75_fk_myapp_member_id' of table
'django.myapp_agreement'")
}}}
Both commands are run on `1.11.3.dev20170617223301`
--
Ticket URL: <https://code.djangoproject.com/ticket/28305#comment:10>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/070.7b109893c949b40f06054fa4a112bc3b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
