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

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 2

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.

Answer 3

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