Mysql – Exporting table from MySQL 5.7 to MySQL 5.6 (Error)

MySQLmysql-5.6phpmyadmin

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 :

enter image description here

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)

Best Answer

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.

Related Question