create table duplicate key! What?

I ran into an interesting and informative edge case involving asynchronous replication in MySQL and Percona's pt-online-schema_change utility.

In our 5.7 MySQL environment we were adding columns to an 18M 500G table.  Unfortunately this not only copies the table to complete the alter it also blocks the world from writing.  So we had to come up with another way to manage this change.  We have used Percona's pt-osc utility before and have great praise for it.  Our setup involved a PXC cluster and a single standalone slave.

pt-osc creates a new table _table_new with foreign keys renamed to _old_name, e.g., an underscore is added to make the fk in the new table unique.  But, we had previously run a pt-osc on this table and had swapped the old/new table successfully so we did not check that those fk names had been changed.  When we ran a new pt-osc it replicated through to the slave and failed with duplicate key on the create table ddl.

The fix was simple; drop and recreate the foreign keys without the leading _ (use set foreign_key_checks=NO to prevent scanning on the fk rebuild).  Worked like a charm and we resumed the slave.

I confess that it was not simple to figure this out and thankfully Percona was around to help me.