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