#24410: MySQL manually created indexes aren't recognized
-------------------------------------+-------------------------------------
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| 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
* component: Uncategorized => Database layer (models, ORM)
* needs_tests: => 0
* needs_docs: => 0
* type: Uncategorized => Bug
Old description:
> Hey,
>
> Using Django 1.7's migrations with MySQL, when I need to create a custom
> index on a column (`Specified key was too long; max key length is 767
> bytes`, thus the column needs a prefix length), it's not recognized, not
> deleted when migrating backwards and the erroneous index creation SQL is
> recreated in the next migration, causing the migration to fail, or
> 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
> }}}
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/24410#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.7f2d805b9df09da26100707944a959d2%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
