zabbix-proxy 2.0.1: Cannot delete or update a parent row: a foreign key constraint fa

[sergeyarl]

Recently we have upgraded to zabbix 2.0.1. Everything is working well except for zabbix proxy setup.
We are getting the following error in zabbix proxy log:

 
 15062:20120703:160808.651 Received configuration data from server. Datalen 4767
 15062:20120703:160809.079 [Z3005] query failed: [1451] Cannot delete or update a parent row: a foreign key constraint fails (`zabbix`.`opgroup`, CONSTRAINT `c_opgroup_2` FOREIGN KEY (`groupid`) REFERENCES `groups` (`groupid`)) [delete from applications where applicationid in (1,5,7,9,13,15,17,21,23,179,206,207,214,227,228,229,230,231,232,234,235,236,237,238,240,241,242,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,319,320,322,323,324,325,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355);
delete from groups where groupid in (1,2,4);
delete from expressions where expressionid between 1 and 4;
delete from regexps where regexpid between 1 and 3;
delete from dchecks where dcheckid=2;
delete from drules where druleid=2;
delete from interface where interfaceid=1;
delete from hosts where hostid in (10001,10047,10050,10056,10060,10065,10066,10067,10068,10069,10070,10071,10072,10073,10074,10075,10076,10077,10078,10079,10081,10082,10083,10084);
delete from globalmacro where globalmacroid=2;

then after zabbix proxy restart the error message changes:

15171:20120703:161027.615 [Z3005] query failed: [1451] Cannot delete or update a parent row: a foreign key constraint fails (`zabbix`.`opgroup`, CONSTRAINT `c_opgroup_2` FOREIGN KEY (`groupid`) REFERENCES `groups` (`groupid`)) [delete from groups where groupid in (1,2,4);
delete from expressions where expressionid between 1 and 4;
delete from regexps where regexpid between 1 and 3;
delete from dchecks where dcheckid=2;
delete from drules where druleid=2;
delete from interface where interfaceid=1;
delete from hosts where hostid in (10001,10047,10050,10056,10060,10065,10066,10067,10068,10069,10070,10071,10072,10073,10074,10075,10076,10077,10078,10079,10081,10082,10083,10084);
delete from globalmacro where globalmacroid=2;
]
 15171:20120703:161027.615 failed to update local proxy cofiguration copy: database error

We have a fresh zabbix proxy instance. The database was created with the following commands:

# mysql zabbix < /usr/share/zabbix/database/mysql/schema.sql
# mysql zabbix < /usr/share/zabbix/database/mysql/images.sql
# mysql zabbix < /usr/share/zabbix/database/mysql/data.sql

Version of zabbix server, proxy and agents is 2.0.1.

I found similar error reports on the forum, but didn't see any reasonable solution.

Does anybody know what can cause this issue?

[rodom77]

I'm having a similar issue that I've been working on quite sometime... for some reason though, my binaries are still reporting as version 1.8.10 from Dec, 28th 2011 and this is after compiling from the new 2.0.1 tgz.

The DB errors I'm getting are:

 30959:20120703:203003.653 [Z3005] query failed: [1054] Unknown column 'i.snmp_port' in 'field list' [select i.itemid,i.hostid,h.proxy_hostid,i.type,i.data_type,i.value_type,i.key_,i.snmp_community,i.snmp_oid,i.snmp_port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authpassphrase,i.snmpv3_privpassphrase,i.ipmi_sensor,i.delay,i.delay_flex,i.trapper_hosts,i.logtimefmt,i.params,i.status,i.authtype,i.username,i.password,i.publickey,i.privatekey from items i,hosts h where i.hostid=h.hostid and h.status in (0) and i.status in (0,3) and i.itemid between 000000000000000 and 099999999999999]
 30959:20120703:203003.654 [Z3005] query failed: [1054] Unknown column 'useip' in 'field list' [select hostid,proxy_hostid,host,useip,ip,dns,port,useipmi,ipmi_ip,ipmi_port,ipmi_authtype,ipmi_privilege,ipmi_username,ipmi_password,maintenance_status,maintenance_type,maintenance_from,errors_from,available,disable_until,snmp_errors_from,snmp_available,snmp_disable_until,ipmi_errors_from,ipmi_available,ipmi_disable_until,status from hosts where status in (0,5,6) and hostid between 000000000000000 and 099999999999999]

I've been slowly fixing issues as I go but the binaries remaining the last version is puzzling me. Then hopefully I can fix the DB issues...

[groe0286]

I'm having a similar issue that I've been working on quite sometime... for some reason though, my binaries are still reporting as version 1.8.10 from Dec, 28th 2011 and this is after compiling from the new 2.0.1 tgz.

The DB errors I'm getting are:

 30959:20120703:203003.653 [Z3005] query failed: [1054] Unknown column 'i.snmp_port' in 'field list' [select i.itemid,i.hostid,h.proxy_hostid,i.type,i.data_type,i.value_type,i.key_,i.snmp_community,i.snmp_oid,i.snmp_port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authpassphrase,i.snmpv3_privpassphrase,i.ipmi_sensor,i.delay,i.delay_flex,i.trapper_hosts,i.logtimefmt,i.params,i.status,i.authtype,i.username,i.password,i.publickey,i.privatekey from items i,hosts h where i.hostid=h.hostid and h.status in (0) and i.status in (0,3) and i.itemid between 000000000000000 and 099999999999999]
 30959:20120703:203003.654 [Z3005] query failed: [1054] Unknown column 'useip' in 'field list' [select hostid,proxy_hostid,host,useip,ip,dns,port,useipmi,ipmi_ip,ipmi_port,ipmi_authtype,ipmi_privilege,ipmi_username,ipmi_password,maintenance_status,maintenance_type,maintenance_from,errors_from,available,disable_until,snmp_errors_from,snmp_available,snmp_disable_until,ipmi_errors_from,ipmi_available,ipmi_disable_until,status from hosts where status in (0,5,6) and hostid between 000000000000000 and 099999999999999]

I've been slowly fixing issues as I go but the binaries remaining the last version is puzzling me. Then hopefully I can fix the DB issues...

That's actually a simple one. You still have the old binaries on the system, and it's actually running those!

Do a:
[root@test]# zabbix_server --version
[root@test]# which zabbix_server

If it says something like /usr/sbin/zabbix_server or /usr/bin/zabbix_server then don't forget to overwrite those binaries, as the make install will not have done so, if you've specified a --prefix option in ./configure.

Also, you can have a look in /etc/init.d/zabbix_server to see which binary is being started.

Also, you can do:
[root@test]# yum -y install mlocate
[root@test]# updatedb
[root@test]# locate zabbix_server

to find all your zabbix_server binaries.


Ohhh, so many options, but only 1 conclusion, you're running the old binary on the new database. That's BAD! Restore a backup of the database as irreversible changes may have been made to your database!!!

[sergeyarl]

Unfortunately this doesn't solve my issue. All my binaries report version 2.0.1. So this is not a version related problem.

[Udo]

# mysql zabbix < /usr/share/zabbix/database/mysql/schema.sql
# mysql zabbix < /usr/share/zabbix/database/mysql/images.sql
# mysql zabbix < /usr/share/zabbix/database/mysql/data.sql

Please cross-verify the installation documentation. If I remember correctly only schema should get installed on a proxy.


Udo

[rodom77]

@groe0286, Thanks! That helped point me in the right direction. My compiled version were correct, it was the fact that an older version of the binaries was in a PATH location. Fixed that and viola.. all is well. Thanks again!

[sergeyarl]

# mysql zabbix < /usr/share/zabbix/database/mysql/schema.sql
# mysql zabbix < /usr/share/zabbix/database/mysql/images.sql
# mysql zabbix < /usr/share/zabbix/database/mysql/data.sql

Please cross-verify the installation documentation. If I remember correctly only schema should get installed on a proxy.


Udo

Thank you very much. That seems to solve my problem. I should have read the documentation more carefully.

[groe0286]

Unfortunately this doesn't solve my issue. All my binaries report version 2.0.1. So this is not a version related problem.

I was responding to rodom77's post. ;-)

[extress]

Thanks for the tips, I banged my head against a wall