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.

Sublime

When i reduced size of name which is in : UNIQUE KEY name (private_id,name(255)), to UNIQUE KEY name (private_id,name(10)), it works but i need size 255.? – Sublime

Issue appears to be related to the TINYTEXT datatype. Behavior replicated with 5.7.17-0ubuntu0.16.04.1-log, with MYISAM engine as well as INNODB. Workaround is to use VARCHAR(255) in place of TINYTEXT. (My answer is updated below.) – Deca

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

Deca

As you suggested i tried 254 but its not wotrking unless i set it to 80 or below. Is there any way to increase Index key length limit for InnoDB ? – Sublime

Still no luck after adding ROW_FORMAT=DYNAMIC – Sublime

You first need to change my.cnf (or equivalent) and add: innodb_large_prefix=ON; then restart mysql to get this working – Ultramicrochemistry

Yeah, 4 steps are needed (Barracuda, FPT, prefix, Dynamic); see my answer. – Cannibalize

Thanks.changing tinytext to varchar(255) worked fine. – Sublime

The reason tinytext fails and varchar works is because tinytext has a limit of 255 bytes, while varchar(255) has a limit of 255 characters. 86 utf8mb3 (or 64 utf8mb4) characters could exceed 255 bytes. The behavior is then described in the manual at dev.mysql.com/doc/en/create-index.html beginning "If a specified index prefix exceeds the maximum column data type size" It has nothing to do with row formats, large prefixes, or storage engine, all of which have limits much higher than 255. – Salvage

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)

Cannibalize

TINYTEXT is only 255 bytes anyway! Don't use MyISAM. – Cannibalize

You just found a couple of reasons why you should not use MyISAM even for testing. (And you explained why my Answer was not quite right.) – Cannibalize

I don't want to dwell long on this Question since there are so many things that need to be fixed: don't use MyISAM, don't use index prefixing, don't use TINYTEXT, don't mix UNIQUE and prefixing. And another that I have not mentioned: Why have the UNIQUE at all? – Cannibalize

Answer 3

This problem exists before the limit was raised in 5.7.7

Bichromate