MySql – ERROR 1071 Specified key was too long; max key length is 1000 bytes while restoring dump

All we need is an easy explanation of the problem, so here it is.

while trying to restore a MySql Dump, i get the following error:

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

any ideas?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You may to have adjust some VARCHAR length in some table based on the Character Set you are using.

That exact error message is actually posted as a bug report from Nov 2004, when in fact, it is not really a bug. That should direct you on how to adjust key lengths, especially your PRIMARY KEYs.

If you know which table is causing the Error 1071, you have to do the following:

Step 01) mysqldump only database schema

mysqldump --routines --triggers --all-databases --no-data > MySQLSchema.sql

Step 02) mysqldump only data

mysqldump --all-databases --no-create-info > MySQLData.sql

Step 03) Using vi or some other editor, edit the table’s PRIMARY KEY to manually limit the PRIMARY KEY in such a way that the key does not exceed 1000 characters.

Step 04) Load the schema

mysql -A < MySQLSchema.sql

Step 05) Load the data

mysql -A < MySQLData.sql

You may have to resort to this if you cannot otherwise figure this out,

UPDATE 2012-01-23 11:43 EDT

Since you mentioned a certain table has the wrong storage engine, here is what you do:

Look above at Step 03. Go find the table in the file. Look for ENGINE=MyISAM in the file and change it to ENGINE=InnoDB. Then, load the schema file first and the data file afterwards.

UPDATE 2012-01-23 11:52 EDT

You should add this to to /etc/my.cnf

[mysqld]
default-storage-engine=InnoDB

then run service mysql restart. If you cannot restart mysql at this time, run this in the mysql client:

mysql> SET GLOBAL default_storage_engine = 'InnoDB';

However, the mysqldump will still have the ENGINE=MyISAM at the end of each CREATE TABLE. You will have to perform the 5-Step plan I mentioned above including changing the ENGINE=MyISAM to ENGINE=InnoDB. DO NOT CHANGE THAT FOR THE mysql DATABASE !!! Change only those tables that are yours.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply