48

I know there have been plenty of questions about this, but I think my math is right.

  • MySQL reserves 3 bytes per UTF8 character.
  • MyISAM allows keys of length 1000 bytes.
  • My UTF8 VARCHAR(255) should be 255 * 3 = 765 bytes

Unless UNQUE requires an extra 200+ bytes per entry, why doesn't this work?

mysql> ALTER TABLE entry ADD UNIQUE INDEX (name(255));
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

Is there anything I can do about this?

EDIT:

It turns out the limit is 250. It seems chars count as 4 bytes for unique indices, but I don't know why.

EDIT 2:

Thanks Vladislav Vaintroub, the charset is indeed utf8mb4. That solves the mystery. I hadn't seen any documentation on this change.

I'm guessing it builds the non unique index by implicitly truncating the field, which is unacceptable for unique indices so it refuses.

If you re-enter your comment as an answer I'd be happy to accept it.

Solution: Specify utf8, not utf8mb4 (MySQL Admin doesn't allow this, so create the table manually)

Mathias Bynens
  • 144,855
  • 52
  • 216
  • 248
David Kanarek
  • 12,611
  • 5
  • 45
  • 62
  • read this too : http://www.xaprb.com/blog/2006/04/17/max-key-length-in-mysql/ – xkeshav May 30 '11 at 06:20
  • The first one says you aren't allowed to store 4 byte chars (I'm not). The second one says VARCHAR(333) should be fine, and I'm trying to use VARCHAR(255). – David Kanarek May 30 '11 at 06:28
  • 3
    Can you run "show create table entry"? Would be interesting to see the charset. New mysql (5.5+) does have 4 byte UTF8 named utf8mb4, at some point prior to 5.5 release I think it was also named "utf8", but then was renamed "utf8mb4" to avoid compatibility problems. – Vladislav Vaintroub May 30 '11 at 23:21
  • 1
    Relative questions are [Specified key was too long; max key length is 767 bytes](http://stackoverflow.com/questions/3489041) and [Specified key was too long; max key length is 1000 bytes](http://stackoverflow.com/questions/1814532). – dma_k Nov 10 '11 at 09:24
  • Possible duplicate of [#1071 - Specified key was too long; max key length is 767 bytes](http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes) – PhoneixS Dec 20 '16 at 08:54

3 Answers3

48

If you're using utf8mb4, and you have unique indexes on varchar columns that are greater than 191 characters in length, you'll need to turn on innodb_large_prefix to allow for larger columns in indexes, because utf8mb4 requires more storage space than utf8 or latin1. Add the following to your my.cnf file.

[mysqld]
innodb_file_format=barracuda
innodb_file_per_table=1
innodb_large_prefix=1
init_connect='SET collation_connection = utf8mb4_unicode_ci; SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

More info about the why and future from MySQL 5.7 documentation:

If innodb_large_prefix is enabled (the default in MySQL 5.7.7), the index key prefix limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.

innodb_large_prefix is deprecated in MySQL 5.7.7 and will be removed in a future release. innodb_large_prefix was introduced in MySQL 5.5 to disable large index key prefixes for compatibility with earlier versions of InnoDB that do not support large index key prefixes.

To sum up, the limit is only there for compatibility and will be increased in future versions.

jsears
  • 4,511
  • 2
  • 31
  • 36
  • I finally got this to work for me with importing a database dump after adding the line: `default-storage-engine=InnoDB` along with the above, inside the `[mysqld]` section. – JamesWilson Feb 07 '17 at 19:52
  • 2
    It's worth mentioning that the boolean entries in the config file must be set as `1` and not `ON` in MySQL 5.5, otherwise they are not taken into consideration. Also, since it is not possible to set a default row format in 5.5 and 5.6 (*innodb_default_row_format* system variable was added in 5.7.9), the tables must be created with `ROW_FORMAT=DYNAMIC`. – dregad Oct 11 '17 at 08:07
  • Note that in the above suggested additions to config, the first `init_connect` is ignored or overwritten so the result is only "SET NAMES utf8mb4". If you want both in the init string, you might use `init_connect = 'SET collation_connection = utf8mb4_unicode_ci; SET NAMES utf8mb4'` – marcguyer Jan 05 '18 at 15:56
9

MySQL reserves the max amount for a UTF8 field which is 4 bytes, so that is why you are blowing past the 1000 byte limit. My recommendation is to create the varchar at less than 255 or create it without UTF8.

Both of those solutions are probably not right for you or you would have already tried that.

The only other solution I can think of is to split the column into 2 small columns and create an unique index on both of those fields, but I believe that you would get the same error as above.

Since you probably need UTF8, I would seriously look at reducing the varchar(255) column down a little to 250 (or 249) to make this work.

Nik
  • 2,885
  • 2
  • 25
  • 25
JohnL
  • 216
  • 1
  • 3
  • 3
    I hate to nitpick, but a UTF-8-encoded character can be up to 6 bytes. Four bytes per character implies an internal UCS-4 (probably a C `wchar_t`-based) representation. (actually, I lied. I like to nitpick. Sorry!) – Alexios Dec 03 '13 at 18:44
  • 5
    @Alexios Default `utf8` encoding is 3 byte in MySQL, whereas `utf8_mb4` is 4 byte variable (`utf32` is 4 byte fixed) – Daniel W. Apr 07 '14 at 08:06
8

Anyone who does need a larger key length should look at innodb_large_prefix

visit http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_large_prefix

Omesh
  • 27,801
  • 6
  • 42
  • 51