Opened 10 years ago
Closed 9 years ago
#24653 closed Bug (fixed)
Database introspection doesn't work when using MySQL OPTIONS['read_default_file']
Reported by: | László Károlyi | Owned by: | Zahar Shimanchik |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.9 |
Severity: | Normal | Keywords: | foreign key constraint mysql |
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
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 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)
Attachments (4)
Change History (19)
comment:1 by , 10 years ago
Description: | modified (diff) |
---|
comment:2 by , 10 years ago
Description: | modified (diff) |
---|
comment:3 by , 10 years ago
Description: | modified (diff) |
---|
follow-up: 5 comment:4 by , 10 years ago
comment:5 by , 10 years ago
Replying to 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.
by , 10 years ago
Attachment: | 0002_remove_choice_question.py added |
---|
Removing the question foreign key
follow-up: 7 comment:6 by , 10 years ago
Still no luck for me on reproducing. There's some 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.
comment:7 by , 10 years ago
Replying to 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?
comment:8 by , 10 years ago
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 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.
comment:9 by , 10 years ago
Component: | Migrations → Database layer (models, ORM) |
---|---|
Summary: | Migrations: Removing a field with foreign key constraint fails → Database introspection doesn't work when using MySQL OPTIONS['read_default_file'] |
Triage Stage: | Unreviewed → Accepted |
Oops, I forgot the remove the DATABASES['NAME']
key when I tested with read_default_file
. I can indeed reproduce it now.
follow-up: 11 comment:10 by , 10 years ago
What's the use case which prevents you from setting the DATABASES['default']['NAME']
option?
comment:11 by , 10 years ago
Replying to 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.
comment:12 by , 9 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
Version: | 1.8 → 1.9 |
comment:14 by , 9 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
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.