[ZBX-14466] Duplicate entry 'XXXXXX' for key 'PRIMARY' [insert into event_recovery Created: 2018 Jun 13 Updated: 2020 Jul 28 Resolved: 2018 Oct 24 |
|
Status: | Closed |
Project: | ZABBIX BUGS AND ISSUES |
Component/s: | Server (S) |
Affects Version/s: | 3.4.10 |
Fix Version/s: | None |
Type: | Incident report | Priority: | Major |
Reporter: | Sascha Papini | Assignee: | Unassigned |
Resolution: | Fixed | Votes: | 1 |
Labels: | None | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified |
Attachments: |
![]() ![]() |
||||
Issue Links: |
|
Description |
In my zabbix_server.log I have a lot of error: 28308:20180613:072304.296 [Z3005] query failed: [1062] Duplicate entry '4797763' for key 'PRIMARY' [insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (4869248,4869375,null,null,null),(4858997,4869375,null,null,null),(4819277,4869375,null,null,null),(4798259,4869375,null,null,null),(4802518,4869375,null,null,null), I check our DB and in events i found this MariaDB [zabbix]> select * from events where eventid=4797763; +---------+--------+--------+----------+------------+-------+--------------+-----------+ | eventid | source | object | objectid | clock | value | acknowledged | ns | +---------+--------+--------+----------+------------+-------+--------------+-----------+ | 4797763 | 3 | 4 | 36183 | 1525482903 | 1 | 0 | 455074981 | +---------+--------+--------+----------+------------+-------+--------------+-----------+ The best way for resolve this issue is delete from table events the id 4797763 ? |
Comments |
Comment by Elina Kuzyutkina (Inactive) [ 2018 Jun 13 ] |
Hello, Sascha Restarting Zabbix server service should fix this problem. Please don't delete any records from events, they can have dependencies. Is the situation possible that the second instance of the Zabbix server was launched? Can you attach a Zabbix_server log file? Do you use one instance of database or you configured replication? Are there any third-party services or scripts working with the Zabbix database? Regards, Elina |
Comment by Sascha Papini [ 2018 Jun 13 ] |
Hi Elina, I restarted many times zabbix-server, but the error remained. For now I solved by putting the host with item 36183 disabled |
Comment by Elina Kuzyutkina (Inactive) [ 2018 Jun 13 ] |
Can you delete from event_recovery where eventid=4797763; Than enable item. |
Comment by Elina Kuzyutkina (Inactive) [ 2018 Jun 13 ] |
Sorry, just found log attached, give me some time to check it |
Comment by Sascha Papini [ 2018 Jun 13 ] |
No problem |
Comment by Elina Kuzyutkina (Inactive) [ 2018 Jun 13 ] |
Looks like you have some items collected by userparamers, for example bind.incounter. The script sometimes return an error and item became not supported, because Zabbix gets string instead of numeric. It will be a good idea to change userparameter to interpret errors in a specific number or forward script errors to devnull. Olso, you can delete records from event_recovery delete from event_recovery where eventid in ('4797699','4797705','4797754','4797763','4797768'); But the reason for the appearance of duplicates is unclear. So: Is the situation possible that the second instance of the Zabbix server was launched? Do you use one instance of database or you configured replication? Are there any third-party services or scripts working with the Zabbix database?
|
Comment by Florian Seidel [ 2018 Jul 18 ] |
I have the same issue in 3.4.11 after upgrading from 3.4.9. |
Comment by Aigars Kadikis [ 2018 Jul 30 ] |
I would like to add to Elinas comment one approach how to configure UserParameter so it will always retrieve some numeric value. This would be the way how to count zabbix processes: UserParameter=custom.key,pslist=$(ps -caux|grep [z]abbix) && echo ${pslist:-0} The classic variable is $pslist, but if the value is empty then it will get substituted with 0. F10, please tell more about you configuration: Regards, |
Comment by Florian Seidel [ 2018 Aug 09 ] |
I think the Problem was the last update. We have a distributed environment and 2 proxies were not stopped during the server upgrade. We started with a fresh database and imported all setting. After that everything is working fine and performance values of the database are much better. We do not have a HA setup for our DB in the moment. |
Comment by Christopher Beye [ 2018 Aug 16 ] |
Hi, I have the same problem. And get tons of errors in my server log: 59293:20180816:102804.451 [Z3005] query failed: [1062] Duplicate entry '17257' for key 'PRIMARY' [insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (17257,102313,null,null,null);
Is there any fix so far?
Regrads, Chris |
Comment by dimir [ 2018 Aug 20 ] |
UserParameter=custom.key,pslist=ps -caux|grep -c [z]abbix |
Comment by dimir [ 2018 Aug 20 ] |
|
Comment by Eric NOYELLE [ 2018 Aug 21 ] |
Hi, I have had the same issue, and I think it is due to the fact that I restored a backup (not full data, just configuration using zabbix-mysql-dump), and that I did many changes (removed hosts...). So the symptom is:
I am thinking that the restore process has restored values inside the event_recovery and problem tables that don't have any matching record in table events.
So I am wondering if it is safe to delete records from event_recovery and problem tables when they have no matching record in table events? Is there anything else to do? Here is a request I have tried to clean up the tables: delete from event_recovery where eventid not in (select eventid from events) or r_eventid not in (select eventid from events); delete from problem where eventid not in (select eventid from events) or r_eventid not in (select eventid from events);
Thanks in advance, Eric. |
Comment by Christopher Beye [ 2018 Aug 22 ] |
Hi, @ericn I did exactly the same. Moved to a new server and then the problems starts to begin... I deleted all entries in the following tables: Problems, Events & Event_recovery. It was not important for me Now it is solved Chris |
Comment by Eric NOYELLE [ 2018 Aug 28 ] |
Thank you Chris for your comment. So yes, I think the zabbix-mysql-dump script could be improved to avoid these issues when restoring. After removing the orpheans from the event_recovery and problem tables, I still had left aside events (in table events) showing as problems in the history... I deleted them as well. Now it's all clean, and Zabbix seems to work fine: It just triggered the same alert that had caused problems and when the cause of the trigger was solved, the problem went into resolved state, as expected.
Eric. |
Comment by Aigars Kadikis [ 2018 Oct 24 ] |
Thanks to community effort! Armed with this knowledge I will close the issue. |