AlterField migration tries to alter column that still has a foreign key contraint

[Andreas Backx]

A test case has been created here: ​https://github.com/AndreasBackx/Django-OneToOneFieldBug-Example. How to run the test case is described there. The bug applies to Django 1.11.2 and the master branch of Django. There are tests for 1.10.7 which succeed and tests that fail for 1.11.2 and the master branch.

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.