Re: [Django] #30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" when altering pk of model with ForeignKey to self

[Django]

#30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" 
when
altering pk of model with ForeignKey to self
---+
 Reporter:  Carsten Fuchs  |Owner:  Dart
 Type:  Bug|   Status:  assigned
Component:  Migrations |  Version:  2.2
 Severity:  Normal |   Resolution:
 Keywords:  mysql  | Triage Stage:  Accepted
Has patch:  0  |  Needs documentation:  0
  Needs tests:  0  |  Patch needs improvement:  0
Easy pickings:  0  |UI/UX:  0
---+
Changes (by Matthijs Kooijman):

 * Attachment "testcase_and_rough_fix.patch" added.

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

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.663647aa74327e03e85bb0207ab48aea%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" when altering pk of model with ForeignKey to self

[Django]

#30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" 
when
altering pk of model with ForeignKey to self
---+
 Reporter:  Carsten Fuchs  |Owner:  Dart
 Type:  Bug|   Status:  assigned
Component:  Migrations |  Version:  2.2
 Severity:  Normal |   Resolution:
 Keywords:  mysql  | Triage Stage:  Accepted
Has patch:  0  |  Needs documentation:  0
  Needs tests:  0  |  Patch needs improvement:  0
Easy pickings:  0  |UI/UX:  0
---+

Comment (by 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
 
[[https://github.com/django/django/blob/1b8a26efa2e45ac471c969791e8c977e6d633091/django/db/backends/base/schema.py#L533|`_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
 
[[https://github.com/django/django/blob/1b8a26efa2e45ac471c969791e8c977e6d633091/django/db/backends/base/schema.py#L14|`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
 
[[https://github.com/django/django/blob/1b8a26efa2e45ac471c969791e8c977e6d633091/django/db/backends/base/schema.py#L30-L36|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
 
[[https://github.com/django/django/blob/1b8a26efa2e45ac471c969791e8c977e6d633091/django/db/models/options.py#L550-L564|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).

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are 

Re: [Django] #30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" when altering pk of model with ForeignKey to self

[Django]

#30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" 
when
altering pk of model with ForeignKey to self
---+
 Reporter:  Carsten Fuchs  |Owner:  Dart
 Type:  Bug|   Status:  assigned
Component:  Migrations |  Version:  2.2
 Severity:  Normal |   Resolution:
 Keywords:  mysql  | Triage Stage:  Accepted
Has patch:  0  |  Needs documentation:  0
  Needs tests:  0  |  Patch needs improvement:  0
Easy pickings:  0  |UI/UX:  0
---+

Comment (by 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:

 {{{
 #!python
 # 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)
 }}}

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.d4c0cfd2c0f69a79cb2180531ac58127%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" when altering pk of model with ForeignKey to self

[Django]

#30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" 
when
altering pk of model with ForeignKey to self
---+
 Reporter:  Carsten Fuchs  |Owner:  Dart
 Type:  Bug|   Status:  assigned
Component:  Migrations |  Version:  2.2
 Severity:  Normal |   Resolution:
 Keywords:  mysql  | Triage Stage:  Accepted
Has patch:  0  |  Needs documentation:  0
  Needs tests:  0  |  Patch needs improvement:  0
Easy pickings:  0  |UI/UX:  0
---+

Comment (by 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

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.dd49f1a1f55201815b387cb3a94a339c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" when altering pk of model with ForeignKey to self

[Django]

#30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" 
when
altering pk of model with ForeignKey to self
---+
 Reporter:  Carsten Fuchs  |Owner:  Dart
 Type:  Bug|   Status:  assigned
Component:  Migrations |  Version:  2.2
 Severity:  Normal |   Resolution:
 Keywords:  mysql  | Triage Stage:  Accepted
Has patch:  0  |  Needs documentation:  0
  Needs tests:  0  |  Patch needs improvement:  0
Easy pickings:  0  |UI/UX:  0
---+
Changes (by Dart):

 * owner:  nobody => Dart
 * status:  new => assigned


Comment:

 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

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.93bcf1f0e32264f88296b010c81a7815%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" when altering pk of model with ForeignKey to self

[Django]

#30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" 
when
altering pk of model with ForeignKey to self
---+
 Reporter:  Carsten Fuchs  |Owner:  nobody
 Type:  Bug|   Status:  new
Component:  Migrations |  Version:  2.2
 Severity:  Normal |   Resolution:
 Keywords:  mysql  | Triage Stage:  Accepted
Has patch:  0  |  Needs documentation:  0
  Needs tests:  0  |  Patch needs improvement:  0
Easy pickings:  0  |UI/UX:  0
---+
Changes (by Dart):

 * Attachment "mysite.zip" added.

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

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.adda6a4badda4fa6c00933c4a1a98812%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" when altering pk of model with ForeignKey to self

[Django]

#30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" 
when
altering pk of model with ForeignKey to self
---+
 Reporter:  Carsten Fuchs  |Owner:  nobody
 Type:  Bug|   Status:  new
Component:  Migrations |  Version:  2.2
 Severity:  Normal |   Resolution:
 Keywords:  mysql  | Triage Stage:  Accepted
Has patch:  0  |  Needs documentation:  0
  Needs tests:  0  |  Patch needs improvement:  0
Easy pickings:  0  |UI/UX:  0
---+
Changes (by Tim Graham):

 * status:  closed => new
 * resolution:  needsinfo =>


-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.84abe7c4fd870295cebbac41082db2a5%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" when altering pk of model with ForeignKey to self

[Django]

#30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" 
when
altering pk of model with ForeignKey to self
---+-
 Reporter:  Carsten Fuchs  |Owner:  nobody
 Type:  Bug|   Status:  closed
Component:  Migrations |  Version:  2.2
 Severity:  Normal |   Resolution:  needsinfo
 Keywords:  mysql  | Triage Stage:  Accepted
Has patch:  0  |  Needs documentation:  0
  Needs tests:  0  |  Patch needs improvement:  0
Easy pickings:  0  |UI/UX:  0
---+-

Comment (by 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:

 {{{
 #!python
 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)
 }}}

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.0ef3bfeeccb2cba40829fa379acaf42d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" when altering pk of model with ForeignKey to self (was: MySQL: Cannot change column 'id': used in a foreign ke

[Django]

#30152: MySQL: "Cannot change column 'id': used in a foreign key constraint" 
when
altering pk of model with ForeignKey to self
---+-
 Reporter:  Carsten Fuchs  |Owner:  nobody
 Type:  Bug|   Status:  closed
Component:  Migrations |  Version:  2.2
 Severity:  Normal |   Resolution:  needsinfo
 Keywords:  mysql  | Triage Stage:  Accepted
Has patch:  0  |  Needs documentation:  0
  Needs tests:  0  |  Patch needs improvement:  0
Easy pickings:  0  |UI/UX:  0
---+-
Changes (by Tim Graham):

 * stage:  Unreviewed => Accepted


Comment:

 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.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.2a4c799b2340c1d9846d1c53b73a8058%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.