How to resolve "specified key was too long max key length is 255 bytes" in mysql?

Question

Whenever i fire this query from one of the mysql client (emma):

CREATE TABLE `tbl_mappings` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `private_id` int(11) unsigned NOT NULL,
  `name` tinytext NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`private_id`,`name`(255)),
  KEY `FK_tbl__private_integrations_mappings_tbl__private_integrations` (`private_id`),
  CONSTRAINT `FK_tbl__private_integrations_mappings_tbl__private_integrations` FOREIGN KEY (`private_id`) REFERENCES `tbl__private_integrations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

i get error : specified key was too long max key length is 255 bytes

i am using mysql server 5.7,ubuntu 16.04

And i have tried adding configuration in my.cnf under [mysqld] :

innodb_file_format=barracuda
innodb_file_per_table=1
innodb_large_prefix=1
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-storage-engine=InnoDB

And then restarted mysql service .still it wont work.

Any help is appreciated. Thank you.

Answer 1

EDIT

Issue appears to be related to the TINYTEXT datatype. (I can replicate the observed behavior with MySQL version 5.7.17-0ubuntu0.16.04.1-log, using either InnoDB or MyISAM.)

The short answer (as a workaround, how to resolve the 1071 warning) is to use datatype VARCHAR(255) in place of TINYTEXT.

---

I ran several test cases with various character sets (utf8, utf8mb4, latin1) and using InnoDB and MyISAM storage engines. The 1071 warning appears to be related to the prefix length specified in the index on the TINYTEXT column... appears to be a MySQL limit on the prefix length (not specifically related to InnoDB, since I can replicate the behavior with MyISAM.) I did not test with any other TEXT types other than TINYTEXT.

---

PREVIOUS ANSWER

Index key length limit for InnoDB tables is 767 bytes.

The name(255) in the key definition is specifying the first 255 characters of name. With the MySQL utf8 characterset, a character can take from one to three bytes. And 255 times three is 765. Add in the four bytes for the int private_id, and that's 769, which exceeds the maximum.

That's why you are getting the error.

Several approaches to resolving that.

Easiest would be to reduce the number of characters of name that are included in the index, e.g.

UNIQUE KEY `name` (`private_id`,`name`(254))

If that doesn't satisfy your use case, then you might need to consider using the deprecated innodb_large_prefix setting. You would need to use DYNAMIC or COMPRESSED row format. See the discussions here:

https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-specification.html