Database introspection doesn't work when using MySQL OPTIONS['read_default_file']

[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)

[Tim Graham]

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.

[László Károlyi]

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.

[László Károlyi]

initial migration

[László Károlyi]

Removing the question foreign key

[László Károlyi]

error traceback

[László Károlyi]

Mysql variables

[Tim Graham]

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.

[László Károlyi]

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?

[László Károlyi]

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.

[Tim Graham]

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

[Claude Paroz]

What's the use case which prevents you from setting the DATABASESdefaultNAME option?

[László Károlyi]

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.

[Tim Graham]

​PR

[Tim Graham <timograham@…>]

In 65aa9420:

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