Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-21533

Error with duplicate values on tables "history_uint" columns "itemid", "clock", "ns".

    XMLWordPrintable

Details

    • Problem report
    • Status: Closed
    • Trivial
    • Resolution: Won't fix
    • None
    • None
    • None
    • None

    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.

      Attachments

        Activity

          People

            zabbix.support Zabbix Support Team
            teab Elvijs Bokanovs
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: