MySQL Duplicate Key Error - InnoDB or MyISAM?

January 20, 2011

When bringing up a new MySQL slave, I did the ol' mysqldump route for a change. The command usually goes something like this:

master-host $ mysqldump -uroot -p --quick --compact --all-databases | gzip | \
 ssh -cblowfish slave-host "gunzip > dump.sql"
slave-host $ mysql > dump.sql

But I kept getting duplicate key errors on import! Graaaaaaah! They only seemed to happen when there were multi-byte characters in the varchar column... suspicious. Let's SHOW CREATE TABLE on both. Ah HA! Master is InnoDB, slave is MyISAM. Thanks, mysqldump. So... well the tables are not created using the same storage engine! mysqldump just decided that everything should be MyISAM which has a key length limitation different than InnoDB.

I'm by no means the first person to run into this, I'm just tossing it out there as well. See http://mmatemate.blogspot.com/2011/01/about-innodb-index-size-limitations.html for a similar adventure.