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.

with percona xtradb, mysqldump can create inconsistent ddl missing auto increment for example!

When running Percona's XtraDB cluster and using pxc_strict_mode=ENFORCNG (the default) lock tables will generate an error.

mysqldump defaults to --opt which includes --lock-tables wherin the error gets thrown.  Of course you can temporarily change pxc_strict_mode to DISABLED or PERMISSIVE to get around the error however there can be side affects of this in a live system.

A better approach is to use mysqldump without --opt by specifying --no-opt however this can generate incorrect DDL because of the embedded option (in --opt) of --create-options.  The safe method if you want to use --no-opt is to specify the options individually and omit the --lock-tables implied by --opt.

As shown by help 

--opt               Same as --add-drop-table, --add-locks, --create-options,

                      --quick, --extended-insert, --lock-tables, --set-charset,

                      and --disable-keys. Enabled by default, disable with

                      --skip-opt.

So omit --lock-tables and include the others and your ddl will be consistent.

Color me unhappy when I copied a schema from one pxc cluster to another and missed this so that my copied schema omitted auto increment and automatic update of timestamp.  

Have fun.