#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 [email protected].
To post to this group, send email to [email protected].
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.
