Zabbix on InnoDB cluster - Tables without primary key

[phil570]

Hello,

After I saw the announcement for InnoDB cluster support, I decided to give it a try as we just installed a new InnoDB cluster in my company and we already have some apps running on it.

I'm trying to install Zabbix on a MySQL 8.0 InnoDB cluster, but I'm running into some issues.

My infrastructure will be set up like that :

- One Zabbix server 5.0 LTS with his DB on MySQL 8.0 InnoDB cluster
- Zabbix proxies (same version) for each subnet with their DB installed locally
- MySQL router installed on Zabbix server

When I first tried to execute the script for initializing the DB as suggested in the documentation (with the command zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -h 127.0.0.1 -P 6446 -u zabbix -p zabbix), I ran into the error "The table does not comply by the requirements of an external plugin".

After somme googling, I found out that this error may be caused by some tables that don't have primary key which is not supported by InnoDB cluster.

I found this subject related to the same issue but the solution to this problem was not completely clear for me : https://www.zabbix.com/forum/zabbix-...uster-database

I tried many modifications of the SQL script create.sql.gz and after some failures, I managed to get Zabbix working by doing these steps :

- Adding the column "mandatory" as primary key for the table dbverion
- Adding a new column named "id" bigint unsigned not null auto_increment as primary key for the tables history, history_uint, history_str, history_log and history_test.

Zabbix is now working, but I'm not sure that this is the right way to get it working on MySQL InnoDB cluster and it would be great if someone could confirm that the modifications done on the script create.sql.gz below are correct :

[...]


CREATE TABLE `history` (
`id` bigint unsigned NOT NULL auto_increment,
`itemid` bigint unsigned NOT NULL,
`clock` integer DEFAULT '0' NOT NULL,
`value` DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
`ns` integer DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE INDEX `history_1` ON `history` (`itemid`,`clock`);

CREATE TABLE `history_uint` (
`id` bigint unsigned NOT NULL auto_increment,
`itemid` bigint unsigned NOT NULL,
`clock` integer DEFAULT '0' NOT NULL,
`value` bigint unsigned DEFAULT '0' NOT NULL,
`ns` integer DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE INDEX `history_uint_1` ON `history_uint` (`itemid`,`clock`);

CREATE TABLE `history_str` (
`id` bigint unsigned NOT NULL auto_increment,
`itemid` bigint unsigned NOT NULL,
`clock` integer DEFAULT '0' NOT NULL,
`value` varchar(255) DEFAULT '' NOT NULL,
`ns` integer DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE INDEX `history_str_1` ON `history_str` (`itemid`,`clock`);

CREATE TABLE `history_log` (
`id` bigint unsigned NOT NULL auto_increment,
`itemid` bigint unsigned NOT NULL,
`clock` integer DEFAULT '0' NOT NULL,
`timestamp` integer DEFAULT '0' NOT NULL,
`source` varchar(64) DEFAULT '' NOT NULL,
`severity` integer DEFAULT '0' NOT NULL,
`value` text NOT NULL,
`logeventid` integer DEFAULT '0' NOT NULL,
`ns` integer DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE INDEX `history_log_1` ON `history_log` (`itemid`,`clock`);

CREATE TABLE `history_text` (
`id` bigint unsigned NOT NULL auto_increment,
`itemid` bigint unsigned NOT NULL,
`clock` integer DEFAULT '0' NOT NULL,
`value` text NOT NULL,
`ns` integer DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE INDEX `history_text_1` ON `history_text` (`itemid`,`clock`);


[...]


CREATE TABLE `dbversion` (
`mandatory` integer DEFAULT '0' NOT NULL,
`optional` integer DEFAULT '0' NOT NULL,
PRIMARY KEY (mandatory)
) ENGINE=InnoDB;


[...]

For those who run into the same issue, I hope this will help

UPDATE :

Forgot to mention that I also tried to add a column named "id" as primary key for the table dbversion but ended up with an error 'column count does not match value count' when the script ran the query INSERT INTO dbversion VALUES ('5000000','5000002');

That's why I decided to use the "mandatory" column as primary key for the table dbversion.

Kind regards,

Phil

[dimir]

There's also this video (and a blog post made out of it) from Zabbix Summit 2019 that may be useful: https://blog.zabbix.com/scaling-zabb...-cluster/8472/

[phil570]

Hi dimir,

Thanks for your reply and sorry for my late answer.

The doc you provided was pretty useful, my Zabbix server is now working on InnoDB cluster (with the modifications detailed in my first post).