Trivial 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