Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
but how could that be possible since all i did was to dump the db and then trying to restore it, thus to my understanding the varchar length was enough, BTW, im using UTF8 –  Ran Jan 23 '12 at 16:01
    
Did you switch storage engines (from MyISAM to InnoDB)? Did you dump from one DB from an old version of MySQL and attempt to restore the dump into MySQL 5.5? –  RolandoMySQLDBA Jan 23 '12 at 16:04
    
nope, same engine (innoDB) to same version (5.1) –  Ran Jan 23 '12 at 16:07
    
found the problem, i was mistaken, it created the new tables with myisam instead of innodb!! what can i do to make sure the newly created tables will be innodb? –  Ran Jan 23 '12 at 16:37
    
I'll update my answer to address that. –  RolandoMySQLDBA Jan 23 '12 at 16:38
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.