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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

When trying to export and import an 58G database yields the following error:

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

Dump created using mysqldump -q --opt and import is just cat bigfile.sql|mysql ...

MySQL versions:

  • Import: 5.1.49-1ubuntu8.1
  • Export: 5.1.41-3ubuntu12.10-log (Ubuntu)
share|improve this question
Are you changing from MyISAM to InnoDB or vice versa? Or the collation? If so, check the limits on any char constraints on the schema (like varchar(X), they are calculated differently – DrColossos Apr 11 '11 at 18:12
@DrColossos: Isn't the engine type defined in the dump file? Nothing has been changed in the import/export. – jonasl Apr 11 '11 at 20:02

This sounds like a character set conversion issue to me, the clue being bytes rather than characters. Are you going from ASCII to a multibyte charset, e.g. Unicode? Check the target database, check your environment (NLS_ env vars), check the dumpfile itself (e.g. if you were using UTF8 in the source that's Unicode but not multibyte if you only used the lower code page).

share|improve this answer
Sounds like you're on the right track! I actually tried to import the SQL-dump into a remote system (not local) and it worked. I will try to change the env and see if it works. – jonasl Apr 12 '11 at 7:41

Your Answer


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.