#28305: AlterField migration tries to alter column that still has a foreign key
contraint
-------------------------------------+-------------------------------------
Reporter: Andreas | Owner: nobody
Backx |
Type: | Status: new
Uncategorized |
Component: | Version: master
Migrations | Keywords: mysql,
Severity: Normal | onetoonefield, utf8mb4
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
''A test case has been created here: https://github.com/AndreasBackx
/Django-OneToOneFieldBug-Example. How to run the test case is described
there.''
There is a migration issue when moving from Django 1.10.7 to Django
1.11.2. This sample I made is a slimmed down version from an internal
library which moved from utf8 to utf8mb4 in MySQL. This required the keys
to be changed from 255 characters to 191 characters in order to be
compatible with older versions of MySQL but the reason is irrelevant here.
The issue is that Django 1.11.2+ tries to change the 'id' column of which
the max_length has been changed from 255 to 191 that still has a
constraint. It results in the following error:
{{{
django.db.utils.OperationalError: (1833, "Cannot change column 'id': used
in a foreign key constraint
'myapp_agreement_member_id_0dc75c75_fk_myapp_member_id' of table
'test_onetoone.myapp_agreement'")
}}}
This is the full traceback:
{{{
Applying myapp.0002_utf8mb4...Traceback (most recent call last):
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/backends/utils.py", line 65, in execute
return self.cursor.execute(sql, params)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/backends/mysql/base.py", line 101, in execute
return self.cursor.execute(query, args)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-packages/MySQLdb/cursors.py",
line 250, in execute
self.errorhandler(self, exc, value)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
raise errorvalue
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-packages/MySQLdb/cursors.py",
line 247, in execute
res = self._query(query)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-packages/MySQLdb/cursors.py",
line 411, in _query
rowcount = self._do_query(q)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-packages/MySQLdb/cursors.py",
line 374, in _do_query
db.query(q)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/MySQLdb/connections.py", line 292, 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
'test_onetoone.myapp_agreement'")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "manage.py", line 22, in <module>
execute_from_command_line(sys.argv)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/core/management/__init__.py", line 363, in
execute_from_command_line
utility.execute()
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/core/management/__init__.py", line 355, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/core/management/commands/test.py", line 29, in
run_from_argv
super(Command, self).run_from_argv(argv)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/core/management/base.py", line 283, in run_from_argv
self.execute(*args, **cmd_options)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/core/management/base.py", line 330, in execute
output = self.handle(*args, **options)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/core/management/commands/test.py", line 62, in handle
failures = test_runner.run_tests(test_labels)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/test/runner.py", line 601, in run_tests
old_config = self.setup_databases()
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/test/runner.py", line 546, in setup_databases
self.parallel, **kwargs
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/test/utils.py", line 187, in setup_databases
serialize=connection.settings_dict.get('TEST', {}).get('SERIALIZE',
True),
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/backends/base/creation.py", line 69, in create_test_db
run_syncdb=True,
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/core/management/__init__.py", line 130, in call_command
return command.execute(*args, **defaults)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/core/management/base.py", line 330, in execute
output = self.handle(*args, **options)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/core/management/commands/migrate.py", line 204, in handle
fake_initial=fake_initial,
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/migrations/executor.py", line 115, in migrate
state = self._migrate_all_forwards(state, plan, full_plan, fake=fake,
fake_initial=fake_initial)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/migrations/executor.py", line 145, in
_migrate_all_forwards
state = self.apply_migration(state, migration, fake=fake,
fake_initial=fake_initial)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/migrations/executor.py", line 244, in apply_migration
state = migration.apply(state, schema_editor)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/migrations/migration.py", line 129, in apply
operation.database_forwards(self.app_label, schema_editor, old_state,
project_state)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/migrations/operations/fields.py", line 215, in
database_forwards
schema_editor.alter_field(from_model, from_field, to_field)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/backends/base/schema.py", line 515, in alter_field
old_db_params, new_db_params, strict)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/backends/base/schema.py", line 684, in _alter_field
params,
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/backends/base/schema.py", line 120, in execute
cursor.execute(sql, params)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/backends/utils.py", line 65, in execute
return self.cursor.execute(sql, params)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-packages/django/db/utils.py",
line 94, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/utils/six.py", line 685, in reraise
raise value.with_traceback(tb)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/backends/utils.py", line 65, in execute
return self.cursor.execute(sql, params)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/django/db/backends/mysql/base.py", line 101, in execute
return self.cursor.execute(query, args)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-packages/MySQLdb/cursors.py",
line 250, in execute
self.errorhandler(self, exc, value)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
raise errorvalue
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-packages/MySQLdb/cursors.py",
line 247, in execute
res = self._query(query)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-packages/MySQLdb/cursors.py",
line 411, in _query
rowcount = self._do_query(q)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-packages/MySQLdb/cursors.py",
line 374, in _do_query
db.query(q)
File "/path/to/project/django-onetoone-
test/.tox/py35-django-111/lib/python3.5/site-
packages/MySQLdb/connections.py", line 292, in query
_mysql.connection.query(self, query)
django.db.utils.OperationalError: (1833, "Cannot change column 'id': used
in a foreign key constraint
'myapp_agreement_member_id_0dc75c75_fk_myapp_member_id' of table
'test_onetoone.myapp_agreement'")
}}}
Here is some extra information that I thought might be relevant.
This is the output of ''onetoone/manage.py sqlmigrate myapp 0002'' on
Django 1.11.2 (after resetting the database and running migrate which
failed on the second migration):
{{{
BEGIN;
--
-- Create model Group
--
CREATE TABLE `myapp_group` (`id` varchar(255) NOT NULL PRIMARY KEY);
--
-- Create model Member
--
CREATE TABLE `myapp_member` (`id` varchar(255) NOT NULL PRIMARY KEY,
`group_id` varchar(255) NULL);
--
-- Create model Agreement
--
CREATE TABLE `myapp_agreement` (`id` varchar(255) NOT NULL PRIMARY KEY,
`member_id` varchar(255) NULL UNIQUE);
ALTER TABLE `myapp_member` ADD CONSTRAINT
`myapp_member_group_id_31ff18be_fk_myapp_group_id` FOREIGN KEY
(`group_id`) REFERENCES `myapp_group` (`id`);
ALTER TABLE `myapp_agreement` ADD CONSTRAINT
`myapp_agreement_member_id_0dc75c75_fk_myapp_member_id` FOREIGN KEY
(`member_id`) REFERENCES `myapp_member` (`id`);
COMMIT;
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;
}}}
The same but for Django 1.10.7 (the second migration succeeded here):
{{{
BEGIN;
--
-- Create model Group
--
CREATE TABLE `myapp_group` (`id` varchar(255) NOT NULL PRIMARY KEY);
--
-- Create model Member
--
CREATE TABLE `myapp_member` (`id` varchar(255) NOT NULL PRIMARY KEY,
`group_id` varchar(255) NULL);
--
-- Create model Agreement
--
CREATE TABLE `myapp_agreement` (`id` varchar(255) NOT NULL PRIMARY KEY,
`member_id` varchar(255) NULL UNIQUE);
ALTER TABLE `myapp_member` ADD CONSTRAINT
`myapp_member_group_id_31ff18be_fk_myapp_group_id` FOREIGN KEY
(`group_id`) REFERENCES `myapp_group` (`id`);
ALTER TABLE `myapp_agreement` ADD CONSTRAINT
`myapp_agreement_member_id_0dc75c75_fk_myapp_member_id` FOREIGN KEY
(`member_id`) REFERENCES `myapp_member` (`id`);
COMMIT;
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`;
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;
}}}
This is the diff for these files:
{{{
--- django10 2017-06-13 22:18:21.099594038 +0200
+++ django11 2017-06-13 22:17:38.821556738 +0200
@@ -25,7 +25,7 @@
--
-- Alter field id on member
--
-ALTER TABLE `myapp_agreement` DROP FOREIGN KEY
`myapp_agreement_member_id_0dc75c75_fk`;
+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`);
}}}
I'm not too familiar with the database part of the Django core and
wouldn't know where to begin. I thought to add as much information as I
could, I can provide more if need be, but the test project should allow
you to make your own assessments.
--
Ticket URL: <https://code.djangoproject.com/ticket/28305>
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/055.bfce7e7e8bc536e43ebb305580467e91%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
