[ZBX-22659] query failed due to primary key constraint: [1062] Duplicate entry '183334929' for key 'PRIMARY' Created: 2023 Apr 12  Updated: 2023 Jun 22  Resolved: 2023 Jun 22

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 6.0.16
Fix Version/s: None

Type: Problem report Priority: Trivial
Reporter: Marco Hofmann Assignee: Elina Kuzyutkina (Inactive)
Resolution: Won't fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Debian 11
Zabbix 6.2.7
MariaDB 10.8.7

Attachments: PNG File image-2023-04-12-10-55-54-223.png    

 Description   

Version is 6.2.7 was we haven't migrated to 6.4, yet.

Steps to reproduce:

  1. In early January, while still being on Zabbix 6.0, we dropped our history and applied the primary keys DB patch:
mysql -u root -p zabbix < /usr/share/zabbix-sql-scripts/mysql/history_pk_prepare.sql

This worked without any issues (we know of)

  1. On 10.02.2023 we upgraded to 6.2.7 without any issues (we know of)
  2. On 07.04.2023 we had a planned downtime in our datacenter. The Zabbix VM and DB were powered off gracefully and powered on a few hours later.
  3. Issues started to appear after the downtime.

Result:

Since then, many, but not all trigger events, don't close anymore. Our event problems list grows bigger every day.

Se the following examples of services that are of type 0 running, but the problem persists:

In the Zabbix Server log, we see the following error a lot, and I suspect a connection:

  1261:20230412:105626.256 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183364615' for key 'PRIMARY'
  1260:20230412:105626.258 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183455685' for key 'PRIMARY'
  1261:20230412:105630.278 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183491625' for key 'PRIMARY'
  1259:20230412:105630.281 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183909559' for key 'PRIMARY'
  1260:20230412:105631.287 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183458684' for key 'PRIMARY'
  1258:20230412:105631.289 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183618546' for key 'PRIMARY'
  1260:20230412:105634.304 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183493894' for key 'PRIMARY'
  1261:20230412:105635.306 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183386042' for key 'PRIMARY'
  1260:20230412:105637.316 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183780765' for key 'PRIMARY'
  1258:20230412:105637.325 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183722694' for key 'PRIMARY'
  1259:20230412:105639.320 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183479570' for key 'PRIMARY'
  1259:20230412:105639.322 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183491625' for key 'PRIMARY'
  1261:20230412:105640.332 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183707372' for key 'PRIMARY'
  1258:20230412:105642.344 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183465442' for key 'PRIMARY'
  1260:20230412:105644.351 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183894241' for key 'PRIMARY'

Expected:

Problem Events should close as normal, when the condition is met.

Notes:

The very first time the issue was in the logs was here, one day before the planned datacenter downtime:

14234:20230406:141103.642 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '183334929' for key 'PRIMARY'

Since then, this error has been logged thousands of times.

I know this might be much to ask, but the error message is not helpful at all. Could you please give us any hint, what to search for in the database, so we can start troubleshooting that issue?

 Comments   
Comment by Marco Hofmann [ 2023 Apr 26 ]

We partly solved that issue. We had millions of unrelated entries in our event table. So eventids were lingering in that table, that shouldn't be there, which prevented issues from being closed.

We executed the following query to prune that table from the biggest part of old entries, but not all unnecessary entries have been deleted.

DELETE FROM event_recovery WHERE eventid NOT IN (SELECT eventid FROM events);

DELETE FROM `event_recovery` WHERE `eventid` IS NOT NULL AND `r_eventid` NOT IN (SELECT `eventid` FROM `events` WHERE `value` = 0);

Manually: (DELETE FROM `event_recovery` WHERE eventid = <event ID>)

We still don't know where this comes from. But from all I can tell, it had nothing to do with the primary key change, nor the upgrade from 6.0 to 6.2. I'm not sure if it's related to ZBX-20148.

Comment by Ricardo Canani [ 2023 May 25 ]

Would it be related to ZBX-21533?

Comment by Elina Kuzyutkina (Inactive) [ 2023 Jun 22 ]

Hi,
there is nothing with ZBX-20148 (housekeeper won't delete opened problems) or ZBX-21533 (these tables aren't involved in events calculations)
Also there is no indication of a bug here - it can be consequence of some manual queries\backup restoring\replication problems and so on. As this project dedicated for a bugs tracking I am going to close this ticket
You can stop zabbix server, truncate ids table, start zabbix server
The error should gone. If that doesn't help please use our community (t.me/ZabbixTech or Forum) or commercial support services for further investigating

Regards, Elina

Comment by Marco Hofmann [ 2023 Jun 22 ]

elina.kuzyutkina But Housekeeper does delete from the events table? That's why I thought ZBX-20148 might be related?

Generated at Sat Apr 12 16:57:43 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.