Error: Specified key was too long; max key length is 1000 bytes

Question

Error:

1071 - Specified key was too long; max key length is 1000 bytes 

CREATE TABLE `phppos_modules_actions` (
  `action_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `module_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `action_name_key` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `sort` INT NOT NULL ,
  PRIMARY KEY ( `action_id` , `module_id` )
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I know the error occurs because of 255x2x3 (3 bytes per character)

This doesn't happen on all installations. What setting can I change?

Answer 1

In my case, MySQL was started without InnoDB support. And during DB backup importing - tried to create MyISAM tables.

At the same time, no errors were shown in the console during MySQL restart.
Only when checked MySQL log file - found this.

BTW an error was related to innodb_log_file_size = 4G, only when changed it to innodb_log_file_size = 1G - InnoDB support was enabled.

Answer 2

NO_ENGINE_SUBSTITUTION disabled with INNODB not active, a bad combination

• Up to MySql 5.5 the sqlmode default was a blank string, that means that the sqlmode NO_ENGINE_SUBSTITUTION was not set by default

According to MySql docs (see https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_engine_substitution) this is the meaning of sqlmode NO_ENGINE_SUBSTITUTION:

Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.

Because storage engines can be pluggable at runtime, unavailable engines are treated the same way:

With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE, a warning occurs and the table is not altered.

With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable.

• So: if NO_ENGINE_SUBSTITUTION is disabled AND INNODB is switched OFF, MySql will switch to MYISAM also if you specify INNODB in your CREATE TABLE statement.
• If the table you are creating is OK for MYISAM, you just receive a warning and the table is created. That is not your case, your creation statement include an index that is beyond the 1000 bytes limit of MYISAM, then the creation fails with error 1071 reporting the error of MYISAM. That is because the working engine is MYISAM, not INNODB.

PROOF

MySql version 5.1.56 community

Case 1:

Options in my.cnf  
sql-mode=""  
default-storage-engine=MYISAM  
skip-innodb uncommented (without#) 

Return on execution of your create statement:
Error Code: 1071. Specified key was too long; max key length is 1000 bytes

Explanation: INNODB is not active, the engine is automatically switched to MYISAM  
that returns this error as they key is longer than MYISAM 1000 bytes limit.  
The key length is: 
2 fields x 255 char x 3 bytes utf8 encoding + 2 x 1 length byte = 1532 bytes   

Case 2:

Options in my.cnf
sql-mode="NO_ENGINE_SUBSTITUTION"
default-storage-engine=MYISAM
skip-innodb uncommented (without#)

Return on execution of your create statement:
Error Code: 1286. Unknown table engine 'INNODB'

Explanation: INNODB is not active but the engine substitution is not permitted
by sql mode therefore the DB returns an error about the attempt of using a disabled engine. 

Case 3:

Options in my.cnf
sql-mode="NO_ENGINE_SUBSTITUTION"
default-storage-engine=MYISAM
skip-innodb commented (with#)

Return on execution of your create statement:
Table creation OK!

Explanation: INNODB is active (skip-innodb commented) and it is used also if      
the default engine is MYISAM.

To reproduce the tests restart MySql after every change in my.cnf.

Since MySql version 5.6 sqlmode is no more empty by default and contains NO_ENGINE_SUBSTITUTION, moreover INNODB is the default engine, so the error is difficult to meet.

OTHER TESTS

No other way of reproducing the error:

Error Code: 1071. Specified key was too long; max key length is 1000 bytes 

while trying to create an INNODB table has been found.

In INNODB you have two kinds of ERROR 1071:

Error Code: 1071. Specified key was too long; max key length is 767 bytes

this has nothing to do with innodb_large_prefix ON or OFF, but is only related to the size of a single VARCHAR column being used as an index.

Mysql store varchar utf8 with 3 bytes plus 1 byte for the length up 255 character and 2 after (see: http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html), so if you try to set a key with VARCHAR(256) utf8 you get:

256 x 3 + 2 = 770 bytes

and you get the previous error, as the max key length for a single column is 767 bytes for an InnoDB table. A VARCHAR(255) is ok, because:

255 x 3 + 1 = 766 bytes

I tested it on four installations of Mysql, version 5.1.56, 5.5.33, 5.6 and 5.7, and that is confirmed. No issue with your query with VARCHAR(255), issue with VARCHAR(256):

Error Code: 1071. Specified key was too long; max key length is 767 bytes

As you can see the message is different, because it is an INNODB message not a MYISAM one!

The other type of ERROR 1071 for INNODB tables is:

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

This is related to keys with multiple columns. For those keys to be enabled you need to set innodb_large_prefix to on. Anyway, if you try to run something like this:

CREATE TABLE `phppos_modules_actions` (
  `action_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `module_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `action_name_key` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `action_name_key1` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `action_name_key2` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `sort` INT NOT NULL ,
  PRIMARY KEY ( `action_id` , `module_id`, `action_name_key`, `action_name_key1`, `action_name_key2` )
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

With a key of 5 VARCHAR(255) utf8 columns, that is 3830 bytes, you will run into the:

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

Exotic Hypothesis

During the search of the cause I formulated and tested different and pretty weird hypothesis:

ROW format

Tested REDUNDANT, COMPACT, COMPRESS, DYNAMIC: no impact on table creation with your statement.

FILE format

Tested Antelope and Barracuda: no impact on table creation with your statement.

MySql built

Tested 32bit and 64 bit MySql: no impact on table creation with your statement.

Others similar failures

Here you can find the same error in the same situation:

https://www.drupal.org/node/2466287

I tested that statement in the 3 test situations listed in PROOF and it reproduced exactly the same behavior as yours, so I can say the issue is the same. In that case they switched to other DB, but the problem is the mix of setting, not the DB version.

References

A very good article of indexing with INNODB is given here:

http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

WARNING: disabling INNODB by uncommenting skip-innodb in my.cnf after the creation of INNODB table with index longer than 1000 will not permit the start of MySql service

Regards

Answer 3

As says, the total length of your index is too long.

The short answer is that you shouldn't be indexing such long VARCHAR columns anyway, because the index will be very bulky and inefficient.

The best practice is to use prefix indexes so you're only indexing a left substring of the data. Most of your data will be a lot shorter than 255 characters anyway.

767 bytes is the stated prefix limitation for InnoDB tables - its 1,000 bytes long for MyISAM tables.

According to the response to this issue, you can get the key to apply by specifying a subset of the column rather than the entire amount. Means You can declare a prefix length per column as you define the index.

For example:

KEY `key_name` (`action_id`(50),`module_id`(50))

Here is the example to explain it more clearly: I created a table and inserted some data in it.

CREATE TABLE `phppos_modules_actions` (
  `action_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `module_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `action_name_key` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `sort` INT NOT NULL ,
  PRIMARY KEY ( `action_id`(50) , `module_id`(50) )
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

But what's the best prefix length for a given column? Here's a method to find out:

SELECT
 ROUND(SUM(LENGTH(`action_id`)<10)*100/COUNT(*),2) AS pct_length_10,
 ROUND(SUM(LENGTH(`action_id`)<20)*100/COUNT(*),2) AS pct_length_20,
 ROUND(SUM(LENGTH(`action_id`)<50)*100/COUNT(*),2) AS pct_length_50,
 ROUND(SUM(LENGTH(`action_id`)<100)*100/COUNT(*),2) AS pct_length_100
FROM `phppos_modules_actions`;


+---------------+---------------+---------------+----------------+
| pct_length_10 | pct_length_20 | pct_length_50 | pct_length_100 |
+---------------+---------------+---------------+----------------+
|         42.86 |         80.20 |        100    |         100    |
+---------------+---------------+---------------+----------------+

This tells you that 80% of your strings are less than 20 characters, and all of your strings are less than 50 characters. So there's no need to index more than a prefix length of 50, and certainly no need to index the full length of 255 characters.

Tweak as you need to get the key to apply, but I wonder if it would be worth it to review your data model regarding this entity to see if there's improvements that would allow you to implement the intended business rules without hitting the MySQL limitation.

Some alternative way about settings in innodb can be found at http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

Answer 4

You have (accidentally?) InnoDB turned off. It is creating the table as MyISAM and the entire key is more than 1000 bytes.

Check variables of whether you have InnoDB, and whether there is "no_engine_substitution" (if that existed way back in 5.1.56) Also check mysqld.err to see if it has some relevant errors on startup.

Answer 5

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

Answer 6

Official doc 5.6

Limits on InnoDB Tables

The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. This limit applies to the length of the combined index key in a multi-column index.

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

---

The MyISAM Storage Engine

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

http://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html

---

However your table is declared as InnoDB. So I don't know what to think

There's also a clue at the end of this old bug

If you need this you should really look at MySQL 5.5 and the innodb_large_prefix option that is available from 5.5.14 (July 2011) onwards because it probably does what you are looking for:

"Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes), for InnoDB tables that use the DYNAMIC and COMPRESSED row formats. (Creating such tables also requires the option values innodb_file_format=barracuda and innodb_file_per_table=true.) See Section 13.3.15, “Limits on InnoDB Tables” for the relevant maximums associated with index key prefixes under various settings.

Answer 7

In MySQL 5.6.3+ with some limitations(need ROW_FORMAT=DYNAMIC, innodb_file_format=BARRACUDA and innodb_file_per_table=true) you can enable innodb_large_prefix for a 3072 byte key length limit.

Answer 8

It seems to be related in the storage engine. My end user was using MyISAM which handles the storage of data differently causing that error.

Answer 9

I don't now how much flexibility you have, but here are some options:

• Upgrade MySQL version to the latest release (I tested the code on MySQL 5.5.25 and got no errors.)

• Change from utf8 to latin1.

• Reduce the size of the the fields that make up the primary key. Create a separate auto_increment primary key field to replace the existing key. Create a separate index on the first field action_id (but not the second field)

Other than those options, you are pretty much stuck since there is no setting that you can change in MySQL that will enable an index key greater than 1000 bytes.