0

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.

Improve this question
4
  • Have you checked the storage engine of your table example.example? You can use SHOW CREATE TABLE example.example; or SELECT engine FROM information_schema.tables WHERE table_schema='example' AND table_name='example';
    – Bill Karwin
    Apr 19, 2021 at 13:19
  • Interesting! Thank you. The tables in this database use MyISAM for some reason. I expected all databases and tables to be using InnoDB. I assume I would have to try converting the affected tables to InnoDB prior to migrating them.
    – gecko
    Apr 19, 2021 at 13:23
  • The error message you posted above says it allows ALTER TABLE if the alteration is to change the table's storage engine to InnoDB: ALTER TABLE example.example ENGINE=InnoDB;
    – Bill Karwin
    Apr 19, 2021 at 13:40
  • Thank you. Altering the offending tables to use InnoDB prior to dumping the database did the trick.
    – gecko
    Apr 20, 2021 at 5:22

1 Answer 1

Reset to default
0

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

Improve this answer
1
  • Thank you for your input. Whilst Plan B did not work, as explained in my original post, it caused the error to change to complaints about DML, Plan C worked perfectly. albeit possibly involved risks.
    – gecko
    Apr 20, 2021 at 5:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.