#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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/053.a8190492a52e72a0edafce5b488fb813%40djangoproject.com.
