Cannot delete proxy - SQL statement execution has failed

[Starko]

We are using Zabbix 3.4.8 on Debian Stretch with about 29 Proxys I guess. We are slowly migration from passive Proxys with IPsec VPNs to Active Proxys with TLS-PSK. This Zabbix Installation is online since Zabbix 2.0.
While migrating the Proxys from Passive to Active I hit one of the oldest Proxys. This one has been in use since ever. I first stumbled on this error, when I tried to switch from passive to active:

https://i.imgur.com/kKtTM8C.png

Interface is linked to item "Maximum number of opened files" on "".

So I searched for a while but eventually gave up and created the Proxy a second time with Active TLS-PSK. I switched the hosts and everything works just fine. But as I tried to delete the old instance, I now faced a new problem which must be related to the first problem I guess:

https://i.imgur.com/TjhAZlk.png

Error in query [DELETE FROM interface WHERE hostid='10085'] [Cannot delete or update a parent row: a foreign key constraint fails (`zabbix`.`items`, CONSTRAINT `c_items_4` FOREIGN KEY (`interfaceid`) REFERENCES `interface` (`interfaceid`))]
SQL statement execution has failed "DELETE FROM interface WHERE hostid='10085'"

Can anybody help me with this, or is this interesting enough to be considered for a bug report?

[Starko]

Hello everybody! Sorry for the double post, but I just wanted to say, that by now I have the second "old" proxy I can't delete. My Zabbix Server is version 3.4.12 today.

Cannot delete proxy
Details[LIST][*]Error in query [DELETE FROM interface WHERE hostid='10745'] [Cannot delete or update a parent row: a foreign key constraint fails (`zabbix`.`items`, CONSTRAINT `c_items_4` FOREIGN KEY (`interfaceid`) REFERENCES `interface` (`interfaceid`))][*]SQL statement execution has failed "DELETE FROM interface WHERE hostid='10745'"[/LIST]

Can anybody help me?

[zux]

the proxy is probably linked to some hosts, can you try this?
select hostid, proxy_hostid, host, status from hosts where hostid=10085;

and also, identify the hostid of the proxies you can't delete and then do this:
select hostid, proxy_hostid, host, status from hosts where proxy_hostid=<proxy_hostid>;

you can easily identify the hostid of proxy, by looking at the link, when you click on that proxy

[Starko]

Found the solution via ZBX-15082 and the help of a friend.
The following statement shows you the orphaned items:

SELECT itemid,hostid,key_ FROM items WHERE interfaceid IN (SELECT interfaceid FROM interface WHERE hostid=10085)

Which showed me this:

+--------+--------+-------------------------------+
| itemid | hostid | key_                          |
+--------+--------+-------------------------------+
|  24866 |  10085 | kernel.maxfiles               |
|  24867 |  10085 | kernel.maxproc                |
|  24868 |  10085 | net.if.discovery              |
|  24869 |  10085 | net.if.in[{#IFNAME}]          |
|  24870 |  10085 | net.if.out[{#IFNAME}]         |
|  24871 |  10085 | proc.num[,,run]               |
|  24872 |  10085 | proc.num[]                    |
|  24873 |  10085 | system.boottime               |
|  24874 |  10085 | system.cpu.intr               |
|  24875 |  10085 | system.cpu.load[percpu,avg15] |
|  24876 |  10085 | system.cpu.load[percpu,avg1]  |
|  24877 |  10085 | system.cpu.load[percpu,avg5]  |
|  24878 |  10085 | system.cpu.switches           |
|  24879 |  10085 | system.cpu.util[,idle]        |
|  24880 |  10085 | system.cpu.util[,interrupt]   |
|  24881 |  10085 | system.cpu.util[,iowait]      |
|  24882 |  10085 | system.cpu.util[,nice]        |
|  24883 |  10085 | system.cpu.util[,softirq]     |
|  24884 |  10085 | system.cpu.util[,steal]       |
|  24885 |  10085 | system.cpu.util[,system]      |
|  24886 |  10085 | system.cpu.util[,user]        |
|  24887 |  10085 | system.hostname               |
|  24888 |  10085 | system.localtime              |
|  24889 |  10085 | system.swap.size[,free]       |
|  24890 |  10085 | system.swap.size[,pfree]      |
|  24891 |  10085 | system.swap.size[,total]      |
|  24892 |  10085 | system.uname                  |
|  24893 |  10085 | system.uptime                 |
|  24894 |  10085 | system.users.num              |
|  24895 |  10085 | vfs.file.cksum[/etc/passwd]   |
|  24896 |  10085 | vfs.fs.discovery              |
|  24897 |  10085 | vfs.fs.inode[{#FSNAME},pfree] |
|  24898 |  10085 | vfs.fs.size[{#FSNAME},free]   |
|  24899 |  10085 | vfs.fs.size[{#FSNAME},pfree]  |
|  24900 |  10085 | vfs.fs.size[{#FSNAME},total]  |
|  24901 |  10085 | vfs.fs.size[{#FSNAME},used]   |
|  24902 |  10085 | vm.memory.size[available]     |
|  24903 |  10085 | vm.memory.size[total]         |
|  48304 |  10085 | icmppingloss[]                |
|  48305 |  10085 | icmppingsec[]                 |
|  48306 |  10085 | icmpping[]                    |
|  48753 |  10085 | net.tcp.service[tcp,,1494]    |
+--------+--------+-------------------------------+
42 rows in set (0.00 sec)

So I could delete it like this:

DELETE FROM `zabbix`.`items` WHERE `itemid` IN (24866,24867,24868,24869,24870,24871,24872,24873,24874,24875,24876,24877,24878,24879,24880,24881,24882,24883,24884,24885,24886,24887,24888,24889,24890,24891,24892,24893,24894,24895,24896,24897,24898,24899,24900,24901,24902,24903,48304,48305,48306,48753);