This error was encountered trying to import a mysql database dump from one server to another.

ERROR 1071 (42000) at line 843: Specified key was too long; max key length is 1000 bytes

This error is actually caused by multiple lines but the fix is the same for each occurrence.  This was the first offending line:

CREATE TABLE `avsys_parameter` (

`id` int(11) NOT NULL auto_increment,

`parameter` varchar(255) NOT NULL,

`value` varchar(255) NOT NULL,

`ord` double NOT NULL default ‘0’,

PRIMARY KEY  (`id`),

UNIQUE KEY `comb` USING BTREE (`parameter`,`value`)

) ENGINE=INNODB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

The problem is that the unique key “comb” is larger than the MySQL limitations for key size.  The default max key size in MySQL for InnoDB tables is 767 bytes and for MyISAM tables 1000 bytes.  Even though this is specified as an InnoDB table I believe it’s trying to create it as MyISAM as this server is configured without InnoDB support.  IF you have InnoDB support you can add the following config option to /etc/my.cnf:

innodb-large-prefix

This doesn’t need an “On” or “Yes” or anything.  Just put it in the config.  It will raise the max key size for InnoDB tables to 3500, or around that.  As far as I know there isn’t a way to raise this setting for MyISAM tables.  The “fix” was to have the key only index part of the columns it’s indexing.  This is done by changing this:

UNIQUE KEY `comb` USING BTREE (`parameter`,`value`)

to this:

UNIQUE KEY `comb` USING BTREE (`parameter`(150),`value`(150))

It’s worth noting that this could theoretically cause a problem on a unique key.  If two different entries have a “parameter” and “value” values where the first 150 characters of each are not unique a new insert will fail.  In this particular case this is basically impossible but it should be considered when using this method when you could run into that problem.