[ZBX-16315] Sometimes Zabbix proxy cannot update configuration due to foreign key constraint failure Created: 2019 Jun 28  Updated: 2024 Apr 10  Resolved: 2019 Jul 04

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Proxy (P), Server (S)
Affects Version/s: None
Fix Version/s: 4.0.11rc1, 4.2.5rc1, 4.4.0alpha1, 4.4 (plan)

Type: Problem report Priority: Trivial
Reporter: Vladislavs Sokurenko Assignee: Vladislavs Sokurenko
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Team: Team A
Sprint: Sprint 53 (Jun 2019), Sprint 54 (Jul 2019)
Story Points: 0.125

 Description   

Steps to reproduce:
Add new hosts to Zabbix proxy when Zabbix proxy configuration preparation is in progress, if host is added just before issuing select query for items then items without host are sent, resulting in error below, same can happen with preprocessing in 4.2

Result:

[Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix_proxy`.`items`, CONSTRAINT `c_items_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE) [insert into items (itemid,type,snmp_community,snmp_oid,hostid,key_,delay,status,value_type,trapper_hosts,snmpv3_securityname,snmpv3_securitylevel,snmpv3_authpassphrase,snmpv3_privpassphrase,lastlogsize,logtimefmt,params,ipmi_sensor,authtype,username,password,publickey,privatekey,mtime,flags,interfaceid,port,snmpv3_authprotocol,snmpv3_privprotocol,snmpv3_contextname,jmx_endpoint,timeout,url,query_fields,posts,status_codes,follow_redirects,post_type,http_proxy,headers,retrieve_mode,request_method,output_format,ssl_cert_file,ssl_key_file,ssl_key_password,verify_peer,verify_host,allow_traps,description) values (28785,0,'','',10271,'net.if.discovery','1h',0,4,'','',0,'','',0,'','','',0,'','','','',0,1,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28786,0,'','',10271,'vfs.fs.discovery','1h',0,4,'','',0,'','',0,'','','',0,'','','','',0,1,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28787,0,'','',10271,'agent.hostname','1h',0,1,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28788,0,'','',10271,'agent.ping','1m',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28789,0,'','',10271,'agent.version','1h',0,1,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28790,0,'','',10271,'kernel.maxfiles','1h',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28791,0,'','',10271,'kernel.maxproc','1h',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28792,0,'','',10271,'proc.num[,,run]','1m',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28793,0,'','',10271,'proc.num[]','1m',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28794,0,'','',10271,'system.boottime','10m',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28795,0,'','',10271,'system.cpu.intr','1m',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28796,0,'','',10271,'system.cpu.load[percpu,avg15]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28797,0,'','',10271,'system.cpu.load[percpu,avg1]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28798,0,'','',10271,'system.cpu.load[percpu,avg5]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28799,0,'','',10271,'system.cpu.switches','1m',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28800,0,'','',10271,'system.cpu.util[,guest]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28801,0,'','',10271,'system.cpu.util[,guest_nice]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28802,0,'','',10271,'system.cpu.util[,idle]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28803,0,'','',10271,'system.cpu.util[,interrupt]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28804,0,'','',10271,'system.cpu.util[,iowait]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28805,0,'','',10271,'system.cpu.util[,nice]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28806,0,'','',10271,'system.cpu.util[,softirq]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28807,0,'','',10271,'system.cpu.util[,steal]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28808,0,'','',10271,'system.cpu.util[,system]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28809,0,'','',10271,'system.cpu.util[,user]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28810,0,'','',10271,'system.hostname','1h',0,1,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28811,0,'','',10271,'system.localtime','1m',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28812,0,'','',10271,'system.swap.size[,free]','1m',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28813,0,'','',10271,'system.swap.size[,pfree]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28814,0,'','',10271,'system.swap.size[,total]','1h',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28815,0,'','',10271,'system.uname','1h',0,1,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28816,0,'','',10271,'system.uptime','10m',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28817,0,'','',10271,'system.users.num','1m',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28818,0,'','',10271,'vfs.file.cksum[/etc/passwd]','1h',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28819,0,'','',10271,'vm.memory.size[available]','1m',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28820,0,'','',10271,'vm.memory.size[total]','1h',0,3,'','',0,'','',0,'','','',0,'','','','',0,0,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28821,0,'','',10271,'net.if.in[{#IFNAME}]','1m',0,3,'','',0,'','',0,'','','',0,'','','','',0,2,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28822,0,'','',10271,'net.if.out[{#IFNAME}]','1m',0,3,'','',0,'','',0,'','','',0,'','','','',0,2,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28823,0,'','',10271,'vfs.fs.inode[{#FSNAME},pfree]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,2,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28824,0,'','',10271,'vfs.fs.size[{#FSNAME},free]','1m',0,3,'','',0,'','',0,'','','',0,'','','','',0,2,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28825,0,'','',10271,'vfs.fs.size[{#FSNAME},pfree]','1m',0,0,'','',0,'','',0,'','','',0,'','','','',0,2,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28826,0,'','',10271,'vfs.fs.size[{#FSNAME},total]','1h',0,3,'','',0,'','',0,'','','',0,'','','','',0,2,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,''),(28827,0,'','',10271,'vfs.fs.size[{#FSNAME},used]','1m',0,3,'','',0,'','',0,'','','',0,'','','','',0,2,8,'',0,0,'','','3s','','','','200',1,0,'','',0,0,0,'','','',0,0,0,'');
]
  8243:20190628:103240.322 failed to update local proxy configuration copy: database error

Similar error reported on 4.2

8929:20190627:052604.744 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix_proxy`.`item_preproc`, CONSTRAINT `c_item_preproc_1` FOREIGN KEY (`itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE) [insert into item_preproc (item_preprocid,itemid,step,type,params,error_handler,error_handler_params) values


 Comments   
Comment by Vladislavs Sokurenko [ 2019 Jun 28 ]

Fixed in feature branch:
https://git.zabbix.com/projects/ZBX/repos/zabbix/pull-requests/220/overview

Comment by Glebs Ivanovskis [ 2019 Jun 28 ]

By the way, pull requests are not publicly visible.

Comment by Vladislavs Sokurenko [ 2019 Jun 28 ]

Have you logged in into Bitbucket ?

Comment by Glebs Ivanovskis [ 2019 Jun 28 ]

No. Using JIRA credentials gives "You do not have permission to access Bitbucket."

Comment by richlv [ 2019 Jun 28 ]

Same here, has been like that since the migration to git.

Comment by Vladislavs Sokurenko [ 2019 Jul 01 ]

We are working on this cyclone and richlv I will keep you updated.

Comment by Vladislavs Sokurenko [ 2019 Jul 01 ]

Fixed in:

  • pre-4.0.11rc1 80cf816b50c
  • pre-4.2.5rc1 4d6dbb6ff4d
  • pre-4.4.0alpha1 (trunk) 500e76e6b23
Generated at Wed Apr 24 04:33:43 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.