All we need is an easy explanation of the problem, so here it is.
I installed a Percona XtraDB cluster with pxc_strict_mode set to ENFORCING, and am trying to import databases from non-Galera MariaDB servers. This works fine for some databases, by dumping them via mysqldump --skip-locks -K and importing them via mysql <database> < <dumpfile> (after creating a blank database using CREATE DATABASE).
One database however is refused the import:
ERROR 1105 (HY000) at line 40: Percona-XtraDB-Cluster prohibits use of ALTER command on a table (example.example) that resides in non-transactional storage engine (except switching to transactional engine) with pxc_strict_mode = ENFORCING or MASTER
Most internet threads simply suggest temporarily disabling pxc_strict_mode. Given however that other databases import without any issues, I assume the issue should be resolvable by altering either the the flags appended to mysqldump, or modifying the statements inside the dumpfile. I attempted removing all ALTER statements in the dump, which changes the above error to ...prohibits the use of DML command....
Both, the source MariaDB instance, as well as the new XtraDB Cluster, are configured with the default InnoDB storage engine.
Would appreciate any input.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
The Cluster can properly handle only InnoDB tables. It sounds like some of your tables have ENGINE=MyISAM?
Plan A: Edit the dump file to change to ENGINE=InnoDB for each table.
Plan B: Remove the offending ALTERs; load the data (assuming the tables successfully convert to InnoDB); then manually run the lost ALTERs.
Plan C: ALTER TABLE t ENGINE=InnoDB; for each table before dumping.
Each has potential hiccups. Some are listed here:
http://mysql.rjweb.org/doc.php/myisam2innodb
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0