While running the following queries on MySQL 5.6.39,


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)

  • Yash - Any chance of accepting my answer below assuming it worked to resolve the issue by turning the upper right hand side check mark from grey to green in my answer? Sep 19, 2019 at 12:52

2 Answers 2


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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.