MySQL и ошибка "Index column size too large. The maximum column size is 767 bytes."

Иногда при восстановлении БД MySQL из дампа или просто создании структуры БД может возникать такая ошибка как «Index column size too large. The maximum column size is 767 bytes.».

В интернете можно найти очень много статей и ответов на вопросы как устранить эту ошибку, но в чем же все таки её причина? Как правильно разрешить эту ошибку? Давайте разберемся ниже.

Если перевести ошибку «Index column size too large. The maximum column size is 767 bytes.» ну русский язык, то звучит она так: «Размер столбца для индекса слишком большой. Максимальный размер столбца составляет 767 байт.»

А теперь собственно корень этой ошибки — если коротко, то это кодировка базы, конкретной таблицы или поля в таблице.

Но что за 767 байт и причем тут кодировка? А вот причем:

В InnoDB движке (вплоть до версии MySQL 5.5.14) существуют следующие ограничения на длину поля с уникальным ключем:
— для кодировки utf8 и типа поля TEXT и VARCHAR максимальная длина поля 255 байт;
— для кодировки utf8mb4 и типа поля TEXT и VARCHAR максимальная длина поля 191 байт;

В кодировке utf8 (utf8mb3) один символ занимает 3 байта, то есть 3 * 255 получаем наши 767 байт лимита.
В кодировке utf8mb4 один символ занимает 4 байта, то есть 4 * 191 получаем наши 767 байт лимита.

Для MySQL ветки 5.6 можно открыть раздел 14.8.1.7 Limits on InnoDB Tables и узнать более детально о других ограничениях.

Начиная с MySQL 5.6.3 появилась опция innodb_large_prefix, которая увеличивает лимит с 767 байт до 3072 байт, но только для таблиц со строками формата DYNAMIC и COMPRESSED и только для формат файлов Barracuda (innodb_file_format=Barracuda) и только при включенной опции innodb_file_per_table=ON. Для строк формата COMPACT и REDUNDANT ограничение в 767 байт сохраняются.

Учитывая, что в разных версиях MySQL параметры innodb_large_prefix, innodb_file_format и innodb_file_per_table по умолчанию могут быть включены, а могут быть нет, то от знания версии MySQL и зависит во многом как правильно решить проблему «Index column size too large. The maximum column size is 767 bytes.».

Если у Вас MySQL версии старше 5.6.6, но младше 5.7.6, то по-умолчанию будут такие значения:
innodb_large_prefix = OFF
innodb_file_format = Antelope
innodb_file_per_table = ON
Формат строк (ROW_FORMAT) для вновь создаваемых таблиц будет COMPACT, изменить формат строк по умолчанию в MySQL 5.6.x нет возможности, такая опция появилась только начиная с MySQL 5.7.9 (см. опцию innodb_default_row_format), для MySQL 5.6.x придется указывать ROW_FORMAT=DYNAMIC при создании новых таблиц или менять формат строк в уже существующих таблиц.

Если у Вас MySQL версии старше 5.7.7, то по-умолчанию будут такие значения:
innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_per_table = ON
innodb_default_row_format = DYNAMIC

Для MySQL версий старше 5.6.6, но младше 5.7.6 есть 2 пути решения проблемы:

Путь №1: Если Вы хотите продолжить использовать кодировку базы и таблиц utf8mb4 и иметь возможность создавать уникальные ключи (UNIQUE INDEX) на полях TEXT и VARCHAR с длинной поля 255 байт и более:

1. Изменить следующие параметры в my.cnf в виду:

[mysqld]
innodb_large_prefix = ON
innodb_file_format = Barracuda

2. При создании новых таблиц обязательно указывать ENGINE=InnoDB ROW_FORMAT=DYNAMIC CHARSET=utf8mb4

Пример создания таблицы и уникального ключа:

CREATE DATABASE mydb;
USE mydb;

CREATE TABLE `users` (
        `uid`            bigint unsigned NOT NULL,
        `login`          varchar(32)  DEFAULT '' NOT NULL,
        `email`          varchar(255)  DEFAULT '' NOT NULL,
        `name`           varchar(255) DEFAULT '' NOT NULL,
        PRIMARY KEY (uid)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC CHARSET=utf8mb4;

CREATE UNIQUE INDEX `email_idx` ON `users` (email);

Проверить формат строк в нашей табличке можно выполнив простой запрос:

mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mydb';
+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | ROW_FORMAT |
+--------------+------------+------------+
| mydb         | users      | Dynamic    |
+--------------+------------+------------+
1 row in set (0.00 sec)

Посмотреть наш уникальный ключ:

mysql> SHOW INDEX FROM users;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY   |            1 | uid         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| users |          0 | email_idx |            1 | email       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

Путь №2: Если Вы не можете использовать новую версию MySQL (то есть > 5.6.3) в которой есть опции innodb_large_prefix и новый формат файлов Barracuda (innodb_file_format=Barracuda), то увы, преодолеть ограничение для полей VARCHAR или TEXT — 255 байт для кодировки utf8 или 191 байт для кодировки utf8mb4 Вам будет невозможно. В таком случае чтобы перейти на кодировку базы utf8mb4 Вам придется либо уменьшать в таблицах размеры полей VARCHAR или TEXT с 255 до 191 байт, либо ограничить размер индекса по полю до 191 байта.

Пример создания таблицы и индекса размером 191 байт:

CREATE DATABASE mydb;
USE mydb;

CREATE TABLE `users` (
        `uid`            bigint unsigned NOT NULL,
        `login`          varchar(32)  DEFAULT '' NOT NULL,
        `email`          varchar(255)  DEFAULT '' NOT NULL,
        `name`           varchar(255) DEFAULT '' NOT NULL,
        PRIMARY KEY (uid)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE UNIQUE INDEX `email_idx` ON `users` (email(191));

Теперь давайте посмотрим формат строк для нашей таблички и наш уникальный индекс:

mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mydb';
+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | ROW_FORMAT |
+--------------+------------+------------+
| mydb         | users      | Compact    |
+--------------+------------+------------+
mysql> SHOW INDEX FROM users;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY   |            1 | uid         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| users |          0 | email_idx |            1 | email       | A         |           0 |      191 | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

Как можно увидеть из поля Sub_part, у нас уникальный индекс, но по полю с ограничением в длине.
Кого-то такой вариант может устроить, а кого-то и нет.

Так что мой Вам совет — обновляйте MySQL или MariaDB на своих серверах до последних версий.
Если у Вас MySQL 5.6.x (последние версии), то включайте опции innodb_large_prefix = ON и innodb_file_format = Barracuda и используйте кодировку utf8mb4 и не забывайте создавать таблицы с ROW_FORMAT=DYNAMIC.
Если у Вас Oracle MySQL или Percona Server for MySQL версии 5.7.x (последние версии), то все нужные опции уже включены, используйте кодировку utf8mb4 без каких либо дополнительных танцев с бубном.

На этом все, до скорых встреч. Если у Вас возникли вопросы или Вы хотите чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.