MySQL 1071 and associated errors.
Our users have reported a few MySQL errors over the years but one of the most common appears to be the
ERROR 1071 (42000): Specified key was too long; max key length is xxx bytes
Where xxx is some specified value often, but not always, a value of 767.
For that reason we decided that we would make this blog entry to try and explain the situation.
We are concerned here mainly with InnoDB tables since that is what we tend to use in our extensions.
This MySQL page says that the limit is 767 bytes. We have seen situations where a limit of 1000 bytes has been specified and suspect that it is very version dependant. The MySQL page says the limit is 3072 bytes, which it states is for version 5.5. However we have seen other documentation that says that the InnoDB internal maximum key length is reported to be 3500 bytes, but MySQL itself restricts this to 3072 bytes. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15.) (Combined index key in a multi-column index). We have however had reports of the lower limits being reported on versions of MySQL later than 5.0.17!
The actual character limit depends very much upon the character set in use. MySQL assumes the worst possible case for the number of bytes per character in the string. For the MySQL 'utf8' encoding, that's 3 bytes per character, since that encoding doesn't allow characters beyond U+FFFF. For the MySQL 'utf8mb4' encoding, it's 4 bytes per character, since that's what MySQL calls actual UTF-8. We generally use UFT8 which means that the limit of 255 characters tends to apply.
One workaround for these limits is to only index a prefix of the longer columns:
One can limit the size by using a subset of the columns as in:
UNIQUE KEY `xxxxx_it_people_name_uk` (`person_name`(50),`person_email`(50))
This can obviously be tweaked by changing the sizes of the columns included to ensure that the size limit is not exceeded forthe system.
Another option might , if possible be to use the setting innodb_large_prefix, which was introduced in MySQL 5.5.14 (?) and allows one to include columns up to 3072 bytes long in InnoDB indexes. It does not affect the index limit, which is still 3072 bytes as quoted in the manual.
SET @@global.innodb_large_prefix = 1;
What we find unusual is that we have never personally encountered these limit problem and we have been running successfully for many years on MySQL 5.0 and 5.1. All recent reports of these types of error seem to be related to MySQL version 5.0.x, which since it is currently out of support, makes one very loathe to change our extensions to comply with an unsupported version of MySQL.