Zabbix, MySQL 5.7 and Percona XtraDB Cluster

[lafdez]

Hi!

I trying to make a fresh install of Zabbix with its database in a Percona XtraDB MySQL Cluster with enforcing pxc_strict_mode with no success. When I did import the schema into the database I got this error:

ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (zabbix.dbversion) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER

This error shows up when the import tries to insert into dbversion table which does not have an explicit primary key.

Is there any way of installing Zabbix in that database without changing pxc_strict_mode?

Thanks in advance,

[Viks]

If you start a completely new installation,
I highly recommend PostgreSQL to you
and do not choose anything from MySQL variants.

After that, it will be very difficult to change, but issues and dissatisfaction will only increase.
(backups, maintenance, locking tables - freezed monitoring, lots of alerts, bad performance, etc.)

[dimir]

This is not true. Most of our users (all kinds, small, medium, enterprise) are on MySQL and I see no reason to say something like that.

As for this error, our dbversion table is very simple. It only has one record. That's why it has no primary key and this is what causing the error, if I understood correctly. We have 6 of 140 tables (in 4.0) that do not have primary keys: all history tables and dbversion. If you would like to stay in strict mode I guess you need to add primary keys (id with autoincrement) to these tables. If you plan to have big installation having primary key on history tables might affect Zabbix performance. Also if you modify DB schema manually there is no guarantee of clean upgrade from our side.

I'd recommend to check all Percona strict modes, perhaps there is something allowing to have tables without primary keys.

[steveroebuck]

We managed to get round this by manually inserting the DBversion into the database then Zabbix would go about its normal creation business, a word of warning tho from our experience having all nodes as masters doesnt work very well, so make sure you are running in an Active/Passive/Passive system.

You will also find that you need to add some primary keys to certain tables after the DB has been created to ensure that historical data is consistent.

alter table dbversion ADD id4galera INT PRIMARY KEY AUTO_INCREMENT;
alter table history add id4galera int key auto_increment;
alter table history drop primary key , add primary key (id4galera,clock);
alter table history_uint add id4galera int key auto_increment;
alter table history_uint drop primary key , add primary key (id4galera,clock);
alter table history_log add id4galera int key auto_increment;
alter table history_log drop primary key , add primary key (id4galera,clock);
alter table history_text add id4galera int key auto_increment;
alter table history_text drop primary key , add primary key (id4galera,clock);
alter table history_str add id4galera int key auto_increment;
alter table history_str drop primary key , add primary key (id4galera,clock)

[lafdez]

First of all, thank you very much for all your answers.

We decided to put the database in the same machine as the server and the web front-end. We thought it was the less risky approach.

dimir : To relax the strict modes was not an option because there were more other databases in that cluster and as far as I could read there could be some kind of problems if we did that. Besides I found (as steveroebuck pointed out in his answer) none of the history tables have primary keys.
steveroebuck : Unfortunately as far as I know it is a multi-master setup.

[steveroebuck]

lafdez Sounds like you've done the best thing then as running it on a multimaster didn't work out well for us we had constant transaction deadlocks and we were getting missing and gappy data. Running as Active/Passive/Passive for writing and Multimaster for reading seems to be working out ok.

[dimir]

I don't know about the targeted size of your installation but usually having Zabbix server, frontend and the database on the same machine in production is not what our users do. Unless you know what you are doing I recommend to have separate machines, especially for the database server. It is well known the database is the most critical point for Zabbix and usually it is recommended a separate and non-virtual machine for the database.