Unable to delete host with 2M trash items

[PortalKiller]

This is mikrotik`s capsman monitored by snmp and somehow interface discovery created so much items for probably every cap re-connection, luckily they disabled and not collecting any data.

I have tried delete just items but item list from web not loading, long wait and after timeout error. Tried delete host via api, same thing, timeouts but after i increased timeouts, send reguest and postgres db was killed by oom after several hours))
Also tried to solve the problem head-on, simply deleting the items themselves from the database, but because of the relations, it's not that easy to do. And that's what I can't understand.
So there is any other ways to remove properly or this trash items or whole host?

[Viktors Fomics]

Hello

Makes sense to remove the dependant DB entries first, but there will be relatively a lot of SQL DELETEs. In the hosts table you can find the hostID that you will need, after that replace the '12345' in the below SQL DELETEs with your hostID. And yes, stop the Zabbix server process before running all those DELETEs.

For the item dependencies:
DELETE FROM history WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM history_uint WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM history_str WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM history_text WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM history_log WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM trends WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM trends_uint WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM item_discovery WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM triggers WHERE triggerid IN (SELECT triggerid FROM functions WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345));
DELETE FROM functions WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM graphs_items WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);

The item itself deletion:
DELETE FROM items WHERE hostid=12345;


If you want to delete the host too:

The host metadata related entry deletion:
DELETE FROM host_discovery WHERE hostid=12345;
DELETE FROM hosts_groups WHERE hostid=12345;
DELETE FROM hosts_templates WHERE hostid=12345;
DELETE FROM interface WHERE hostid=12345;
DELETE FROM host_inventory WHERE hostid=12345;

Deleting the host itself:
DELETE FROM hosts WHERE hostid=12345;

[PortalKiller]

Thanks, i will try it!!

[Viktors Fomics]

Hello

Makes sense to remove the dependant DB entries first, but there will be relatively a lot of SQL DELETEs. In the hosts table you can find the hostID that you will need, after that replace the '12345' in the below SQL DELETEs with your hostID. And yes, stop the Zabbix server process before running all those DELETEs.

For the item dependencies:
DELETE FROM history WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM history_uint WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM history_str WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM history_text WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM history_log WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM trends WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM trends_uint WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM item_discovery WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM triggers WHERE triggerid IN (SELECT triggerid FROM functions WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345));
DELETE FROM functions WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM graphs_items WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);

The item itself deletion:
DELETE FROM items WHERE hostid=12345;


If you want to delete the host too:

The host metadata related entry deletion:
DELETE FROM host_discovery WHERE hostid=12345;
DELETE FROM hosts_groups WHERE hostid=12345;
DELETE FROM hosts_templates WHERE hostid=12345;
DELETE FROM interface WHERE hostid=12345;
DELETE FROM host_inventory WHERE hostid=12345;

Deleting the host itself:
DELETE FROM hosts WHERE hostid=12345;

[PortalKiller]

Thanks, i will try it!!

[PortalKiller]

Hello

Makes sense to remove the dependant DB entries first, but there will be relatively a lot of SQL DELETEs. In the hosts table you can find the hostID that you will need, after that replace the '12345' in the below SQL DELETEs with your hostID. And yes, stop the Zabbix server process before running all those DELETEs.

For the item dependencies:
DELETE FROM history WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM history_uint WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM history_str WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM history_text WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM history_log WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM trends WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM trends_uint WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM item_discovery WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM triggers WHERE triggerid IN (SELECT triggerid FROM functions WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345));
DELETE FROM functions WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);
DELETE FROM graphs_items WHERE itemid IN (SELECT itemid FROM items WHERE hostid=12345);

The item itself deletion:
DELETE FROM items WHERE hostid=12345;


If you want to delete the host too:

The host metadata related entry deletion:
DELETE FROM host_discovery WHERE hostid=12345;
DELETE FROM hosts_groups WHERE hostid=12345;
DELETE FROM hosts_templates WHERE hostid=12345;
DELETE FROM interface WHERE hostid=12345;
DELETE FROM host_inventory WHERE hostid=12345;

Deleting the host itself:
DELETE FROM hosts WHERE hostid=12345;

Thank you for that commands, trashy items succesfully deleted!!

To make these commands more usable, delete from "triggers" should be after deleting "fucntions" and there are 2 more references "item_preproc" and "item_tag":


DELETE FROM history_str WHERE itemid IN (SELECT itemid FROM items WHERE hostid=11111);
DELETE FROM history_text WHERE itemid IN (SELECT itemid FROM items WHERE hostid=11111);
DELETE FROM history_log WHERE itemid IN (SELECT itemid FROM items WHERE hostid=11111);
DELETE FROM trends WHERE itemid IN (SELECT itemid FROM items WHERE hostid=11111);
DELETE FROM trends_uint WHERE itemid IN (SELECT itemid FROM items WHERE hostid=11111);
DELETE FROM item_discovery WHERE itemid IN (SELECT itemid FROM items WHERE hostid=11111);
DELETE FROM functions WHERE itemid IN (SELECT itemid FROM items WHERE hostid=11111);
DELETE FROM triggers WHERE triggerid IN (SELECT triggerid FROM functions WHERE itemid IN (SELECT itemid FROM items WHERE hostid=11111));
DELETE FROM graphs_items WHERE itemid IN (SELECT itemid FROM items WHERE hostid=11111);
DELETE FROM item_preproc WHERE itemid IN (SELECT itemid FROM items WHERE hostid=11111);
DELETE FROM item_tag WHERE itemid IN (SELECT itemid FROM items WHERE hostid=11111);

and only after that all host items can be deleted

DELETE FROM items WHERE hostid=11111;