MySQL – Specified key was too long; max key length is 1000 bytes

Posted in mySQL on February 22nd, 2011 by admin – Be the first to comment

Chances are if you’ve worked with mySQL for a while, you’ve probably ran into Error #1071 or ‘Specified key was too long; max key length is 1000 bytes’, There seems to be a high degree of confusion regarding this error message, I’ve typically ran into this error while exporting/importing databases from different systems. i.e I’ve taken a development mySQL dump and tried importing it into our staging mySQL server.

This error is typically a relatively easy fix and unfortunately this resolution is not easy to find.

Stop mySQL

service mysqld stop or /etc/init.d/mysqld stop

Change into default mySQL data path (usually: /var/lib/mysql).

cd /var/lib/mysql

Delete both ib_logfiles (ib_logfile0 & ib_logfile1 etc).

rm -f ib_logfile*

Now simply start up mySQL and the issue should be resolved.