[ZBX-21533] Error with duplicate values on tables "history_uint" columns "itemid", "clock", "ns". Created: 2022 Aug 30  Updated: 2022 Aug 31  Resolved: 2022 Aug 31

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Problem report Priority: Trivial
Reporter: Elvijs Bokanovs Assignee: Zabbix Support Team
Resolution: Won't fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

Problem described also in Zabbix forum - https://www.zabbix.com/forum/zabbix-help/450318-error-with-duplicate-values-on-tables-history_uint-columns-itemid-clock-ns .

 

Steps to reproduce:

  1. Perform upgrade on Zabbix server from 5.0 LTS to 6.0 LTS. Database engine is PostgreSQL.
  2. Execute SQL script "history_pk_prepare.sql".
  3. Reinsert data from table "history_uint_old" to table "history_uint", who has new multi column primary key "history_uint_pkey". 
  4. Problem also occurs when new data gets added from Zabbix Agent active item.
  5. To find duplicate values: 
    SELECT itemid, clock, ns, COUNT(*) FROM history_uint GROUP BY itemid, clock, ns HAVING COUNT(*) > 1 ORDER BY COUNT desc;
  6. Item who receives lot of data from host. That generates duplicate values: 
    logrt[C:\app\file.log,"Query speed:"]

    Item should receive from Zabbix Agent Active at least 100 values per second. So CPU can process at least 2 item values on same one nanosecond.
    Possibly item "log" also could reproduce issue.

Result:

INSERT INTO history_uint SELECT * FROM history_uint_old;
ERROR:  duplicate key value violates unique constraint "history_uint_pkey"
DETAIL:  Key (itemid, clock, ns)=(1442, 1657527245, 687061700) already exists.

Expected:
Successfully import all data from tables "history*_old" without indexes into newly created tables "history*" with indexes. 

Successfully adding new data from Zabbix Agent Active items to table "history_uint", also items whose data gets processed by CPU on same nano second.

 Comments   
Comment by Edgar Akhmetshin [ 2022 Aug 31 ]

Hello

https://www.zabbix.com/forum/zabbix-help/450318-error-with-duplicate-values-on-tables-history_uint-columns-itemid-clock-ns:

Tables "history*" are large (>500 GB), that's why before upgrade process I renamed them with suffix "_old_table" and created new empty tables with default naming and same structure. So upgrade process could be a lot faster. After successful upgrade tried to insert old data back with:

Code:

INSERT INTO history selec * from history_old_stable;

If insert old data without INSERT/IGNORE (etc) statement, why it should be inserted to PK enabled table? It will fail.

https://www.zabbix.com/documentation/6.0/en/manual/appendix/install/db_primary_keys

https://dev.mysql.com/doc/refman/8.0/en/insert.html

To find duplicate values:
SELECT itemid, clock, ns, COUNT FROM history_uint GROUP BY itemid, clock, ns HAVING COUNT > 1 ORDER BY COUNT desc;

It looks like you have mixed different schemas and table naming. Table with PK in our case will have no duplicates. If you want to use SQL like stated in forum post, find and remove all duplicates like this before re-uploading data.

Also LTS 6.0 has duplicate value processing and it will not stop Server or cause unrecoverable insert errors: ZBXNEXT-6921

Please be advised that this section of the tracker is for bug reports only. The case you have submitted can not be qualified as one, so please reach out to [email protected] for commercial support (https://zabbix.com/support) or consultancy services. Alternatively, you can also use our IRC channel or community forum (https://www.zabbix.com/forum) for assistance. With that said, we are closing this ticket. Thank you for understanding.

Regards,
Edgar

Generated at Sat May 31 03:16:22 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.