I’m a Systems/Software Engineer in the San Francisco Bay Area. I moved from Columbus, Ohio in 2007 after getting a B.S. in Physics from the Ohio State University. I'm married, and we have dogs.

Under my github account (https://github.com/addumb): I open-sourced python-aliyun in 2014, I have an outdated python 2 project starter template at python-example, and I have a pretty handy “sshec2” command and some others in tools.

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.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 United States License. :wq