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)
  • 1
    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, 2011 at 18:12
  • @DrColossos: Isn't the engine type defined in the dump file? Nothing has been changed in the import/export. Apr 11, 2011 at 20:02

1 Answer 1


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).

  • 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. Apr 12, 2011 at 7:41

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