Issue #96: MySQL Operational Error 1071 when running paster setup-db production.ini
| Reported by: | Brandon Jones |
| State: | resolved |
| Created on: | 2015-02-05 15:04 |
| Updated on: | 2015-02-18 23:19 |
Description
I am receiving the following error when trying to run the database setup for Kallithea:
(Operational Error) (1071, 'Specified key was too long: max key length is 767 bytes') 'CREATE INDEX notification_type_idx ON notifications (type)' ()
Looking at the schema, it would appear that the type field of the notifications table is a VARCHAR(256) - which, with UTF-8, puts that at 768 bytes with 3 bytes per character - 1 byte over the Inno-DB limitation.
Is it assumed that innodb_large_prefix is true?
Attachments
Comments
Comment by Mads Kiilerich, on 2015-02-05 20:39
I don't know. I use PostgreSQL (and that is what I would recommend if anybody asked me but I am no PostgreSQL expert).
It seems like you are an MySQL expert. What solution would you recommend?
But you mention UTF-8, so VARCHAR(256) means 256 unicode codepoints, stored using utf-8 internally? That would be weird. I would expect either a max count of 2 byte UCS2 characters or a max size of the utf-8 representation.
However, in "all" places where mankind has seen "size must fit in a byte" limitations, it has been pascal style with 0-255 characters. To the extent it makes sense to have a max size, it would make sense to change all the 256 to 255. Do you agree? Does that work for you?
Comment by Brandon Jones, on 2015-02-06 20:08
I wouldn't argue that I'm a MySQL expert, but my proposed solution would be to just lower all the VARCHAR(256) fields to VARCHAR(255). That way no changes to the database engine or MySQL environment need to be made.
Comment by Mads Kiilerich, on 2015-02-06 20:12
Can you give some reference for that - something we can use to justify it.
Comment by Brandon Jones, on 2015-02-06 20:15
This goes into a bit more explanation behind the issue. I can't speak to whether all 256 characters are actually being used in Rhodecode and if lowering it to 255 would have any adverse effects.
http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/
Comment by Mads Kiilerich, on 2015-02-18 23:19
fixed, thanks for the hints