MySQL: Specified key was too long; max key length is 767 bytes

Happens when we try to add long keys to our table. For example, varchars in utf8mb4 with 256 size, that’s a 1024bytes key! So we can make these strings shorter, trying to do as precise as we can. e.g: If a string won’t be larger than 32 characters, do a 32-char varchar instead.

But in many other cases it can be a problem. We may have lots of tables in a dump, so we don’t have time to optimize them, or they may be optimized and we have no other option.

This error has to do with three configuration options:

  • innodb_large_prefix
  • innodb_file_format
  • innodb_default_row_format

And we can set them globally for MySQL or MariaDB:

set global innodb_large_prefix=on;> set global innodb_file_format=Barracuda;> set global innodb_default_row_format = DYNAMIC;

We could skip global configuration and set row_format for single tables.