Ideas on how to troublehshoot "duplicate key value violates unique constraint ..."

[pablos]

Hi,

We have 121 hosts configured. We have two proxies. In our PostgreSQL and Zabbix logs, we see an extremely high rate of PK constraint violations due to dup key values - see a snippet below.

One thought I had is perhaps several VMs were cloned and the Hostname was not changed in the .conf file.

Any ideas on the best way to identify and resolve the problem? Perhaps enabling debug and checking the Hostname against the originating IP address (if that is possible)?

Thx!

449267:20250624:142725.266 [Z3008] query failed due to primary key constraint: [0] PGRES_FATAL_ERROR:ERROR:  duplicate key value violates unique constraint "history_pkey"
DETAIL:  Key (itemid, clock, ns)=(77421, 1750800441, 31258900) already exists.

449267:20250624:142725.272 skipped 5 duplicates
449267:20250624:142725.607 [Z3008] query failed due to primary key constraint: [0] PGRES_FATAL_ERROR:ERROR:  duplicate key value violates unique constraint "history_uint_pkey"
DETAIL:  Key (itemid, clock, ns)=(77302, 1750800442, 29236500) already exists.

449267:20250624:142725.611 skipped 5 duplicates

[Markku]

There is the item id in the messages. See the items, and find out, why they cause more than one value at the exact same timestamp (to the nanosecond).

Markku

[pablos]

There is the item id in the messages. See the items, and find out, why they cause more than one value at the exact same timestamp (to the nanosecond).

Markku

That is the question I am trying to solve: is it one host or several hosts that are sending the same value.

I will try enabling debug on the Zabbix Server. I was wondering if there were any other ideas.

[Markku]

The issue is clear according to the presented log, item values are not the problem. You have two items that have the same issue separately: they are trying to save values with timestamps (clock and ns) that are duplicates.

So go ahead and see what actual items are 77421 and 77302, what are they doing, and why is it possible that their data is duplicated timestamp-level.

Markku

[vad-valrc]

We have a similar issue and I'm wondering if usage of ProxyGroup with ServerActive could be the issue ?

We have a proxy group PG which contains P1 and P2. On the host agent we setup ServerActive=P1,P2 and I'm wondering if both proxies are not sending the item to the server.

[Markku]

We have a similar issue and I'm wondering if usage of ProxyGroup with ServerActive could be the issue ?

We have a proxy group PG which contains P1 and P2. On the host agent we setup ServerActive=P1,P2 and I'm wondering if both proxies are not sending the item to the server.

"ServerActive=P1,P2" is incorrect if P1 and P2 are in proxy group. It should be "ServerActive=P1;P2". (Edit: this is incorrect: But, the other proxy should reject the agent connection anyway.)

Edit: actually, https://majornetwork.net/2024/05/zab...oad-balancing/ :

Note: In the initial version of this post I used comma-separated proxy addresses in ServerActive (instead of semicolon-separated), and that caused duplicate connections from the agent to the designated proxy (because the agent is not equipped to recognize that it connects to the same proxy twice), eventually causing data duplication in Zabbix database. Using comma-separated proxy addresses is thus not a working solution for proxy load balancing usage.

So first fix the agent configurations, and then see if it helps right away in your case.

Markku

[vad-valrc]

Thank you for noticing this mistake. By fixing this I saw some agents had proxy1,proxy2,server in ServerActive which may be the reason of duplicate if it is send to proxy + server ?

[Markku]

Yes

Markku