[Solved] Specified key was too long; max key length is 767 bytes

Encountered the above-mentioned bug in production today:

       When the system variable innodb_large_prefix is ​​enabled, for InnoDB tables that use DYNAMIC or COMPRESSED row format, the index key prefix is ​​limited to 3072 bytes. If innodb_large_prefix is ​​disabled, no matter what table it is, the index key prefix is ​​limited to 767 bytes.

       The above bug is obviously that the index exceeds the limited length of 767 (innodb_large_prefix is ​​disabled in our production):

       I found that the table where the error was reported has established a varchar type index, varchar(255). I think there is no problem. In fact, it is not. The above 767 is a byte, and the varchar type is a character. At the same time, I found that the character set I used is ( utf8mb4), this means that the maximum number of bytes per character is 4, so it is obvious that 4*255> 767

So the above error was reported (Specified key was too long; max key length is 767 bytes).

       Solution:

     Change the number of characters in varchar, I changed it to 64. varchar(64)

 Or enable innodb_large_prefix, then the limit value will increase to 3072

Read More:

Leave a Reply

Your email address will not be published. Required fields are marked *