Discovery action based host removal fails on triggers created by item discovery

[wano]

There is a discovery based action, that removes registered hosts.
I the host has a trigger, that was created by item discovery, the removal fails with:
[Z3005] query failed: [1451] Cannot delete or update a parent row: a foreign key constraint fails (`zabbix`.`trigger_discovery`, CONSTRAINT `c_trigger_discovery_2` FOREIGN KEY (`parent_triggerid`) REFERENCES `triggers` (`triggerid`)) [delete from triggers where (triggerid between 22917 and 22921 or triggerid in (22934,22935,22936));

The code tries to delete the triggers "triggerid between 22917 and 22921". As there is trigger_discovery table with foreign key to triggers table, the lookup of dependent records is being performed and "or triggerid in (22934,22935,22936)" clause is added.
But it seems, that mysql does not understand that the resulting combination will remove all the necessary triggers.
If the added clause is executed seperately, the the original statement succeeds:
delete from triggers where triggerid in (22934,22935,22936);
delete from triggers where (triggerid between 22917 and 22921 or triggerid in (22934,22935,22936));

Also if the parent triggerid is already in the original list, no clause is added, thus requiring a manual query to determine the failing triggerid:
select * from trigger_discovery where parent_triggerid between 22215 and 22220;
delete from triggers where triggerid=22220;
delete from triggers where triggerid between 22215 and 22220;

The dbversion is 2040000.
Zabbix 2.4.7rc1 (revision 55827).

What am I missing here for the host removal to work?

[sqlbloke]

It is because the cascading delete has not been set correctly

This is the current table that causes the error.

CREATE TABLE graph_discovery (graphid bigint unsigned NOT NULL, parent_graphid bigint unsigned NOT NULL,
PRIMARY KEY (graphid),
CONSTRAINT c_graph_discovery_2 FOREIGN KEY (parent_graphid) REFERENCES graphs (graphid),
CONSTRAINT c_graph_discovery_1 FOREIGN KEY (graphid) REFERENCES graphs (graphid) ON DELETE CASCADE,
INDEX graph_discovery_1 (parent_graphid)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We need to drop the offending constraint

ALTER TABLE profiles DROP FOREIGN KEY `c_graph_discovery_2 `

We need to add the new constraint in which duplicates the old constraint with the ON DELETE CASCADE

ALTER TABLE profiles
ADD CONSTRAINT `c_graph_discovery_2 `
FOREIGN KEY (parent_graphid)
REFERENCES graphs (graphid)
ON DELETE CASCADE

[glebs.ivanovskis]

Issue already fixed: https://support.zabbix.com/browse/ZBX-9950

[sqlbloke]

Not fixed in 2.4 source that you download to install

That is why I replied with how to fix it

[glebs.ivanovskis]

Changing database schema is not the best idea and can lead to more unexpected errors in the future.

[sqlbloke]

Sorry

But that is the correct solution as that is the correct error.

If you haven't gathered by my name sql servers wether MS. MySQL, postgress are my business.

So don't tell me what not to do when what I stated was the correct thing to do.
I upgraded over the weekend to 3.0 no ill effects from my db change so pull your head in.

Bryan

[sqlbloke]

Learn to be thank full to people replying to posts

I noticed that you didn't point the person to the fix even though he put his question there last year and didn't start replying to the post till someone had a solution that was the correct solution.