Sunday, January 6, 2019

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.

No comments:

Post a Comment