[ZBX-5469] foreign key constraint fails Created: 2012 Aug 20  Updated: 2017 May 30  Resolved: 2012 Aug 22

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Proxy (P)
Affects Version/s: 2.0.2
Fix Version/s: 2.0.3rc1, 2.1.0

Type: Incident report Priority: Major
Reporter: Stephan A. Klein Assignee: dimir
Resolution: Fixed Votes: 0
Labels: database, dm
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Debian Squeeze 64bit, MySQL 5.1.63-0+squeeze1 (Debian), Multi-Node Setup, but only one node active


Attachments: File Zabbix Proxy-Log.rtf     File zabbix-2.0-ZBX-5469.patch     Text File zabbix_proxy.log    

 Description   

Webchecks which should run on the zabbix-proxy are not synchronised from the master zabbix server. The proxy-log states therse errors:

12587:20120820:162144.527 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix_proxy`.`httpstep`, CONSTRAINT `c_httpstep_1` FOREIGN KEY (`httptestid`) REFERENCES `httptest` (`httptestid`
update httpstep set httptestid=100100000000011,name='Startseite',no=1,url='http://www.SITE1.biz',timeout=15,posts='',required='Copyright',status_codes='200' where httpstepid=100100000000020;
update httpstep set httptestid=100100000000013,name='SITE 2',no=1,url='http://www.SITE2.de/',timeout=15,posts='',required='Kosten',status_codes='200' where httpstepid=100100000000022;
insert into httpstep (httpstepid,httptestid,name,no,url,timeout,posts,required,status_codes) values (100100000000023,100100000000014,'Startseite DE',1,'http://www.SITE3.de/',15,'','Impressum','200');

... and a few more for additional sites.

12587:20120820:162145.673 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix_proxy`.`httpstepitem`, CONSTRAINT `c_httpstepitem_1` FOREIGN KEY (`httpstepid`) REFERENCES `httpstep` (`htt
update httpstepitem set httpstepid=100100000000019,itemid=100100000024856,type=1 where httpstepitemid=100100000000089;
update httpstepitem set httpstepid=100100000000019,itemid=100100000024857,type=0 where httpstepitemid=100100000000090;
update httpstepitem set httpstepid=100100000000020,itemid=100100000024861,type=2 where httpstepitemid=100100000000091;
update httpstepitem set httpstepid=100100000000020,itemid=100100000024862,type=1 where httpstepitemid=100100000000092;
update httpstepitem set httpstepid=100100000000020,itemid=100100000024863,type=0 where httpstepitemid=100100000000093;
update httpstepitem set httpstepid=100100000000022,itemid=100100000024873,type=2 where httpstepitemid=100100000000097;
update httpstepitem set httpstepid=100100000000022,itemid=100100000024874,type=1 where httpstepitemid=100100000000098;
update httpstepitem set httpstepid=100100000000022,itemid=100100000024875,type=0 where httpstepitemid=100100000000099;
insert into httpstepitem (httpstepitemid,httpstepid,itemid,type) values (100100000000100,100100000000023,100100000024879,2);
insert into httpstepitem (httpstepitemid,httpstepid,itemid,type) values (100100000000101,100100000000023,100100000024880,1);
insert into httpstepitem (httpstepitemid,httpstepid,itemid,type) values (100100000000102,100100000000023,100100000024881,0);
insert into httpstepitem (httpstepitemid,httpstepid,itemid,type) values (100100000000103,100100000000024,100100000024885,2);
insert into httpstepitem (httpstepitemid,httpstepid,itemid,type) values (100100000000104,100100000000024,100100000024886,1);
insert into httpstepitem (httpstepitemid,httpstepid,itemid,type) values (100100000000105,100100000000024,100100000024887,0);

... and a few more.

New checks added on the server are not populated to the proxy so the newly added webchecks stay in an unknown state.

I've tried to delete the proxy database, rebuild a new databse with schema.sql (nothing else) from 2.0.2 package, but the error remains.

If you need further information, please contact me.



 Comments   
Comment by dimir [ 2012 Aug 21 ]

Could you please attach the whole proxy log?

Comment by Stephan A. Klein [ 2012 Aug 21 ]

Please find attached the proxy-log after a proxy restart.

Comment by dimir [ 2012 Aug 22 ]

Thanks, but this file seems to be modified by some rtf editor or something. Could you instead send the real unmodified log?

Comment by dimir [ 2012 Aug 22 ]

Seems that the issue is when one host contains 2 applications and this information is sent to proxy. The unique applications index (hostid,name) fails because we do not send the host name (which ends up empty).

Comment by Stephan A. Klein [ 2012 Aug 22 ]

OK, here is the plain vanilla log, sorry for rtf'ing it.

Could you please clarify how to avoid the error condition? Would it be sufficient to go back to only one application for the webchecks run on the proxy?

Comment by Stephan A. Klein [ 2012 Aug 22 ]

Just deleted the second application, the error seems to be gone

Comment by dimir [ 2012 Aug 22 ]

Right, I could reproduce it easily with 2 applications and 2 web monitoring items. This will be fixed by adding host "name" to the list of things to send when synchronizing "applications".

Comment by dimir [ 2012 Aug 22 ]

Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-5469 .

Comment by dimir [ 2012 Aug 22 ]

If you would like to try the fix you could take development branch from SVN or apply the patch to your sources tree and then you should be able to use different applications with one host.

In order to test your own source tree with the patch:

  • go to zabbix sources directory
  • $ patch p0 < /path/to/zabbix-2.0ZBX-5469.patch
  • $ make clean
  • $ make dbschema
  • $ make install

Only the server needs to be rebuilt. Then clear proxy database and restart server and proxy.

Comment by Alexander Vladishev [ 2012 Aug 23 ]

Successfully tested!

Comment by dimir [ 2012 Aug 23 ]

Fixed in pre-2.0.3 r29762, pre-2.1.0 r29763.

The fix enables possibility to use more than one application in proxy web monitoring for single host.

Details:

Table "applications" has foreign key "hostid"-"name". When sending
application to proxy server was sending only hostid which resulted in
empty application name for every entry. So for 2 applications of same
host the foreign key would look the same:

12345-
12345-

which foreign key constraint would not allow to save.

The fix is to enable sending application name to proxy by adding
ZBX_PROXY flag to "applications"."name" field.

Before this fix:

  • an error would occur when using more than one application in proxy
    web monitoring for single host

After this fix:

  • more than one application is supported when using proxy web
    monitoring for single host
Generated at Fri Mar 29 08:18:27 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.