#30152: MySQL: Cannot change column 'id': used in a foreign key constraint
-------------------------------------+-------------------------------------
Reporter: Carsten | Owner: nobody
Fuchs |
Type: | Status: new
Uncategorized |
Component: Database | Version: 2.2
layer (models, ORM) |
Severity: Normal | Keywords: mysql
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/30152>
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/051.1f56a4aa6ba34f809d620982e289bfda%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
