MySQL Error #1071 – Specified key was too long; max key length is 767 bytes

The Problem

When managing a MySQL database, you might encounter the Error #1071 – Specified key was too long, which indicates an issue with the size of an index that you’re trying to create. This is a common issue faced by developers, especially when they are working with character sets like utf8mb4 that require more bytes per character. In this tutorial, we will edify you with the causes and some efficacious resolutions to overcome this error.

Understanding Error #1071

MySQL has a maximum key length of 767 bytes for InnoDB tables or 1000 bytes for MyISAM tables. If you use a multi-byte character set such as utf8, each character may use up to three bytes, and utf8mb4 character set up to four bytes. When you exceed the permissible key length, MySQL will issue Error #1071.

Resolutions for Error #1071 in MySQL

Alter Table Charset

Change the character set of the table or column to a single-byte such as latin1. However, this could have implications for storing characters outside of Western European languages.

Resolution steps:

1. Access your MySQL database using a command-line client or a database management tool.
2. Execute the ALTER TABLE command to modify the character set of the relevant table. Make sure to backup your data before any modifications.
3. If necessary, convert existing data to the new character set.

Example:

ALTER TABLE your_table_name CONVERT TO CHARACTER SET latin1;

After running this command, your create index command should work without the Error #1071. The output will confirm the completion of the operation without any errors.

Notes: This approach can result in loss of data for characters which do not fit into the latin1 character set, thus use it with caution.

Reduce Column Index Length

If changing the character set is not an option, considering limit the length of indices on utf8 and utf8mb4 columns can avoid the error.

Resolution steps:

1. Identify which column(s) are causing the Error #1071.
2. Determine the appropriate length for your index, considering the character set and its maximum byte-per-character count.
3. Alter your index definition to use a prefix length.

Example:

ALTER TABLE your_table_name ADD INDEX(your_column_name(191));

The output will not show Error #1071 and the index will be created with a limited length. ‘191’ is a typical value for utf8mb4, since 191 characters * 4 bytes/character = 764 bytes < 767 bytes.

Notes: This will only index the first part of the text, it might not suitable if you expect duplicate values in the parts being indexed. Use with a good understanding of your data and index needs.

Migrate to INNODB File Format Barracuda

InnoDB’s older ‘Antelope’ format does have the 767-byte index limit, but the newer ‘Barracuda’ format supports larger indexes if ‘innodb_large_prefix’ option is enabled.

Resolution steps:

1. Make sure your MySQL version supports the Barracuda format and the ‘innodb_large_prefix’ setting.
2. Adjust the relevant settings in your MySQL configuration file.
3. Change the table format to Barracuda if necessary.

Example:

SET GLOBAL innodb_file_format = 'Barracuda'; SET GLOBAL innodb_file_format_max = 'Barracuda'; SET GLOBAL innodb_large_prefix = 'ON'; ALTER TABLE your_table_name ROW_FORMAT=DYNAMIC;

The commands above will output notices showing the changed variables and the completion of the ALTER TABLE operation, without any error.

Notes: Requires MySQL 5.5.14 or newer. Before applying these changes, make sure that the update will not disrupt your systems that rely on the older file formats. This approach offers more flexibility, but also requires deep understanding of your database’s setup and the potential impact of the changes.

Check Collation and Charset Settings

Different collations can require different amounts of space. Make sure your character set and collation settings are consistent across database, table, and columns.

Resolution steps:

1. Examine current charset and collation settings for system, database, table, and column.
2. Synchronize settings to ensure consistency without unnecessarily maximizing index size.

This is more of checking settings rather than running commands.

Notes: This solution does not involve changing or patching up but rather ensuring optimal set-up configuration.