#30152: MySQL: Cannot change column 'id': used in a foreign key constraint
-------------------------------------+-------------------------------------
Reporter: Carsten Fuchs | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> 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:
> {{{
> #!python
> 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
>
> {{{
> #!python
> 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:
> {{{
> #!python
> # 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),
> ),
> ]
> }}}
>
> {{{
> #!python
> # 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:
>
> {{{
> #!sh
> (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
> Operations to perform:
> Apply all migrations: Lori, admin, auth, contenttypes, sessions
> 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
> 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:
> # ...
>
> (TestDjango22a) carsten@black-steel-ubuntu:~/Zeiterfassung$ ./manage.py
> sqlmigrate Lori 0022
> BEGIN;
> --
> -- Alter field id on kostenstelle
> --
> # I did not cut anything here: `manage.py sqlmigrate` seems to check
> which DROP FOREIGN KEY statements must still be emitted?
> # If I repeat `manage.py migrate` and `manage.py sqlmigrate`, the two
> lines with DROP FOREIGN KEY disappear as well.
> 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;
> }}}
> I'm unsure how to proceed as I find it very difficult to come up with a
> testcase that can reproduce this.
New description:
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:
{{{
#!python
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
{{{
#!python
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:
{{{
#!python
# 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),
),
]
}}}
{{{
#!python
# 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:
{{{
#!sh
(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.
--
Comment (by Carsten Fuchs):
I edited the console log in the description to make it more descriptive.
--
Ticket URL: <https://code.djangoproject.com/ticket/30152#comment:1>
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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/066.027283bc65ec9f0c97c3c2e7c2d1693d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
