I have a production Percona Xtradb Cluster 5.7 running with PXC_Strict_Mode = Enforcing which is giving me some headaches at the time to import SQL dumps that were taken using PhpMyadmin.
Main issue is that PhpMyadmin is not adding the primary key in the CREATE TABLE statement, instead it is adding the key at the end of the export file. It seems that change in the structure of the export was introduced by Phpmyadmin developers back in 2014. Now, since primary keys and indexes are defined at the end of the export our imports are failing due to the enforcing mode which does not allow the creation of tables without the PK.
This is a big problem for us since our customers mainly use phpmyadmin and it is the only product we offer them to manage their databases. So, I wondering if someone knows if there is workaround to this situation. Disable pxc_strict is not an option since we have more than 2000 customers running on this cluster and some of them execute bad queries that affect the replication.
Thanks in advance for any information that you could provide!!
Ideally, Phpmyadmin maintainers should add proper PXC support.
As a workaround, you may always craft a script that would edit the SQL file and move all INSERT statements after index creation.
But best would be to just use other tools, like mysqldump or mydumper. For the first one though, the LOCK TABLE statements are also not allowed by PXC strict mode, so you need to use --skip-add-locks to it. An example command for PXC would be:
mysqldump --single-transaction --master-data --skip-add-locks --routines --triggers --all-databases > dump.sql
You may try to transform your backup with https://dbsake.readthedocs.io/en/latest/commands/sieve.html, but I haven’t tried that with phpmyadmin made backups.
Thanks for your update and recommendation. We are already modifying some of the dumps to make them compatible with Percona, but obviously this is a task that takes some time and customers some times don’t understand the underlying issues that we are having with PhpMyadmin.
We are also using other tools such as HeidiSql and mysqldump which are working pretty well, but again I just was looking to see if there was another workaround.