search

zabbix-tools / zabbix-pgsql-partitioning

Partitioning scripts for Zabbix on PostgreSQL
27 stars 11 forks source link

Events partitioning error with keys #7

Open alexmirtoff opened 6 years ago

alexmirtoff commented 6 years ago

If i am turning on partitionig of the events table, i am seeing an error in postgres logs:

2017-11-28 11:23:27 MSK zabbix zabbix ERROR:  insert or update on table "problem" violates foreign key constraint "c_problem_2"
2017-11-28 11:23:27 MSK zabbix zabbix DETAIL:  Key (r_eventid)=(67189991) is not present in table "events".
2017-11-28 11:23:27 MSK zabbix zabbix STATEMENT:  update problem set r_eventid=67189991,r_clock=1511857405,r_ns=231733137,userid=0 where eventid=67184930;

Help me, please.

cavaliercoder commented 6 years ago

Please, which version of Zabbix and PostgreSQL?

alexmirtoff commented 6 years ago

Sorry.

Zabbix 3.4.4 PostgreSQL 9.6.3

alexmirtoff commented 6 years ago

.. any ideas?

cavaliercoder commented 6 years ago

I apologise I'm a little time constrained to take a deeper look in the short term. I suspect the database schema has changed in Zabbix 3.4, meaning my scripts are no longer appropriate (they were written for 3.2).

darrynv commented 6 years ago

The issue is relating to 2 x constraints on the Problems Table when you try to partition the events table.

Namely, c_problem_1 and c_problem_2.

_1 references the eventid that generated the problem _2 references the eventid that recovered the problem

I come from a mysql backend. Could you point me in the right direction in manipulating the constraints to point to the event table partitioning? event_recovery_1_fk_trigger Function from your 3.2 fix seems to address this? partially...?

ghost commented 6 years ago

This morning, I applied the partitioning bootstrap to our 3.2.11 instance (not upgraded from previous versions) and get the same errors, e.g.: query failed: [0] PGRES_FATAL_ERROR:ERROR: insert or update on table "problem" violates foreign key constraint "c_problem_2" query failed: [0] PGRES_FATAL_ERROR:ERROR: insert or update on table "alerts" violates foreign key constraint "c_alerts_2" query failed: [0] PGRES_FATAL_ERROR:ERROR: insert or update on table "event_recovery" violates foreign key constraint "c_event_recovery_2"

Because there is a 'fix' script for when you upgrade to 3.2 from an earlier release, which also mentions these keys, I ran that as well, hoping to get rid of the errors, but unfortunately, it didn't.

So, right now, after running the bootstrap script and running the select statement to partition a few tables (trends, trends_uint, history, history_uint, history_text, history_str and history_log), I have a Zabbix 3.2 server that no longer shows what the current state is, what is a problem, what is no longer a problem and doesn't send any email because of this. It appears I have to rollback to unpartitioned tables to solve the problem.

cavaliercoder commented 6 years ago

This script hasn't had a meaningful update since Zabbix 3.2.7 (Aug 2017). It's possible the schema has changed since this time.

ghost commented 6 years ago

@cavaliercoder so there is no solution for me? I think that the problem I'm facing is also relevant for Zabbix 3.4 and it would be nice if you, or someone else, could find a solution for this.