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.

Solution 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

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

Comments

[tushar patil]

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 ?

[tushar patil]

Still no luck after adding ROW_FORMAT=DYNAMIC

[Jacques Amar]

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

[Rick James]

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

[Rick James]

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

[Rick James]

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?

[Rick James]

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

[tushar patil]

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

[Scott Noyes]

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.