MySQL Operational Error 1071 when running paster setup-db production.ini

Issue #96 resolved
Brandon Jones created an issue

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?

Comments (5)

  1. Mads Kiilerich

    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?

  2. Brandon Jones reporter

    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.

  3. Log in to comment