Opened 10 years ago

Closed 10 years ago

#24410 closed Bug (duplicate)

MySQL manually created indexes aren't recognized

Reported by: László Károlyi Owned by: nobody
Component: Migrations Version: 1.7
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no 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,

Using Django 1.7's migrations with MySQL, when I need to create a custom index on a column with a big length (_mysql_exceptions.Warning: Specified key was too long; max key length is 767 bytes, thus the column needs a prefix length), it's not recognized, AND not deleted when migrating backwards. The erroneous index creation SQL is recreated in the next migration, causing the migration to fail, and creating another index which emits a MySQL warning (_mysql_exceptions.Warning: Duplicate index 'cdn_image_cdn_path_2654d3de68657258_uniq' defined on the table 'xxx.cdn_image'. This is deprecated and will be disallowed in a future release.), thus causing the migration to fail.

Here's the model:

class Image(models.Model):

    """Model for the saved images"""

    comment = models.ManyToManyField(
        'base.Comment', null=None, default=1, verbose_name=_('Found in comment'))
    orig_src = models.URLField(
        verbose_name=_('Original source'), max_length=512, db_index=True)
    mime_type = models.CharField(verbose_name=_('Mime type'), max_length=100)
    cdn_path = models.CharField(
        verbose_name=_('Path in CDN'), max_length=255, unique=True)
    file_hash = models.CharField(
        verbose_name=_('File hash'), max_length=200, unique=True)

    def __str__(self):
        return self.cdn_path

Try creating migrations with changing the db_index property on the orig_src field, and you'll see it happen.

What I did to create my custom index is to add a special migration to my migration file:

        migrations.RunSQL(
            'CREATE INDEX `cdn_image_orig_src_uniq` ON `cdn_image` (`orig_src`(255))',
            reverse_sql='DROP INDEX `cdn_image_orig_src_uniq` ON `cdn_image`'),

Nothing helps here. Even if the index is created manually, the next migration will try to create an erroneous statement for MySQL, and the migration fail.

The other interesting phenomenon is, when migrating backwards, other indexes which are created successfully in former steps (like the one on cdn_path), are not removed. On the next forward migration, the above mentioned warning occurs, and the migration fails.

This blocks me from proceeding with my development. Please fix this bug ASAP.

Change History (6)

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

Component: UncategorizedDatabase layer (models, ORM)
Description: modified (diff)
Type: UncategorizedBug

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

I believe this has to do partially with #24390, as I use python3 too.

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

Component: Database layer (models, ORM)Migrations

comment:4 by Tim Graham, 10 years ago

I'm having trouble understanding the steps to reproduce the issue. I had no trouble migrating this model:

class Image(models.Model):
    orig_src = models.URLField(verbose_name='Original source', max_length=512, db_index=True)

Python 3.4, MySQL 5.6.19, and mysqlclient 1.3.4.

comment:5 by László Károlyi, 10 years ago

Python 3.4.2, MySQL 5.6.22 Homebrew, mysqlclient==1.3.5 here.

To clarify the workflow to reproduce this bug:

  • MySQL will emit the warning mentioned above. In order to have that warning with the traceback and the failing migration avoided, I need to create the index manually with a migrations.RunSQL statement (stated above too).
  • When I try to use the same index name Django created so it'll be recognized by migrations, the hash() related bug comes into picture, causing the hash names to be unpredictable each time.

I provided a pull request for this to be fixed, which uses about the same md5 hashing as south did before:

https://github.com/django/django/pull/4209

Server and client variables:

character_set_client = utf8
character_set_connection = utf8
character_set_database = utf8
character_set_results = utf8
character_set_server = utf8
character_set_system = utf8
collation_connection = utf8_general_ci
collation_database = utf8_general_ci
collation_server = utf8_general_ci
Last edited 10 years ago by László Károlyi (previous) (diff)

comment:6 by Tim Graham, 10 years ago

Resolution: duplicate
Status: newclosed

It seems that the MySQL warning about the index name being too long is tracked in #18392. Note that MySQL warnings are no longer promoted to errors in DEBUG mode as of #23871 (in 1.8); however, you could work around this on 1.7 by running your migrations with DEBUG=False. I think all the issues presented in this ticket are duplicated of other tickets.

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