Bug #68352 Error 1071 (Key too long) is being incorrectly reported.

• Submitted: 12 Feb 2013 19:34
• Modified: 12 Feb 2013 19:42
• Reporter: Van Stokes
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.6.10
• OS: Linux (Ubuntu 12.04 x86_64)
• CPU Architecture: Any
• Tags: INDEX, innodb, key

[12 Feb 2013 19:34] Van Stokes

Description:
When attempting to add a primary key or index to a table I receive an error 1071: Specified key was too long; max key length is 767 bytes.

How to repeat:
show create table oc_pictures_images_cache

CREATE TABLE `oc_pictures_images_cache` (
  `uid_owner` varchar(64) NOT NULL DEFAULT '',
  `path` varchar(256) NOT NULL DEFAULT '',
  `width` int(11) NOT NULL DEFAULT '0',
  `height` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- Fails with 1071
ALTER TABLE `owncloud`.`oc_pictures_images_cache`   
  ADD PRIMARY KEY (`uid_owner`, `path`);

-- Fails with 1071
ALTER TABLE `owncloud`.`oc_pictures_images_cache`   
  ADD  INDEX `oc_pictures_images_cache_idx1` (`uid_owner`, `path`);

Suggested fix:
`uid_owner` varchar(64) + `path` varchar(256) is less than 767 bytes.

Correct error message or correct the math.

[12 Feb 2013 19:42] MySQL Verification Team

Since utf8 characters may be up to 3 bytes each, this error message is correct.
However dynamic/compressed innodb tables do not have this limitation;  See:

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

On a server started with:
--innodb_large_prefix=1 --innodb-file-format=barracuda --innodb-file-per-table

mysql> show create table oc_pictures_images_cache\G
*************************** 1. row ***************************
       Table: oc_pictures_images_cache
Create Table: CREATE TABLE `oc_pictures_images_cache` (
  `uid_owner` varchar(64) NOT NULL DEFAULT '',
  `path` varchar(256) NOT NULL DEFAULT '',
  `width` int(11) NOT NULL DEFAULT '0',
  `height` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid_owner`,`path`),
  KEY `oc_pictures_images_cache_idx1` (`uid_owner`,`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)