Exporting table from MySQL 5.7 to MySQL 5.6 (Error)

Question

While running the following queries on MySQL 5.6.39,

DROP TABLE IF EXISTS `sessions`;

CREATE TABLE `sessions` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `payload` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_activity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


ALTER TABLE `sessions`
  ADD UNIQUE KEY `sessions_id_unique` (`id`);

I get the following error :

However, if I remove the part that says COLLATE=utf8mb4_unicode_ci, everything works fine. Why is this happening ?

The above query is an export from phpMyAdmin (MySQL version 5.7.22)

Answer 1

It seems that the id column is varchar(255) so from your config file enable the innodb_large_prefix on this instance of MySQL to resolve the problem but per the below, this explains why this is occurring from my interpretation.

You can run show variables and see if the innodb_large_prefix if ON or OFF.

---

Maximums and Minimums

• By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

  Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enabling innodb_large_prefix on the master if it cannot also be enabled on slaves.

  The limits that apply to index key prefixes also apply to full-column index keys.

Answer 2

5.5 and 5.6 have the "767 error"; 5.7 gets around it.

• Can you live with 191 characters instead of 255?
• Will utf8 suffice instead of utf8mb4?

Here are details on 3 ways to get around "767" if you have 5.6 that might work for you: http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

(Prefix indexing is not a reasonable option for the PRIMARY KEY.)