[ZBX-5305] foreign key constraint fails when proxy tries to delete groups Created: 2012 Jul 11  Updated: 2017 May 30  Resolved: 2012 Jul 17

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Proxy (P)
Affects Version/s: 2.0.0, 2.0.1
Fix Version/s: None

Type: Incident report Priority: Major
Reporter: Wesley Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

centos 5.8 X64
apache+PHP+mysql(version: 5.0.95 )

Issue Links:
Duplicate

 Description   

proxy server log

4239:20120711:134729.555 Received configuration data from server. Datalen 45156
4239:20120711:134729.655 [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 dchecks where dcheckid=2;
delete from drules where druleid=2;
delete from interface where interfaceid=1;
delete from hosts where hostid in (10047,10056,10060,10065,10066,10067,10068,10069,10070,10071,10072,10073,10074,10075,10076,10077,10078,10079,10081,10082,10083,10084);
]
4239:20120711:134729.655 failed to update local proxy cofiguration copy: database error

and add zabbix_agentd server must restart proxy or clear the database resumed after the introduction.

 Comments   
Comment by richlv [ 2012 Jul 11 ]

what are exact proxy and server versions ?
is this a clean install of the proxy or an upgrade from 1.8 ?
if it is a clean install, what exact commands were used to create the database ?

Comment by Wesley [ 2012 Jul 12 ]

versions：2.0.1
not is update,is new install.
I don't think that is in the database.

Comment by Oleksii Zagorskyi [ 2012 Jul 12 ]

I have the same error (with some differences):

17937:20120711:161230.042 [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,5,3003000000000007);
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 in (1,1001000000032130,1001000000032136,1001000000032235,1001000000168903,1001000000343188,1001000000343189);
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,1001000000010710,1001000000010712,1001000000010745,1001000000011463,1001000000011570,1001000000011595,1001000000056301,1001000000114396);
delete from globalmacro where globalmacroid=2;
]
17937:20120711:161230.042 failed to update local proxy cofiguration copy: database error

<zalex>
Probably I had the DB with data. Recreating with only DB schema solved the problem.

CLOSED

Comment by Oleksii Zagorskyi [ 2012 Jul 12 ]

(1) note also a typo in the message - "cofiguration", missing N letter.

Comment by Oleksii Zagorskyi [ 2012 Jul 12 ]

Show us please also an output from this SQL **on the proxy db**:
SQL> SELECT * FROM opgroup;

Are you sure that you have created **only** DB **schema** as described in documentation?
http://www.zabbix.com/documentation/2.0/manual/appendix/install/db_scripts#scripts

Comment by dimir [ 2012 Jul 16 ]

jack.lee, please reply to the last comment or we will assume the case closed.

Comment by Wesley [ 2012 Jul 17 ]

Please tell me how to operate?
I confirm that I only into the several database scripts.
================================
mysql> use zabbix
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM opgroup;
-----------------------------

opgroupid operationid groupid

-----------------------------

1 2 2

-----------------------------
1 row in set (0.00 sec)
==============================
mysql> show variables like 'collation_%';
---------------------------------------+

Variable_name Value

---------------------------------------+

collation_connection latin1_swedish_ci
collation_database utf8_general_ci
collation_server latin1_swedish_ci

---------------------------------------+
3 rows in set (0.03 sec)

mysql> show variables like 'character_set_%';
----------------------------------------------------+

Variable_name Value

----------------------------------------------------+

character_set_client latin1
character_set_connection latin1
character_set_database utf8
character_set_filesystem binary
character_set_results latin1
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/

----------------------------------------------------+
8 rows in set (0.00 sec)

mysql>

Comment by Oleksii Zagorskyi [ 2012 Jul 17 ]

jack.lee, drop your database *on proxies* and recreate it according to documentation:
http://www.zabbix.com/documentation/2.0/manual/appendix/install/db_scripts

Then the table "opgroup" (and others) will be empty, as they must be for proxies !!!

Issue CLOSED.

Comment by dimir [ 2012 Jul 17 ]

Oleksiy, the typo will be fixed in ZBXNEXT-1268.

Comment by dimir [ 2012 Jul 17 ]

jack.lee, when you create proxy database only schema should be loaded, the data must not be loaded.

Generated at Mon Jul 21 07:42:40 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.