kodai, has this happened more than once? It looks like someone could have created an item for the host that is being deleted during the discovery rule run. Could that have happened?

I will check. Please give me some time.

Sure, also, do you know if the host, that is being deleted contains lld rule?

This happens with 2.4.5?

kodai Yes, it happened with 2.4.5

Do you know if discovered host is linked to a template with lld rule?

kodai Yes, the discovery action assign Template OS Linux to discovered hosts include LLD rules

I was able to reproduce the error with the discovered host that is linked to a template with lld rule that has item prototypes.

The scenario

• a template called "test" that contains one discovery rule with type Zabbix trapper and one item prototype (doesn't matter how is it configured)
• 2 network discovery rules both discover the same IP address, named:
  • drule_add
  • drule_delete
• 2 discovery actions
  • one with condition Discovery rule = drule_add and action Link to templates: test
  • another with condition Discovery rule = drule_delete and action Remove host
• edit file src/libs/zbxdbhigh/host.c and in function DBdelete_hosts() between lines DBselect_uint64(sql, &itemids); and DBdelete_items(&itemids); put sleep(10);
• recompile zabbix_server

Steps to reproduce

• start zabbix server
• in one terminal execute command tail -n 10000 -F /tmp/zabbix_server.log | egrep --color=always 'op_host_del|constraint|insert into items |insert into hosts |delete from hosts ' and watch the output
• wait for line In op_host_del(), e. g.:

   11494:20150610:170013.706 query [txnlev:1] [insert into hosts (hostid,proxy_hostid,host,name,description) values (10387,null,'dimir.zabbix.lan','dimir.zabbix.lan','')]
   11494:20150610:170013.715 query [txnlev:1] [insert into items (itemid,name,key_,hostid,type,value_type,data_type,delay,delay_flex,history,trends,status,trapper_hosts,units,multiplier,delta,formula,logtimefmt,valuemapid,params,ipmi_sensor,snmp_community,snmp_oid,snmpv3_securityname,snmpv3_securitylevel,snmpv3_authprotocol,snmpv3_authpassphrase,snmpv3_privprotocol,snmpv3_privpassphrase,authtype,username,password,publickey,privatekey,templateid,flags,description,inventory_link,interfaceid,lifetime,snmpv3_contextname,evaltype,port) values (23634,'Filesystem discovery','vfs.fs.discovery',10387,2,4,0,0,'',90,365,0,'','',0,0,'','',null,'','','','','',0,0,'',0,'',0,'','','','',23328,1,'',0,null,'0','',0,''),(23635,'vfs.fs.size[$1,$2]','vfs.fs.size[{#FSNAME},pfree]',10387,0,0,0,10,'',90,365,0,'','%',0,0,'1','',null,'','','','','',0,0,'',0,'',0,'','','','',23329,2,'',0,138,'30','',0,'');
   11494:20150610:170022.834 In op_host_del()

• now open another terminal, you have 10 seconds to send lld data to zabbix trapper using zabbix_sender
• next line should appear in the first terminal, where you executed tail:

   11486:20150610:170024.253 query [txnlev:1] [insert into items (itemid,name,key_,hostid,type,value_type,data_type,delay,delay_flex,history,trends,status,trapper_hosts,units,multiplier,delta,formula,logtimefmt,valuemapid,params,ipmi_sensor,snmp_community,snmp_oid,port,snmpv3_securityname,snmpv3_securitylevel,snmpv3_authprotocol,snmpv3_authpassphrase,snmpv3_privprotocol,snmpv3_privpassphrase,authtype,username,password,publickey,privatekey,description,interfaceid,flags,snmpv3_contextname) values (23636,'vfs.fs.size[$1,$2]','vfs.fs.size[/,pfree]',10387,0,0,0,10,'',90,365,0,'','%',0,0,'1','',null,'','','','','','',0,0,'',0,'',0,'','','','','',138,4,''),(23637,'vfs.fs.size[$1,$2]','vfs.fs.size[/home,pfree]',10387,0,0,0,10,'',90,365,0,'','%',0,0,'1','',null,'','','','','','',0,0,'',0,'',0,'','','','','',138,4,'');

  Note the different PID!

• now when 10 seconds is finished an SQL error should appear:

   11494:20150610:170032.841 query [txnlev:1] [delete from hosts where hostid=10387]
   11494:20150610:170032.842 [Z3005] query failed: [1451] Cannot delete or update a parent row: a foreign key constraint fails (`dimir_zbx_9623`.`items`, CONSTRAINT `c_items_4` FOREIGN KEY (`interfaceid`) REFERENCES `interface` (`interfaceid`)) [delete from hosts where hostid=10387]

The complete example of error output (pid:11494 - network discoverer, pid:11486 - trapper):

 11494:20150610:170013.706 query [txnlev:1] [insert into hosts (hostid,proxy_hostid,host,name,description) values (10387,null,'dimir.zabbix.lan','dimir.zabbix.lan','')]
[...]
 11494:20150610:170013.715 query [txnlev:1] [insert into items (itemid,name,key_,hostid,type,value_type,data_type,delay,delay_flex,history,trends,status,trapper_hosts,units,multiplier,delta,formula,logtimefmt,valuemapid,params,ipmi_sensor,snmp_community,snmp_oid,snmpv3_securityname,snmpv3_securitylevel,snmpv3_authprotocol,snmpv3_authpassphrase,snmpv3_privprotocol,snmpv3_privpassphrase,authtype,username,password,publickey,privatekey,templateid,flags,description,inventory_link,interfaceid,lifetime,snmpv3_contextname,evaltype,port) values (23634,'Filesystem discovery','vfs.fs.discovery',10387,2,4,0,0,'',90,365,0,'','',0,0,'','',null,'','','','','',0,0,'',0,'',0,'','','','',23328,1,'',0,null,'0','',0,''),(23635,'vfs.fs.size[$1,$2]','vfs.fs.size[{#FSNAME},pfree]',10387,0,0,0,10,'',90,365,0,'','%',0,0,'1','',null,'','','','','',0,0,'',0,'',0,'','','','',23329,2,'',0,138,'30','',0,'');
[...]
 11494:20150610:170022.834 In op_host_del()
[...]
 11486:20150610:170024.253 query [txnlev:1] [insert into items (itemid,name,key_,hostid,type,value_type,data_type,delay,delay_flex,history,trends,status,trapper_hosts,units,multiplier,delta,formula,logtimefmt,valuemapid,params,ipmi_sensor,snmp_community,snmp_oid,port,snmpv3_securityname,snmpv3_securitylevel,snmpv3_authprotocol,snmpv3_authpassphrase,snmpv3_privprotocol,snmpv3_privpassphrase,authtype,username,password,publickey,privatekey,description,interfaceid,flags,snmpv3_contextname) values (23636,'vfs.fs.size[$1,$2]','vfs.fs.size[/,pfree]',10387,0,0,0,10,'',90,365,0,'','%',0,0,'1','',null,'','','','','','',0,0,'',0,'',0,'','','','','',138,4,''),(23637,'vfs.fs.size[$1,$2]','vfs.fs.size[/home,pfree]',10387,0,0,0,10,'',90,365,0,'','%',0,0,'1','',null,'','','','','','',0,0,'',0,'',0,'','','','','',138,4,'');
[...]
 11494:20150610:170032.841 query [txnlev:1] [delete from hosts where hostid=10387]
[...]
 11494:20150610:170032.842 [Z3005] query failed: [1451] Cannot delete or update a parent row: a foreign key constraint fails (`dimir_zbx_9623`.`items`, CONSTRAINT `c_items_4` FOREIGN KEY (`interfaceid`) REFERENCES `interface` (`interfaceid`)) [delete from hosts where hostid=10387]

Fix for 2.0 available in development branch: svn://svn.zabbix.com/branches/dev/ZBX-9623

This was fixed by introducing locking of a database record of host ID that is being handled by a process that deletes a host from the database and process that handles LLD rule of the same host.

Successfully tested, please review small changes in r54082

Could you please test fix for 2.2: svn://svn.zabbix.com/branches/dev/ZBX-9623-2.2

wiper looks good.

Fixed in pre-2.0.15rc1 r54100, pre-2.2.10rc1 r54123, pre-2.4.6rc1 r54124, pre-2.5.0 r54128.