Opened 9 years ago

Closed 8 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 László Károlyi)

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)

0001_initial.py (1.1 KB ) - added by László Károlyi 9 years ago.
initial migration
0002_remove_choice_question.py (341 bytes ) - added by László Károlyi 9 years ago.
Removing the question foreign key
traceback.txt (6.9 KB ) - added by László Károlyi 9 years ago.
error traceback
variables.txt (12.5 KB ) - added by László Károlyi 9 years ago.
Mysql variables

Download all attachments as: .zip

Change History (19)

comment:1 by László Károlyi, 9 years ago

Description: modified (diff)

comment:2 by László Károlyi, 9 years ago

Description: modified (diff)

comment:3 by László Károlyi, 9 years ago

Description: modified (diff)

comment:4 by Tim Graham, 9 years ago

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.

in reply to:  4 comment:5 by László Károlyi, 9 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 InnoDB with MySQL 5.6.24, 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.

Last edited 9 years ago by László Károlyi (previous) (diff)

by László Károlyi, 9 years ago

Attachment: 0001_initial.py added

initial migration

by László Károlyi, 9 years ago

Removing the question foreign key

by László Károlyi, 9 years ago

Attachment: traceback.txt added

error traceback

by László Károlyi, 9 years ago

Attachment: variables.txt added

Mysql variables

comment:6 by Tim Graham, 9 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.

in reply to:  6 comment:7 by László Károlyi, 9 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 László Károlyi, 9 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 Tim Graham, 9 years ago

Component: MigrationsDatabase layer (models, ORM)
Summary: Migrations: Removing a field with foreign key constraint failsDatabase introspection doesn't work when using MySQL OPTIONS['read_default_file']
Triage Stage: UnreviewedAccepted

Oops, I forgot the remove the DATABASES['NAME'] key when I tested with read_default_file. I can indeed reproduce it now.

comment:10 by Claude Paroz, 9 years ago

What's the use case which prevents you from setting the DATABASES['default']['NAME'] option?

Last edited 9 years ago by Claude Paroz (previous) (diff)

in reply to:  10 comment:11 by László Károlyi, 9 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 Zahar Shimanchik, 8 years ago

Owner: changed from nobody to Zahar Shimanchik
Status: newassigned
Version: 1.81.9

comment:13 by Tim Graham, 8 years ago

Has patch: set

comment:14 by Claude Paroz, 8 years ago

Triage Stage: AcceptedReady for checkin

comment:15 by Tim Graham <timograham@…>, 8 years ago

Resolution: fixed
Status: assignedclosed

In 65aa9420:

Fixed #24653 -- Fixed MySQL database introspection when using read_default_file.

Note: See TracTickets for help on using tickets.
Back to Top