MySQL: "Cannot change column 'id': used in a foreign key constraint" when altering pk referenced by ManyToMany relation

[Carsten Fuchs]

As outlined for Django 1.11.18 at ​https://groups.google.com/d/msg/django-users/3L5deYDtDMU/jRixojr7DgAJ, the problem can be reproduced with Django 2.2a1 in a fresh database.
Possibly related is #28305, which sounds very similar.

Using a model like this:

class Kostenstelle(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=60, blank=True)
    # ... omitted fields

    class Meta:
        db_table = 'kostenstelle'

I replaced the id line with

    id = models.IntegerField(primary_key=True, help_text="...")

Running manage.py makemigrations (of Django 1.11.1) created two migration files, numbers 0022 and 0023:

# Migration 0022 (import statements omitted).
class Migration(migrations.Migration):

    dependencies = [
        ('Lori', '0021_alter_Vortraege_jahr'),
    ]

    operations = [
        migrations.AlterField(
            model_name='kostenstelle',
            name='id',
            field=models.IntegerField(primary_key=True, serialize=False),
        ),
    ]

# Migration 0023 (import statements omitted).
class Migration(migrations.Migration):

    dependencies = [
        ('Lori', '0022_alter_Kostenstelle_id'),
    ]

    operations = [
        migrations.AlterField(
            model_name='kostenstelle',
            name='id',
            field=models.IntegerField(help_text='...', primary_key=True, serialize=False),
        ),
    ]

These used to work properly with the Oracle DB backend, but with Django 2.2a1 with MySQL backend, there are problems:

(TestDjango22a) carsten@black-steel-ubuntu:~/Zeiterfassung$ # starting with a fresh MySQL database
(TestDjango22a) carsten@black-steel-ubuntu:~/Zeiterfassung$ ./manage.py --version
2.2a1
(TestDjango22a) carsten@black-steel-ubuntu:~/Zeiterfassung$ ./manage.py migrate Lori 0021
Operations to perform:
  Target specific migration: 0021_alter_Vortraege_jahr, from Lori
Running migrations:
  Applying contenttypes.0001_initial… OK
  Applying auth.0001_initial… OK
  Applying Lori.0001_initial… OK
  Applying Lori.0002_alter_Ausbezahlt_monat… OK
  Applying Lori.0003_alter_Kalendereintrag_m2m… OK
  Applying Lori.0004_del_Erfasst_kann_gg… OK
  Applying Lori.0005_add_Mitarbeiter_email… OK
  Applying Lori.0006_create_UserBereichZuordnung… OK
  Applying Lori.0007_init_UserBereichZuordnung… OK
  Applying Lori.0008_del_Bereich_benutzer… OK
  Applying Lori.0009_add_Bereich_benutzer… OK
  Applying Lori.0010_create_UserKstZuordnung… OK
  Applying Lori.0011_init_UserKstZuordnung… OK
  Applying Lori.0012_del_Kostenstelle_benutzer… OK
  Applying Lori.0013_add_Kostenstelle_benutzer… OK
  Applying Lori.0014_refine_UserZuordnungen… OK
  Applying Lori.0015_add_UserBereichZuordnung_darf_urlantr… OK
  Applying Lori.0016_add_Mitarbeiter_anschrift… OK
  Applying Lori.0017_create_PekoSollStd… OK
  Applying Lori.0018_alter_Vortraege_jahr… OK
  Applying Lori.0019_alter_UserProfile_ma… OK
  Applying Lori.0020_del_PekoGewichte… OK
  Applying Lori.0021_alter_Vortraege_jahr… OK

(TestDjango22a) carsten@black-steel-ubuntu:~/Zeiterfassung$ ./manage.py sqlmigrate Lori 0022
BEGIN;
--
-- Alter field id on kostenstelle
--
ALTER TABLE `kostenstelle` DROP FOREIGN KEY `kostenstelle_parent_id_d0c73a18_fk_kostenstelle_id`;
ALTER TABLE `Lori_oeffnungszeiten` DROP FOREIGN KEY `Lori_oeffnungszeiten_kst_id_54e15381_fk_kostenstelle_id`;
ALTER TABLE `Lori_vertragsverlauf` DROP FOREIGN KEY `Lori_vertragsverlauf_kostenstelle_id_59f33815_fk_kostenstelle_id`;
ALTER TABLE `Lori_userkstzuordnung` DROP FOREIGN KEY `Lori_userkstzuordnun_kostenstelle_id_ac2cc3c0_fk_kostenste`;
ALTER TABLE `Lori_pekosollstd` DROP FOREIGN KEY `Lori_pekosollstd_kst_id_6b0156f7_fk_kostenstelle_id`;
ALTER TABLE `kostenstelle` MODIFY `id` integer NOT NULL;
ALTER TABLE `kostenstelle` MODIFY `parent_id` integer NULL;
ALTER TABLE `Lori_oeffnungszeiten` MODIFY `kst_id` integer NOT NULL;
ALTER TABLE `Lori_vertragsverlauf` MODIFY `kostenstelle_id` integer NULL;
ALTER TABLE `Lori_userkstzuordnung` MODIFY `kostenstelle_id` integer NOT NULL;
ALTER TABLE `Lori_pekosollstd` MODIFY `kst_id` integer NOT NULL;
ALTER TABLE `kostenstelle` ADD CONSTRAINT `kostenstelle_parent_id_d0c73a18_fk` FOREIGN KEY (`parent_id`) REFERENCES `kostenstelle` (`id`);
ALTER TABLE `Lori_oeffnungszeiten` ADD CONSTRAINT `Lori_oeffnungszeiten_kst_id_54e15381_fk` FOREIGN KEY (`kst_id`) REFERENCES `kostenstelle` (`id`);
ALTER TABLE `Lori_vertragsverlauf` ADD CONSTRAINT `Lori_vertragsverlauf_kostenstelle_id_59f33815_fk` FOREIGN KEY (`kostenstelle_id`) REFERENCES `kostenstelle` (`id`);
ALTER TABLE `Lori_userkstzuordnung` ADD CONSTRAINT `Lori_userkstzuordnung_kostenstelle_id_ac2cc3c0_fk` FOREIGN KEY (`kostenstelle_id`) REFERENCES `kostenstelle` (`id`);
ALTER TABLE `Lori_pekosollstd` ADD CONSTRAINT `Lori_pekosollstd_kst_id_6b0156f7_fk` FOREIGN KEY (`kst_id`) REFERENCES `kostenstelle` (`id`);
COMMIT;

(TestDjango22a) carsten@black-steel-ubuntu:~/Zeiterfassung$ ./manage.py migrate Lori 0022
Operations to perform:
  Target specific migration: 0022_alter_Kostenstelle_id, from Lori
Running migrations:
  Applying Lori.0022_alter_Kostenstelle_id…Traceback (most recent call last):
  File "/home/carsten/.virtualenvs/TestDjango22a/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/carsten/.virtualenvs/TestDjango22a/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/home/carsten/.virtualenvs/TestDjango22a/lib/python3.6/site-packages/MySQLdb/cursors.py", line 198, in execute
    res = self._query(query)
  File "/home/carsten/.virtualenvs/TestDjango22a/lib/python3.6/site-packages/MySQLdb/cursors.py", line 304, in _query
    db.query(q)
  File "/home/carsten/.virtualenvs/TestDjango22a/lib/python3.6/site-packages/MySQLdb/connections.py", line 217, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1833, "Cannot change column 'id': used in a foreign key constraint 'Lori_kalendereintrag_kostenstelle_id_edc2995b_fk_kostenste' of table 'LoriDB.Lori_kalendereintrag_kstellen'")

The above exception was the direct cause of the following exception:
# ...

I'm unsure how to proceed as I find it very difficult to come up with a testcase that can reproduce this.

[Carsten Fuchs]

I edited the console log in the description to make it more descriptive.

[Tim Graham]

I can't reproduce this using the information provided:

$ python manage.py makemigrations
Migrations for 't30152':
  t30152/migrations/0001_initial.py
    - Create model Kostenstelle

(changed field as from AutoField to IntegerField)

$ python manage.py makemigrations
Migrations for 't30152':
  t30152/migrations/0002_auto_20190201_2147.py
    - Alter field id on kostenstelle
$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, aggregation, auth, contenttypes, generic, polls, proxytest, sessions, t30152
Running migrations:
  Applying t30152.0001_initial… OK
  Applying t30152.0002_auto_20190201_2147… OK

You'll need to provide more information.

[Carsten Fuchs]

Test case model definitions

[Carsten Fuchs]

Hi Tim,
many thanks for your work!

I've been able to come up with a small example with which I can reproduce this.
Please see the three attached files models.py and the migrations 0001_initial.py and 0002_auto_20190201_2242.py.

(TestDjango22a) carsten@black-steel-ubuntu:~/ticket30152$ ./manage.py --version
2.2a1

(TestDjango22a) carsten@black-steel-ubuntu:~/ticket30152$ ./manage.py startapp Lori

# settings.py edited

(TestDjango22a) carsten@black-steel-ubuntu:~/ticket30152$ ./manage.py makemigrations
Migrations for 'Lori':
  Lori/migrations/0001_initial.py
    - Create model Kostenstelle
    - Create model KalenderEintrag

# changed `id` field definition

(TestDjango22a) carsten@black-steel-ubuntu:~/ticket30152$ ./manage.py makemigrations
Migrations for 'Lori':
  Lori/migrations/0002_auto_20190201_2242.py
    - Alter field id on kostenstelle

(TestDjango22a) carsten@black-steel-ubuntu:~/ticket30152$ ./manage.py migrate
Operations to perform:
  Apply all migrations: Lori, admin, auth, contenttypes, sessions
Running migrations:
  Applying Lori.0001_initial… OK
  Applying Lori.0002_auto_20190201_2242…Traceback (most recent call last):
  File "/home/carsten/.virtualenvs/TestDjango22a/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/carsten/.virtualenvs/TestDjango22a/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/home/carsten/.virtualenvs/TestDjango22a/lib/python3.6/site-packages/MySQLdb/cursors.py", line 198, in execute
    res = self._query(query)
  File "/home/carsten/.virtualenvs/TestDjango22a/lib/python3.6/site-packages/MySQLdb/cursors.py", line 304, in _query
    db.query(q)
  File "/home/carsten/.virtualenvs/TestDjango22a/lib/python3.6/site-packages/MySQLdb/connections.py", line 217, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1833, "Cannot change column 'id': used in a foreign key constraint 'Lori_kalendereintrag_kostenstelle_id_bcd99894_fk_kostenste' of table 'LoriDB.Lori_kalendereintrag_kst'")

The above exception was the direct cause of the following exception:
# ...

Can you please have a look if you can reproduce it with these files?

[Tim Graham]

Great! I think there are still some unnecessary fields on the models (DateField, etc) but my guess is the ForeignKey to self plus another model related to that one (ManyToManyField in this case) are the root causes.

[Carsten Fuchs]

Ah, slowly I get the hang of working with migrations in this way! :-)
Sorry about the extra fields. Also the meta db_table doesn't contribute. The minimum models.py file I've managed to find is:

class Kostenstelle(models.Model):
    # id     = models.AutoField(primary_key=True)
    id     = models.IntegerField(primary_key=True, help_text="...")
    parent = models.ForeignKey('self', models.PROTECT, null=True, blank=True)


class KalenderEintrag(models.Model):
    id  = models.AutoField(primary_key=True)
    kst = models.ManyToManyField(Kostenstelle, blank=True)

[Dart]

Project, which, if deployed locally, you can see the bug.

[Dart]

I deployed the project locally and checked for errors. Ultimately, the bug exists and I will fix it. Unfortunately, I didn’t have previous experience with commit into django, but I’ll do all my efforts to make my first commit!
Thanks

[Carsten Fuchs]

Hi Dart,
thanks for looking into this!

I too spent some time over the weeked trying to find out what's going on, but both my knowledge about the Django internals and about databases is little so that I did not get far.

I wonder why _alter_field() skips m2m fields at all? See
​https://github.com/django/django/blob/1b8a26efa2e45ac471c969791e8c977e6d633091/django/db/backends/base/schema.py#L573

[Carsten Fuchs]

Don't know why I didn't see this earlier, but I can reproduce this with even simpler model definitions: the FK to self is not needed, having a many2many field is enough:

# models.py
from django.db import models


class Kostenstelle(models.Model):
    #id = models.AutoField(primary_key=True)
    id = models.IntegerField(primary_key=True, help_text="...")


class KalenderEintrag(models.Model):
    id  = models.AutoField(primary_key=True)
    kst = models.ManyToManyField(Kostenstelle, blank=True)

[Matthijs Kooijman]

I ran into this issue myself this week and spent some time debugging it before I found this report. What I've found is that when changing the id field, MySQL/MariaDB complains if any constraints still reference the field. The ​`_alter_field()` method works around this by looking for reverse (incoming) ForeignKey relations (which will have an associated constraint), dropping the constraint before changing the id field and recreating the constraints afterwards. As Carsten has found, this works fine for normal ForeignKeys, but not for the ForeignKeys implicitly created by a ManyToMany relation.

Note that this problem does not occur when the ManyToMany relation uses an explicit through model, since then the relevant ForeignKey relations are explicit and will be treated just like all others.

I wonder why _alter_field() skips m2m fields at all?

I've wondered this as well, and tried modifying the code to process m2m fields (e.g. let ​`is_relevant_relation()` return True for m2m relations. However, further on, the code that has to actually process these "relevant relations" does not know how to handle m2m relation, so it throws some exception.

However, I realized that it is not actually the m2m relation that needs to be processed, but the underlying implicit ForeignKey (or the resulting ManyToOneRel, really). When looking for relevant relations, the code ​uses `_meta.related_objects`. When an explicit through model is involved, this will include the ManyToOneRels pointing to that model and everything works. However, with an implicit through model, these relations are not even returned by related_objects, so there's nothing we can do inside is_relevant_relation() to fix this.

Looking ​inside `related_objects` I see that it filters out hidden relations (except for m2m relations). Some debug printing shows that the relations that we need are in fact hidden. So replacing related_objects by _get_fields(forward=False, reverse=True, include_hidden=True) actually seems to fix this problem. This is of course an internal API which, I think, is not acceptable to use outside of the object itself, but I could not find any other public API to return exactly this (we could use get_fields(), but that has no way to only return reverse fields and not forward fields, so that needs additional filtering). Perhaps there is a better fix anyway?

I'm attaching a patch that makes the above change to fix the problem (as stated, this is not a proper fix yet), but also adds a testcase that exposes this problem. I've found the migrations.test_operations.OperationTests.test_alter_field_pk_fk test which essentially tests this case, but with an incoming ForeignKey field. I ended up copying this test twice, once with an incoming m2m field with an explicit through model (which works already) and one with an implicit through model (which breaks when used without the fixes in my patch). The patch was made against 2.2b1 rather than master, since I didn't have any easy access to python3.6, but I think 2.2 should be similar enough.

I also tried running all migration tests with this rough fix applied, but it seems that the testsuite already errors out on a clean checkout on my MySQL setup (sqlite seems to work). With the fix applied, the number of failing tests seems to stay the same, so that sounds promising (I haven't tried running the full test suite yet, though).

[Matthijs Kooijman]

Testcase showing the problem, plus a rough stab at a fix

[Mariusz Felisiak]

Partly fixed by #30591.

[Mariusz Felisiak]

I confirmed that this was fixed by 241deed2590bcb1d8c45271d44c86eaedfb57119.