Failed to install index for silk.Request model: (1071, 'Specified key was too long; max key length is 767 bytes')

[GitFree]

I get this warning when run "python manage.py syncdb".

[mtford90]

Interesting, what database are you using?

[GitFree]

mysql5.6
2014年9月19日 上午4:02于 "Michael Ford" notifications@github.com写道：

Interesting, what database are you using?

—
Reply to this email directly or view it on GitHub
#38 (comment).

[utkuyaman]

http://dev.mysql.com/doc/refman/5.6/en/create-index.html says:

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix limit is 1000 bytes.

this means the length of the varchar column is too long to apply index on so we must decrease the size of the column or size of the index to 255.
but as far as i know indexing the first xxx characters of a column is not supported in django.

I tried changing the table definition to:

class Request(models.Model):
    path = CharField(max_length=255, db_index=True)
    ...
    view_name = CharField(max_length=255, db_index=True, blank=True, default='')

and it worked.

but i don't know if changing the length of these 2 fields is good or bad for the system to work.
I need a comment for this from a commiter.

[mtford90]

@utkuyaman, the only issue I see with that is if a path is longer than 255 characters it's going to break when we try to save down to the db. Not sure how rare that would be.

I totally support making this change though, but I think if you do make a pull request for this you should add code that truncates any paths that are >255

[prismic]

Is there any update on this? I can take a stab at making a pull request to truncate the path if that's the way to go.

[utkuyaman]

I changed the table definition as i described in my reply. and it worked for me.
Unfortunately I couldn't find time to test it completely and commit the change.
You can try to change the codes and reply here if it works, then i can commit a patch.

[prismic]

I just tried changing the length of the two fields and it did indeed work for me.

[mtford90]

@prismic @utkuyaman I def think truncating is the way to go. It would be nice if there was a way to make the max_length configurable however - if the database supports >255 e.g. postgres, it does seem a shame to not support that. I suspect it would be a bad idea linking the settings with model fields though...

[prismic]

If changing maxlength is not desirable, what about the ability to turn off indexing on those fields via settings? I imagine running on a dev box indexing would not be a huge performance issue, although in production it would be. Would a warning message in the docs about turning off indexing suffice?

[avelis]

@mtford90 To create a pull request that simply truncates a Request.path that is larger than 255 characters can be done in one of two ways:

1. Override the path model fields setter using a properties technique.
2. Augment the save method to truncate before persistence to the DB.

Both have pro's and cons. Whichever you lean on I can probably develop, write a test and make a pull request. Both solutions put the development at the model level. This simplifies the responsibility to one location that is very visible to discover and understand.

Thoughts?

[smaccona]

@utkuyaman @prismic @mtford90 hard-coding the max_length to 255 characters, even if you also safely truncate it to 255 characters via a model property, a pre-save signal or a similar technique won't cover all eventualities, because the character encoding of the underlying table/column is also a factor.

By default, MySQL uses a character encoding it calls utf8, however it's not actually utf8 at all but a non-standard 3-byte encoding. To really support UTF8 in MySQL, you need a 4-byte encoding, and the one MySQL provides is called utf8mb4. This is what you need to do if you want to fully support internationalization, so many people will be using utf8mb4.

The upshot of this is that the maximum index length is actually 191 characters (767/4) instead of 255 characters (767/3). For more details on how the Django team already ran into this issue, see https://code.djangoproject.com/ticket/18392 (in particular the comment at https://code.djangoproject.com/ticket/18392#comment:16 is enlightening). They haven't fully resolved this yet.

Also, to be clear: it only makes sense to both change max_length and implement truncate-before-save, right? One allows you to create the index, the second prevents an error on attempted save of paths that are too long.

What was the rationale for the original max_length=300? I don't believe Django itself places a limit on Request.path length - it's just a string.

[avelis]

@smaccona I believe the attempt was to ensure the ability to capture long URL's that django-silk could be stored. Further up in the thread you will read that indexing a varchar column longer than 255 is not possible in MySQL but it is PostgreSQL. That difference is at the heart of this issue.

[smaccona]

@avelis what I meant was that the current Request model's name and view fields had their max_length originally set to 300, regardless of database (before this indexing issue was raised). I was just wondering why the current value of 300 was picked as the max_length in the first place.

Also, indexing a varchar column longer than 255 characters is possible in MySQL by setting the server option innodb_large_prefix to be true, setting innodb_file_format to be "Barracuda" and using a row format of ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED. You can do this without restarting MySQL by using set global commands from the MySQL client, and also place the relevant lines in your equivalent of the my.cnf file so they survive a server restart. See https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix for details and http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/ for a walkthrough.

The point of my post above was that even reducing the max_length to 255 for those two fields won't help when your MySQL setup is using a Unicode character set such as utf8mb4 (that's the only one that works for real internationalization support). If you're using utf8mb4, then a varchar(255) column takes up 255 * (4 bytes per character) = 1020 bytes, which is still greater than the default 767-byte index maximum index length in MySQL, so the error will still occur when python manage.py syncdb tries to create the index.