Percona use it or not, problems

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

I have been trying to implement Percona for some time, but without much success. I installed two servers S1 and S2, connected them, both are in enforcing mode, the idea for some future is that simultaneous writing can be done on S1 and S2 server. Unfortunately, I was not able to even take the step of entering data on only one server. Some applications that we use like Zabbix, don’t have tables with a primary key and simply Percona will not work with them. As far as I have seen, it is not recommended to change the operating mode, and this is for this application. I hope that there is a solution, otherwise we can forget to use Percona.

Errors for zabbix:
2021-11-22T12:44:39.893391Z 56545 [ERROR] WSREP: Percona-XtraDB-Cluster prohibits use of DML command on a table (zabbix.history) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER
2021-11-22T12:44:39.894436Z 56546 [ERROR] WSREP: Percona-XtraDB-Cluster prohibits use of DML command on a table (zabbix.history_uint) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER

1 Like
2

Hello @zakonuzemunu,
As you discovered, PXC in strict mode does not allow DML to run on tables without primary key. This is done for several reasons, the main one being less data to synchronize between nodes when replicating and another big reason is for handling conflicts when writing to multiple nodes at the same time.

You can relax this requirement by setting pxc_strict_mode=PERMISSIVE or DISABLED.

The proper solution is to simply add a PK to the tables. Something as simple as id int unsigned AUTO_INCREMENT will be just fine. Your application won’t use it, so it shouldn’t impact the app.

Also, 2 servers with PXC is not advised. You should always have an odd number of nodes. If one of your 2 goes down due to crash/power loss/network outage, then both nodes will be down since only 50% will remain. You must have > 50% to be online.

1 Like