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”.

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.

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

UNIQUE KEY `product_issue` (`product_id`,`issue`)

Replace with

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.