[mysql] Django loses track of renamed table when recreating a foreign key, resulting in "Table 'foo.bar' doesn't exist"

[Stephen Finucane]

I'm using concrete inheritance and attempting to merge a "child" model back into its parent. To do this, I must duplicate all fields from the child model into the parent model, then delete the parent and rename the child. Unfortunately this fails on Django 1.11 because it the migration machinery attempts to create some foreign key constraints using the old name for the child table.

Note: I was unable to reproduce this bug with 2.0 or higher, which means this has since been resolved. However, I wasn't able to find a bug nor identify the change that fixed it though so I'm filing in case anyone else stumbles upon this. I expect it to be closed but I'd really appreciate pointers to the actual fix since not being able to identify it myself is annoying :-)

---

Create a parent and child model using concrete inheritance, where the child table has a foreign key:

from django.db import models
import django.db.models.deletion


class Foo(models.Model):

    name = models.CharField(max_length=255, blank=True, null=False)


class Bar(Foo):

    baz = models.ForeignKey(
        'Baz',
        blank=True,
        null=True,
        on_delete=django.db.models.deletion.CASCADE,
    )


class Baz(models.Model):

    title = models.CharField(max_length=255, blank=True, null=False)

Create migrations and some sample data:

$ python manage.py makemigrations
$ python manage.py migrate
$ python manage.py shell
>>> from core.models import Foo, Bar, Baz
>>> baz = Baz(title='a cool title').save()
>>> Bar(name='test', baz=baz).save()
>>> quit()

Now attempt to fold the child back into the parent but keep the child model's name (it's not possible to do it the other way due to bug #23521), like so:

from django.db import models
import django.db.models.deletion


class Bar(models.Model):

    name = models.CharField(max_length=255, blank=True, null=False)
    baz = models.ForeignKey(
        'Baz',
        blank=True,
        null=True,
        on_delete=django.db.models.deletion.CASCADE,
    )


class Baz(models.Model):

    title = models.CharField(max_length=255, blank=True, null=False)

I did this by hand with the following migration:

from django.db import migrations, models
import django.db.models.deletion

class Migration(migrations.Migration):

    dependencies = [
        ('core', '0001_initial'),
    ]

    operations = [
        migrations.RenameField(
            model_name='bar',
            old_name='baz',
            new_name='baz2',
        ),
        migrations.AddField(
            model_name='foo',
            name='baz',
            field=models.ForeignKey(
                blank=True,
                null=True,
                on_delete=django.db.models.deletion.CASCADE,
                to='core.Baz',
            ),
        ),
        migrations.RemoveField(
            model_name='bar',
            name='baz2',
        ),
        migrations.DeleteModel(
            name='Bar',
        ),
        migrations.RenameModel(
            old_name='Foo',
            new_name='Bar',
        ),
    ]

Execute this migration. It will fail:

$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, core, sessions
Running migrations:
  Applying core.0002_merge_foo_bar...Traceback (most recent call last):
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/db/backends/mysql/base.py", line 101, in execute
    return self.cursor.execute(query, args)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (1146, "Table 'testdb.core_foo' doesn't exist")

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 "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/core/management/__init__.py", line 356, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/tmp/django-bug2/venv/lib/python3.7/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 "/tmp/django-bug2/venv/lib/python3.7/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 "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/db/backends/base/schema.py", line 109, in __exit__
    self.execute(sql)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/db/backends/base/schema.py", line 136, in execute
    cursor.execute(sql, params)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/django/db/backends/mysql/base.py", line 101, in execute
    return self.cursor.execute(query, args)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/tmp/django-bug2/venv/lib/python3.7/site-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1146, "Table 'testdb.core_foo' doesn't exist")

Sticking in some debug logging into the 'execute' function in 'django.db.backends.utils', I see that's because the foreign key constraint it's generating it using the wrong table:

ALTER TABLE `core_foo` ADD CONSTRAINT `core_foo_baz_id_59f0d7c7_fk_core_baz_id` FOREIGN KEY (`baz_id`) REFERENCES `core_baz` (`id`)

On Django 2.0 and better, it uses the correct name:

ALTER TABLE `core_bar` ADD CONSTRAINT `core_bar_baz_id_26792e0f_fk_core_baz_id` FOREIGN KEY (`baz_id`) REFERENCES `core_baz` (`id`)

[Stephen Finucane]

I can workaround this issue by moving the removal of the child class and rename of the parent into a separate migration:

from django.db import migrations, models
import django.db.models.deletion

class Migration(migrations.Migration):

    dependencies = [
        ('core', '002_merge_foo_bar_part_a'),
    ]

    operations = [
        migrations.DeleteModel(
            name='Bar',
        ),
        migrations.RenameModel(
            old_name='Foo',
            new_name='Bar',
        ),
    ]

Again, just in case anyone else stumbles upon this.

[Simon Charette]

I was unable to reproduce this bug with 2.0 or higher, which means this has since been resolved. However, I wasn't able to find a bug nor identify the change that fixed it though so I'm filing in case anyone else stumbles upon this. I expect it to be closed but I'd really appreciate pointers to the actual fix since not being able to identify it myself is annoying :-)

You're looking for #25530 (b50815ee418b38e719476c2d5f6e2bc69f686927).

Please TicketClosingReasons/UseSupportChannels to get help doing that in the future.