2015년 9월 21일 월요일

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

What Problems? 


When you create index in Innodb table, you may see this error statement.
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
When you see this error statement, You can not create index in InnoDB table.


What for?


This is InnoDB restriction.
http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html
You can find InnoDB Index Restrictions.

What can I do for fixing problems?


1. Decrease index key size.
max key lenght is 767 bytes. So, euckr is 383 character, utf8 is 255 character, utf8mb4 is 191 character. ( 1 character = 2 byte in euckr =3 byte in utf8 =4byte in utf8mb4 )

2. Changing InnoDB Fileformat, enabling innodb_large_prefix.
When you use Barracuda file format, You can create index for 3072 byte.

You can check system variables.
innodb_file_format_max=Barracuda
innodb_file_format=Barracuda

And, When you create table, you must set row format is dynamic or compressed.

In detail,  You can check this url.
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix


댓글 없음:

댓글 쓰기