[Django] #24653: Migrations: Removing a field with foreign key constraint fails
Django
----------------------------+------------------------------------------
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Severity: Normal | Keywords: foreign key constraint mysql
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------+------------------------------------------
Hey,
I have a model that has a foreign key to another model. Let's say I change
my mind and want the relation the other way around (the another model
having a foreign key to my model), and so I modify the models accordingly,
removing the foreign key of my model.
This creates a migration with a `migrations.RemoveField` on the model,
which is just right. But when running this migration on MySQL, I get an
error saying
{{{
django.db.utils.OperationalError: (1553, "Cannot drop index
'base_comment_f6be1d8d': needed in a foreign key constraint")
}}}
and the migration fails.
Shouldn't the `migrations.RemoveField` drop that constraint automatically?
--
Ticket URL: <https://code.djangoproject.com/ticket/24653>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: foreign key | Triage Stage:
constraint mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by karolyi):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> Hey,
>
> I have a model that has a foreign key to another model. Let's say I
> change my mind and want the relation the other way around (the another
> model having a foreign key to my model), and so I modify the models
> accordingly, removing the foreign key of my model.
>
> This creates a migration with a `migrations.RemoveField` on the model,
> which is just right. But when running this migration on MySQL, I get an
> error saying
>
> {{{
> django.db.utils.OperationalError: (1553, "Cannot drop index
> 'base_comment_f6be1d8d': needed in a foreign key constraint")
> }}}
> and the migration fails.
>
> Shouldn't the `migrations.RemoveField` drop that constraint
> automatically?
New description:
Hey,
I have a model that has a foreign key to another model. Let's say I change
my mind and want the relation the other way around (the another model
having a foreign key to my model), and so I modify the models accordingly,
removing the foreign key of my model.
This creates a migration with a `migrations.RemoveField` on the model,
which is just right. But when running this migration on MySQL, I get an
error saying
{{{
django.db.utils.OperationalError: (1553, "Cannot drop index
'base_comment_f6be1d8d': needed in a foreign key constraint")
}}}
and the migration fails.
Shouldn't the `migrations.RemoveField` drop that constraint automatically?
This happens using python 3, might have to do something with #24390.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:1>
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: foreign key | Triage Stage:
constraint mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Description changed by karolyi:
Old description:
> Hey,
>
> I have a model that has a foreign key to another model. Let's say I
> change my mind and want the relation the other way around (the another
> model having a foreign key to my model), and so I modify the models
> accordingly, removing the foreign key of my model.
>
> This creates a migration with a `migrations.RemoveField` on the model,
> which is just right. But when running this migration on MySQL, I get an
> error saying
>
> {{{
> django.db.utils.OperationalError: (1553, "Cannot drop index
> 'base_comment_f6be1d8d': needed in a foreign key constraint")
> }}}
> and the migration fails.
>
> Shouldn't the `migrations.RemoveField` drop that constraint
> automatically?
>
> This happens using python 3, might have to do something with #24390.
New description:
Hey,
I have a model that has a foreign key to another model. Let's say I change
my mind and want the relation the other way around (the another model
having a foreign key to my model), and so I modify the models accordingly,
removing the foreign key of my model.
This creates a migration with a `migrations.RemoveField` on the model,
which is just right. But when running this migration on MySQL, I get an
error saying
{{{
django.db.utils.OperationalError: (1553, "Cannot drop index
'base_comment_f6be1d8d': needed in a foreign key constraint")
}}}
and the migration fails.
Shouldn't the `migrations.RemoveField` drop that constraint automatically?
This happens using python 3, might have to do something with #24390.
The only way to workaround this now is to create a
`migrations.RunPython(fk_remove)` statement before the
`migrations.RemoveField`, to remove the foreign key manually, by getting
its name from `INFORMATION_SCHEMA`:
{{{
def remove_fk(apps, schema_editor):
from django.db.backends.mysql.base import DatabaseWrapper
if isinstance(schema_editor.connection, DatabaseWrapper):
cursor = schema_editor.connection.cursor()
Edit = apps.get_model('base', 'Edit')
table_edit_name = Edit._meta.db_table
cursor.execute('SELECT DATABASE()')
db_name = cursor.fetchall()[0][0]
cursor.execute(
'SELECT CONSTRAINT_NAME FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE'
' `TABLE_SCHEMA`=\'%s\' AND `REFERENCED_TABLE_NAME`=\'%s\'' %
(
db_name,
table_edit_name
))
result = cursor.fetchall()
if not result:
return
Comment = apps.get_model('base', 'Comment')
table_comment_name = Comment._meta.db_table
fk_name = result[0][0]
query = 'ALTER TABLE `%s` DROP FOREIGN KEY `%s`' % (
table_comment_name,
fk_name
)
cursor.execute(query)
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:2>
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: foreign key | Triage Stage:
constraint mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Description changed by karolyi:
Old description:
> Hey,
>
> I have a model that has a foreign key to another model. Let's say I
> change my mind and want the relation the other way around (the another
> model having a foreign key to my model), and so I modify the models
> accordingly, removing the foreign key of my model.
>
> This creates a migration with a `migrations.RemoveField` on the model,
> which is just right. But when running this migration on MySQL, I get an
> error saying
>
> {{{
> django.db.utils.OperationalError: (1553, "Cannot drop index
> 'base_comment_f6be1d8d': needed in a foreign key constraint")
> }}}
> and the migration fails.
>
> Shouldn't the `migrations.RemoveField` drop that constraint
> automatically?
>
New description:
Hey,
I have a model that has a foreign key to another model. Let's say I change
my mind and want the relation the other way around (the another model
having a foreign key to my model), and so I modify the models accordingly,
removing the foreign key of my model.
This creates a migration with a `migrations.RemoveField` on the model,
which is just right. But when running this migration on MySQL, I get an
error saying
{{{
django.db.utils.OperationalError: (1553, "Cannot drop index
'base_comment_f6be1d8d': needed in a foreign key constraint")
}}}
and the migration fails.
Shouldn't the `migrations.RemoveField` drop that constraint automatically?
This happens using python 3, might have to do something with #24390.
The only way to workaround this now is to create a
`migrations.RunPython(remove_fk)` statement before the
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:3>
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: foreign key | Triage Stage:
constraint mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
Which MySQL version and storage engine are you using? I tried to reproduce
using the tutorial by removing the `question =
models.ForeignKey(Question)` field. Can you reproduce using that method?
Tested with MySQL 5.6.19 and both InnoDB and MyISAM storage engines;
Django master and stable/1.8.x branches.
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:4>
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: foreign key | Triage Stage:
constraint mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by karolyi):
Replying to [comment:4 timgraham]:
> Which MySQL version and storage engine are you using? I tried to
reproduce using the tutorial by removing the `question =
models.ForeignKey(Question)` field. Can you reproduce using that method?
Tested with MySQL 5.6.19 and both InnoDB and MyISAM storage engines;
Django master and stable/1.8.x branches.
I use MySQL 5.6.24, with Django 1.8, Python 3.4.3, and mysqlclient 1.3.6.
I managed to narrow down the problem to a specific mysql setting. As it
seems, when you use a mysql configuration in `settings.py` like
{{{
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'OPTIONS': {
'read_default_file': os.path.join(
BASE_DIR,
'mysql.conf'
)
}
}
}
}}}
and the content of mysql.conf being
{{{
[client]
database = django_test
user = root
# password = PASSWORD
default-character-set = utf8
}}}
, using mysql locally.
This way you'll be able to reproduce the problem w/ using your example
from the tutorial. In that case, you should get something like
{{{
django.db.utils.OperationalError: (1553, "Cannot drop index
'polls_choice_7aa0f6ee': needed in a foreign key constraint")
}}}
Generated migration files, as well as the error traceback, attached.
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:5>
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: foreign key | Triage Stage:
constraint mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by karolyi):
* Attachment "0001_initial.py" added.
initial migration
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: foreign key | Triage Stage:
constraint mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by karolyi):
* Attachment "0002_remove_choice_question.py" added.
Removing the question foreign key
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: foreign key | Triage Stage:
constraint mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by karolyi):
* Attachment "traceback.txt" added.
error traceback
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: foreign key | Triage Stage:
constraint mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by karolyi):
* Attachment "variables.txt" added.
Mysql variables
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: foreign key | Triage Stage:
constraint mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
Still no luck for me on reproducing. There's some
[https://github.com/django/django/blob/bd0883d5cc51ba0c4e106c9e20bfc94b296d6320/django/db/backends/base/schema.py#L437-L439
logic in the SchemaEditor] to remove indexes before the column is dropped.
What is the value if `fk_names` when you see the crash? In my case it's
something like
`['polls_choice_poll_id_776991a85051577d_fk_polls_poll_id']`. The index in
your error message is quite a bit shorter which seems suspicious.
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:6>
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: foreign key | Triage Stage:
constraint mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by karolyi):
Replying to [comment:6 timgraham]:
> What is the value if `fk_names` when you see the crash?
I just did a debug, it's an empty list (`[]`).
Did you setup the mysql client as I did, with the `read_default_file`
option?
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:7>
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: foreign key | Triage Stage:
constraint mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by karolyi):
I think I found the culprit. Just as I expected, using
`read_default_file`, for which you don't need to define the database name
in the settings, ends up in `self.connection.settings_dict['NAME']` being
an empty string in
[https://github.com/django/django/blob/bd0883d5cc51ba0c4e106c9e20bfc94b296d6320/django/db/backends/mysql/introspection.py#L162
db/backends/mysql/introspection.py:162]
In my case, as I shown in my example with manually looking up/removing the
foreign key, you'll need to use
{{{
cursor.execute('SELECT DATABASE()')
}}}
To fetch the DB name.
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:8>
Django
OPTIONS['read_default_file']
-------------------------------------+-------------------------------------
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: foreign key | Triage Stage: Accepted
constraint mysql |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* component: Migrations => Database layer (models, ORM)
* stage: Unreviewed => Accepted
Comment:
Oops, I forgot the remove the `DATABASES['NAME']` key when I tested with
`read_default_file`. I can indeed reproduce it now.
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:9>
Django
OPTIONS['read_default_file']
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: foreign key | Triage Stage: Accepted
constraint mysql |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by claudep):
What's the use case which prevents you from setting the
DATABASES['default']['NAME'] option?
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:10>
Django
OPTIONS['read_default_file']
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: foreign key | Triage Stage: Accepted
constraint mysql |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by karolyi):
Replying to [comment:10 claudep]:
> What's the use case which prevents you from setting the
`DATABASES['default']['NAME']` option?
I want the DB configuration to be stored in separate files, so my
opensource project with CI could have different environments to run for
tests/staging/beta/production.
The `read_default_file` option seemed just perfect for that.
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:11>
Django
OPTIONS['read_default_file']
-------------------------------------+-------------------------------------
Reporter: karolyi | Owner:
Type: Bug | Status: assigned
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: foreign key | Triage Stage: Accepted
constraint mysql |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by zshimanchik):
* status: new => assigned
* owner: nobody => zshimanchik
* version: 1.8 => 1.9
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:12>
Django
OPTIONS['read_default_file']
-------------------------------------+-------------------------------------
Reporter: karolyi | Owner:
| zshimanchik
Type: Bug | Status: assigned
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: foreign key | Triage Stage: Accepted
constraint mysql |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/6200 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:13>
Django
OPTIONS['read_default_file']
-------------------------------------+-------------------------------------
Reporter: karolyi | Owner:
| zshimanchik
Type: Bug | Status: assigned
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
constraint mysql | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by claudep):
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:14>
Django
OPTIONS['read_default_file']
-------------------------------------+-------------------------------------
Reporter: karolyi | Owner:
| zshimanchik
Component: Database layer | Version: 1.9
(models, ORM) |
Keywords: foreign key | Triage Stage: Ready for
constraint mysql | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"65aa94200b3d10d515b15a78898c3692cd57e6c7" 65aa9420]:
{{{
#!CommitTicketReference repository=""
revision="65aa94200b3d10d515b15a78898c3692cd57e6c7"
Fixed #24653 -- Fixed MySQL database introspection when using
read_default_file.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24653#comment:15>