Partitioning a zabbix mysql with Perl

[midildi]

Hello everyone! I tried making partitions using this instruction: (Partitioning a Zabbix MySQL(8) database with Perl or Stored Procedures - Zabbix Blog) because I am having issues with database query failed (found the error from logs).

After manually partitioning it (up to Feb 29, 2024). Data started showing in Zabbix GUI (the graph are now showing).

I planned to use crontab and perl for automatic partitining but encountered problem running the command: perl /usr/share/zabbix/mysql_zbx_part.pl
This is the error that I get:

$ perl /usr/share/zabbix/mysql_zbx_part.pl
DBD::mysql::db do failed: VALUES LESS THAN value must be strictly increasing for each partition at /usr/share/zabbix/mysql_zbx_part.pl line 126.
DBD::mysql::db do failed: VALUES LESS THAN value must be strictly increasing for each partition at /usr/share/zabbix/mysql_zbx_part.pl line 126.

Here's the script I used: zabbix-mysql-partitioning-perl/mysql_zbx_part.pl at main · OpensourceICTSolutions/zabbix-mysql-partitioning-perl · GitHub

I did not alter any scripts after line 32 (just copied and paste them)
I use MariaDB 10.5.23 and Zabbix 6.0.25


Did anyone encountered error like this? hope you can help me.. I'm not much a programmer so I am not sure what's the problem when looking at the perl script file.

Thank you!!

[larcorba]

Hi Midildi

Could you execute the following MySQL queries?

use zabbix;
show create table history;

It looks like your initial partitioning of the tables isn't done correctly. That's why the scripts throws the error.

[surfrock66]

I am getting this as well, but I'm not sure what's wrong? I believe my tables are correctly partioned?

| history | CREATE TABLE `history` (
  `itemid` bigint unsigned NOT NULL,
  `clock` int NOT NULL DEFAULT '0',
  `value` double NOT NULL DEFAULT '0',
  `ns` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`itemid`,`clock`,`ns`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY RANGE (`clock`)
(PARTITION p2025_03_20 VALUES LESS THAN (1742540400) ENGINE = InnoDB,
 PARTITION p2025_03_21 VALUES LESS THAN (1742626800) ENGINE = InnoDB,
 PARTITION p2025_03_22 VALUES LESS THAN (1742713200) ENGINE = InnoDB,
 PARTITION p2025_03_23 VALUES LESS THAN (1742799600) ENGINE = InnoDB,
 PARTITION p2025_03_24 VALUES LESS THAN (1742886000) ENGINE = InnoDB,
 PARTITION p2025_03_25 VALUES LESS THAN (1742972400) ENGINE = InnoDB,
 PARTITION p2025_03_26 VALUES LESS THAN (1743058800) ENGINE = InnoDB,
 PARTITION p2025_03_27 VALUES LESS THAN (1743145200) ENGINE = InnoDB,
 PARTITION p2025_03_28 VALUES LESS THAN (1743231600) ENGINE = InnoDB,
 PARTITION p2025_03_29 VALUES LESS THAN (1743318000) ENGINE = InnoDB,
 PARTITION p2025_03_30 VALUES LESS THAN (1743404400) ENGINE = InnoDB,
 PARTITION p2025_03_31 VALUES LESS THAN (1743490800) ENGINE = InnoDB,
 PARTITION p2025_04_01 VALUES LESS THAN (1743577200) ENGINE = InnoDB,
 PARTITION p2025_04_02 VALUES LESS THAN (1743663600) ENGINE = InnoDB,
 PARTITION p2025_04_03 VALUES LESS THAN (1743750000) ENGINE = InnoDB,
 PARTITION p2025_04_04 VALUES LESS THAN (1743836400) ENGINE = InnoDB,
 PARTITION p2025_04_05 VALUES LESS THAN (1743922800) ENGINE = InnoDB,
 PARTITION p2025_04_06 VALUES LESS THAN (1744009200) ENGINE = InnoDB,
 PARTITION p2025_04_07 VALUES LESS THAN (1744095600) ENGINE = InnoDB) */ |

[PavelZ]

That's right. Don't forget that there are another history tables and it need partitioned.
Don't forget that the script needs to be run by cron every day.

[surfrock66]

I got burned by doing an upgrade and not remembering to upgrade the partitioning script; my mysql instance went to 8.0 some time long ago, and there's a bunch of stuff you need to uncomment for it to work, so my partitioning got way out of whack and I didn't notice