MySQL: Cannot change column 'id': used in a foreign key constraint

[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.