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

Answer 2

There are 5 solutions here .

If you are hitting the limit because of trying to use CHARACTER SET utf8mb4. Then do one of the following (each has a drawback) to avoid the error:

⚈  Upgrade to 5.7.7 for 3072 byte limit -- your cloud may not provide this; 
⚈  Change 255 to 191 on the VARCHAR -- you lose any keys longer than 191 characters (unlikely?); 
⚈  ALTER .. CONVERT TO utf8 -- you lose Emoji and some of Chinese; 
⚈  Use a "prefix" index -- you lose some of the performance benefits. 
⚈  Stay with 5.6/5.5/10.1 but perform 4 steps to raise the limit to 3072 bytes: 

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  (or COMPRESSED)

On second glance, don't use TINYTEXT, change to VARCHAR(255) which does not need the prefixing!

On third glance, UNIQUE(x, y(255)) is very likely to be wrong. It says "the combination of x and part of y is unique". It does not say x and all of y is unique.

Fourth... Which version of 5.7? Works fine with 5.7.15:

mysql> 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 `private_id` (`private_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.15    |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE tbl_mappings\G
*************************** 1. row ***************************
       Table: tbl_mappings
Create Table: 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 `private_id` (`private_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

Answer 3

This problem exists before the limit was raised in 5.7.7