MySQL throwing the wrong error

2012-01-08 One-minute read

I was running a routine CiviCRM installation when I was stopped by the error:

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

The line referred to a table that created two indexes. Each index combined a 512 byte varchar field with a 4 byte integer field (well below 1000 bytes).

I then tried downloading the sql file to my local computer and it imported without a problem.

So… I started the painstaking process of comparing the configurations on each computer. Eventually, I came to this setting that was causing the problem:

innodb_log_file_size = 268440000

In fact, it wasn’t the setting causing the error. The error was caused because I added that setting without properly shutting down MySQL, removing /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1 files and then restarting MySQL.