#31335: [mysql] Renaming a foreign key field that's also included in an index
fails
with "Cannot drop index 'foo_idx': needed in a foreign key constraint"
--------------------------------------------+------------------------
Reporter: Stephen Finucane | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 3.0
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
--------------------------------------------+------------------------
I have a foreign key field on a model that I wish to rename. Unfortunately
that field is included in an index. Attempting to rename this generates
the following error.
{{{
MySQLdb._exceptions.OperationalError: (1553, "Cannot drop index 'foo_idx':
needed in a foreign key constraint")
}}}
I've included reproduction steps along with a workaround below. This is an
issue on both 3.0.3 and 1.11.28.
----
Create a sample project and application:
{{{
$ virtualenv venv
$ pip install django mysqlclient
$ django-admin startproject test
$ cd test/
$ django-admin startapp core
}}}
Configure `test/settings.py` to use the `mysql` backend and enable the
`core` app, then create some sample models:
{{{
cat <<< EOF > core/models.py
import datetime
from django.db import models
class Article(models.Model):
name = models.CharField(max_length=255, blank=True, null=False)
date = models.DateTimeField(default=datetime.datetime.utcnow)
body = models.TextField(blank=True, null=False)
class Comment(models.Model):
article = models.ForeignKey(
Article,
related_name='comments',
related_query_name='comment',
on_delete=models.CASCADE,
)
date = models.DateTimeField(default=datetime.datetime.utcnow)
body = models.TextField(blank=True, null=False)
class Meta:
indexes = [
# This is a covering index for the /list/ query
models.Index(
fields=['article', 'date'],
name='comment_list_covering_idx',
),
]
EOF
}}}
Once done, create and apply the migrations, then create some entries:
{{{
$ python manage.py makemigrations
$ python manage.py migrate
$ python manage.py shell
>>> from core.models import Article, Comment
>>> article_a = Article(name='Sample article', body='foo').save()
>>> article_b = Article(name='Another article', body='bar').save()
>>> comment_1 = Comment(article=article_a)
>>> comment_2 = Comment(article=article_b, body='moar stuff')
}}}
Rename the foreign key field, for example from `article` to `foo`,
updating the index in the process, and generate the migration:
{{{
$ python manage.py makemigrations
Did you rename comment.article to comment.foo (a ForeignKey)? [y/N] y
Migrations for 'core':
core/migrations/0002_auto_20200303_1424.py
- Remove index comment_list_covering_idx from comment
- Rename field article on comment to foo
- Create index comment_list_covering_idx on field(s) foo, date of
model comment
}}}
Attempt to run that migration. It will fail:
{{{
$ python manage.py migrate
Operations to perform:
Apply all migrations: admin, auth, contenttypes, core, sessions
Running migrations:
Applying core.0002_auto_20200303_1424...Traceback (most recent call
last):
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/backends/mysql/base.py", line 74, in execute
return self.cursor.execute(query, args)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/MySQLdb/connections.py", line 239, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1553, "Cannot drop index
'comment_list_covering_idx': needed in a foreign key constraint")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "manage.py", line 21, in <module>
main()
File "manage.py", line 17, in main
execute_from_command_line(sys.argv)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/core/management/__init__.py", line 401, in
execute_from_command_line
utility.execute()
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/core/management/__init__.py", line 395, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/core/management/base.py", line 328, in run_from_argv
self.execute(*args, **cmd_options)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/core/management/base.py", line 369, in execute
output = self.handle(*args, **options)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/core/management/base.py", line 83, in wrapped
res = handle_func(*args, **kwargs)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/core/management/commands/migrate.py", line 233, in handle
fake_initial=fake_initial,
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/migrations/executor.py", line 117, in migrate
state = self._migrate_all_forwards(state, plan, full_plan,
fake=fake, fake_initial=fake_initial)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/migrations/executor.py", line 147, in
_migrate_all_forwards
state = self.apply_migration(state, migration, fake=fake,
fake_initial=fake_initial)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/migrations/executor.py", line 245, in apply_migration
state = migration.apply(state, schema_editor)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/migrations/migration.py", line 124, in apply
operation.database_forwards(self.app_label, schema_editor,
old_state, project_state)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/migrations/operations/models.py", line 783, in
database_forwards
schema_editor.remove_index(model, index)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/backends/base/schema.py", line 356, in remove_index
self.execute(index.remove_sql(model, self))
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/backends/base/schema.py", line 142, in execute
cursor.execute(sql, params)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/backends/utils.py", line 100, in execute
return super().execute(sql, params)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/backends/utils.py", line 68, in execute
return self._execute_with_wrappers(sql, params, many=False,
executor=self._execute)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/utils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/django/db/backends/mysql/base.py", line 74, in execute
return self.cursor.execute(query, args)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/tmp/django-bug/venv/lib/python3.7/site-
packages/MySQLdb/connections.py", line 239, in query
_mysql.connection.query(self, query)
django.db.utils.OperationalError: (1553, "Cannot drop index
'comment_list_covering_idx': needed in a foreign key constraint")
}}}
The only workaround I've seen is to use `AlterField` to remove the fk
constraint on the `article` field before doing this work, then use
`AlterField` again after to re-add it. Perhaps that needs to happen under
the hood if MySQL can't do this?
Output of pip freeze and the migrations below:
{{{
$ pip freeze
asgiref==3.2.3
Django==3.0.3
mysqlclient==1.4.6
pytz==2019.3
sqlparse==0.3.1
}}}
{{{
$ cat core/migrations/0001_initial.py
# Generated by Django 3.0.3 on 2020-03-03 14:22
import datetime
from django.db import migrations, models
import django.db.models.deletion
class Migration(migrations.Migration):
initial = True
dependencies = [
]
operations = [
migrations.CreateModel(
name='Article',
fields=[
('id', models.AutoField(auto_created=True,
primary_key=True, serialize=False, verbose_name='ID')),
('name', models.CharField(blank=True,
max_length=255)),
('date',
models.DateTimeField(default=datetime.datetime.utcnow)),
('body', models.TextField(blank=True)),
],
),
migrations.CreateModel(
name='Comment',
fields=[
('id', models.AutoField(auto_created=True,
primary_key=True, serialize=False, verbose_name='ID')),
('date',
models.DateTimeField(default=datetime.datetime.utcnow)),
('body', models.TextField(blank=True)),
('article',
models.ForeignKey(on_delete=django.db.models.deletion.CASCADE,
related_name='comments', related_query_name='comment',
to='core.Article')),
],
),
migrations.AddIndex(
model_name='comment',
index=models.Index(fields=['article', 'date'],
name='comment_list_covering_idx'),
),
]
}}}
{{{
$ cat core/migrations/0002_auto_20200303_1424.py
# Generated by Django 3.0.3 on 2020-03-03 14:24
from django.db import migrations, models
class Migration(migrations.Migration):
dependencies = [
('core', '0001_initial'),
]
operations = [
migrations.RemoveIndex(
model_name='comment',
name='comment_list_covering_idx',
),
migrations.RenameField(
model_name='comment',
old_name='article',
new_name='foo',
),
migrations.AddIndex(
model_name='comment',
index=models.Index(fields=['foo', 'date'],
name='comment_list_covering_idx'),
),
]
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31335>
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/053.a8190492a52e72a0edafce5b488fb813%40djangoproject.com.
