[Django] #24410: MySQL manually created indexes aren't recognized
Django
-------------------------------+--------------------
Reporter: karolyi | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.7
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24410>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
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:
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
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by karolyi):
I believe this has to do partially with #24390, as I use python3 too.
--
Ticket URL: <https://code.djangoproject.com/ticket/24410#comment:2>
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.7
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by karolyi):
* component: Database layer (models, ORM) => Migrations
--
Ticket URL: <https://code.djangoproject.com/ticket/24410#comment:3>
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.7
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24410#comment:4>
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.7
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by karolyi):
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 error 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
--
Ticket URL: <https://code.djangoproject.com/ticket/24410#comment:5>
Django
Reporter: karolyi | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: 1.7
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: new => closed
* resolution: => duplicate
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24410#comment:6>