MySQL Specified key was too long; max key length is 767 bytes
When restoring a MySQL database, I got the error “Specified key was too long; max key length is 767 bytes”.
|
1 2 3 |
user@host [~]$ mysql new_db < wordpress-2021-12-19-b51f8a6.sql ERROR 1071 (42000) at line 745 in file: 'wordpress-2021-12-19-b51f8a6.sql': Specified key was too long; max key length is 767 bytes user@host [~]$ |
MySQL version 5.6 and older versions have a limit of 767 bytes prefix limit.
https://dev.mysql.com/doc/refman/5.6/en/create-index.html
Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix length limit is 1000 bytes.
From the error message, the error was on line 745, when checking the SQL file, I found the following SQL statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE `wpk4_gla_merchant_issues` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `product_id` bigint(20) NOT NULL, `issue` varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL, `code` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL, `severity` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'warning', `product` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL, `action` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL, `action_url` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL, `applicable_countries` text COLLATE utf8mb4_unicode_520_ci NOT NULL, `source` varchar(10) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'mc', `type` varchar(10) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'product', `created_at` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `product_issue` (`product_id`,`issue`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci; |
To fix it, find
|
1 |
UNIQUE KEY `product_issue` (`product_id`,`issue`) |
Replace with
|
1 |
UNIQUE KEY `product_issue` (`product_id`,`issue`(191)) |
This will limit the length of the issue column to 191 chars. Each character takes 4 bytes to store in utf8mb4 character set. The limit 191 is found by trying with default length for issue field 200, then reducing it until the SQL gets created properly. The calculation is 191 * 4 + length of id column < 767 The correct solution is to use newer MySQL versions like MySQL 5.7, which allows length up to 3072 bytes.