Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
ERROR 1071 (42000): Specified Key was Too Long; Max Key Length is 767 Bytes
Symptoms
During the import to MySQL, when you create a key for a InnoDB table, the “ERROR 1071 (42000)” error arises. For example,
CREATE TABLE department ( id INT, name VARCHAR(1000) ); ALTER TABLE department ADD PRIMARY KEY (id, name); ERROR 1071 (42000) at line 8: Specified key was too long; max key length is 767 bytes
Cause
The maximum key length for the MySQL database for InnoDB Engine is 767 bytes. The key length includes the sum lengths of all the columns included in the key.
Solutions
There are several solutions for this issue:
- Changing the size of some columns. For example, if VARCHAR(300) is used to store last names, you can reduce its size to 100 with minimal risk of data loss.
- Specifying the partial column length for the character columns in the key. For example, for VARCHAR(1000) column, you can specify only 100 characters to be used in the key.
ALTER TABLE department ADD PRIMARY KEY (id, name(100));
In this example only 100 bytes of the NAME column participate in the primary key.
- Using a different set of columns for the key.
If you have any other questions, please contact us: support@ispirer.com