duplicate table_name in ids table

XMLWordPrintable

    • Type: Incident report
    • Resolution: Unresolved
    • Priority: Trivial
    • None
    • Affects Version/s: 7.0.17
    • Component/s: Server (S)
    • Environment:
      Docker
      zabbix/zabbix-server-pgsql:ubuntu-7.0.16
      timescale/timescaledb:2.14.2-pg16
      zabbix/zabbix-web-nginx-pgsql:7.0.11-ol
      pgbouncer/pgbouncer:latest

      I recently realized some discovery items were not working, then I executed the Discovery rule and got the info "failed to get templates trigger data".
      After some debugging in the database, I realized, some table_names like hosts, items, host_tag, hosts_groups, hostmacro, hosts_templates, item_tag, trigger_tag, triggers were duplicated with different nextid.

      • how I noticed the error: 
      select * from ids order by 1;
      • I had to apply this delete  to get rid of the ids:
      WITH dedup AS (
    SELECT ctid,
           ROW_NUMBER() OVER (PARTITION BY table_name, field_name ORDER BY nextid DESC) AS rn
    FROM ids
)
DELETE FROM ids
WHERE ctid IN (
    SELECT ctid
    FROM dedup
    WHERE rn > 1
);
      • and manually update the nexid of the tables ...
      UPDATE ids
SET nextid = (SELECT COALESCE(MAX(hostid),0)+1 FROM hosts)
WHERE table_name='hosts';

UPDATE ids
SET nextid = (SELECT COALESCE(MAX(itemid),0)+1 FROM items)
WHERE table_name='items';

UPDATE ids
SET nextid = (SELECT COALESCE(MAX(hosttemplateid),0)+1 FROM hosts_templates)
WHERE table_name='hosts_templates';

UPDATE ids
SET nextid = (SELECT COALESCE(MAX(triggerid),0)+1 FROM triggers)
WHERE table_name='triggers';

      then, everything got back to normal

            Assignee:
            Zabbix Support Team
            Reporter:
            Wildes Hanada
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated: