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