MySQL Forums
Forum List  »  InnoDB

InnoDB byte limit encountered with a column for emails
Posted by: Dean Richert
Date: November 28, 2014 06:24PM

I am trying to switch an email column set to VARCHAR(255) from the charset/collation of latin1_swedish_ci to utf8mb4_unicode_ci. The column also is set to unique and has a unique index associated with it. But I receive the error:

ERROR 1071: Specified key was too long; max key length is 767 bytes

I found this is caused by a limit in InnoDB.

Because utf8mb4 uses 4 bytes per character (in this calculation), setting any column to 255 will break the InnoDB 767 byte limit.

I appear to have several options, none of which are good.

Assumptions:
1) I can't lower the email varchar because I need to accept all email addresses. In fact, I want to be able to set it to 320, but am willing to settle on 255.
2) I can't make the email column non-unique because of app logic restraints.


-I can increase innodb's byte limit to 3072 by using "SET @@global.innodb_large_prefix = 1;" But I have no clear idea about what the ramifications for this might be and if it might slow down the entire database. If anyone can spell this out or point me toward a resource, this would help.

-I can set the column to another charset, like latin1_swedish_ci. This is my temporary solution, but this is not preferable because accented and foreign characters are now in email addresses (even gmail accepts accented and foreign characters in email addresses).

Questions:
-Do I have any other options?
-Is the InnoDB max key length for all columns combined or just for a single column?
-What problems might arise from using "SET @@global.innodb_large_prefix = 1;" to increase the InnoDB byte limit? I don't know why it is the default to not allow large prefixes, but I am assuming there was a good reason for it.

Options: ReplyQuote


Subject
Views
Written By
Posted
InnoDB byte limit encountered with a column for emails
2444
November 28, 2014 06:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.