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

primary key constraint catastrophic failure on items table

XMLWordPrintable

    • Icon: Problem report Problem report
    • Resolution: Known Error
    • Icon: Trivial Trivial
    • None
    • 6.0.16, 6.4.2
    • Frontend (F), Server (S)
    • None
    • I'm running Zabbix 6.2.7 but this probably affects all current supported versions.
      Mysql 8.0.32

      Steps to reproduce:

      1. I run a program that sends LLDs and metrics to the trapper port (10051) as well as creating ad-hoc items. With a lot of host creates occurring in my code I encountered an error in the zabbix_server.log which was also visible from the Web frontend telling me I could not link a new template to an existing host or create a new host and link a template to it because there was a 'Duplicate entry [itemid] for key 'items.PRIMARY'.
      2. I was able to track this down to the ids table. It looked like the ids table for field items had a nextid of 2170018 but there was already an actual item created with itemid 2170019 and 2170024, and many more.
      3. This causes use to be unable to create any new items.
      4. You can easily reproduce the error by manually modifying the ids table nextid field for table_name items. Obviously, no one should be touching this table and my code doesn't, but to reproduce the error condition you can decrement the nextid value simply to recreate
      5. It seems that perhaps a high number of inserts and contention might cause this but I'm honestly not sure?

       

      Is it possible for zabbix server to detect when this happens and further increment the nextid in the ids table? Certainly, fixing the root cause is ideal, but this case doesn't seem to happen deterministically, so I'm not sure what else I can provide to assist. 

      To manually fix. I was able to get the most recent itemid by executing:

      select itemid from items order by itemid desc limit 1;

      Then take that id and run:

      update ids set nextid = "<itemid_from_above> where table_name = "items";

      and that fixes things.

       

            tbross Tomass Janis Bross
            GRyan337 Ryan Eberly
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: