Exporting table from MySQL 5.7 to MySQL 5.6 (Error)

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)

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.

StackExchange Database Administrators Q#209230, answer score: 3