Home mysql 1071 Specified key was too long; max key length is 767 bytes

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

Author

Date

Category

This error occurs on the local server when trying to change the encoding and collation of the table to utf8mb4 , but not on the remote server. I know the reason for the error, I am wondering why the behavior is different. Local mysql – 5.5.45, remote – 5.5.55.


Answer 1, authority 100%

Judging by answers to similar the question in the English version of SO in the INNODB engine (up to MySQL 5.5.14) there are the following restrictions on the length of a field with a unique key:

  • INNODB utf8 VARCHAR (255)
  • INNODB utf8mb4 VARCHAR (191)

compare sysvar_innodb_large_prefix parameter in both databases

Enable this option to allow index key prefixes longer than 767 bytes
(up to 3072 bytes), for InnoDB tables that use DYNAMIC or COMPRESSED
row format. (Creating such tables also requires the option values
innodb_file_format = barracuda and innodb_file_per_table = true.) See
Section 14.11.8, “Limits on InnoDB Tables” for maximums associated
with index key prefixes under various settings.

For tables that use REDUNDANT or COMPACT row format, this option does
not affect the permitted index key prefix length. When this setting is
enabled, attempting to create an index prefix with a key length
greater than 3072 for a REDUNDANT or COMPACT table causes an
ER_INDEX_COLUMN_TOO_LONG error.

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions