Cardinality violation: 1242 Subquery returns more than 1 row after deactivation of host object

[tegios]

Hello

Expected Behavior Deactivate host in GUI

Current Behavior After deactivating a host (manually in the GUI) the director check shows the following warning

SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row, query was: SELECT COUNT(*) FROM director_activity_log WHERE id > COALESCE(( SELECT id FROM director_activity_log WHERE checksum = ( SELECT last_activity_checksum FROM director_generated_config WHERE checksum = ( SELECT config_checksum FROM director_deployment_log ORDER by id desc limit 1 ) )), 0)

Activity Log cannot be accessed.

Maybe the problem occured after pressing the deploy button twice.

We found the following reference to a similar issue: https://github.com/Icinga/icingaweb2-module-director/issues/823 Using the desribed SQL statements we identified two ids with the same checksum in director_activity_log but we cannot delete them.

SELECT id FROM director_activity_log WHERE checksum = (SELECT last_activity_checksum FROM director_generated_config WHERE checksum = (SELECT config_checksum FROM director_deployment_log ORDER by id desc limit 1 ) ); +-------+ | id | +-------+ | 25670 | | 25671 | +-------+ 2 rows in set (0.00 sec)

SELECT -> id, -> object_type, -> object_name, -> old_properties, -> new_properties, -> author, -> change_time, -> LOWER(HEX(checksum)) AS checksum, -> LOWER(HEX(parent_checksum)) AS parent_checksum -> FROM director_activity_log -> WHERE id IN (25670, 25671)\G

1. row id: 25670 object_type: icinga_host object_name: my_object_name old_properties: {"object_name":"my_object_name","object_type":"object","display_name":"my_display_name","address":"192.168.1.1","zone":"my_zone_name","vars":{"my_own_vars","imports":["template_1","template_2","template_3","template_4"]} new_properties: {"address":"192.168.1.1","disabled":true,"display_name":"my_display_name","imports":["template_1","template_2","template_3","template_4"],"object_name":"my_object_name","object_type":"object","vars":{"my_own_vars","zone":"my_zone_name"} author: my_user change_time: 2022-08-22 14:14:36 checksum: 18aaa0d189aa101bb12add6c1870794ef136a82b parent_checksum: e7e84418fe2d27da2db50f51c9e58b37acc03f68

2. row id: 25671 object_type: icinga_host object_name: my_object_name old_properties: {"object_name":"my_object_name","object_type":"object","display_name":"my_display_name","address":"192.168.1.1","zone":"my_zone_name","vars":{"my_own_vars","imports":["template_1","template_2","template_3","template_4"]} new_properties: {"address":"192.168.1.1","disabled":true,"display_name":"my_display_name","imports":["template_1","template_2","template_3","template_4"],"object_name":"my_object_name","object_type":"object","vars":{"my_own_vars","zone":"my_zone_name"} author: my_user change_time: 2022-08-22 14:14:36 checksum: 18aaa0d189aa101bb12add6c1870794ef136a82b parent_checksum: e7e84418fe2d27da2db50f51c9e58b37acc03f68 2 rows in set (0.01 sec)

DELETE FROM director_activity_log WHERE id = 25671; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (my_director_db.director_generated_config, CONSTRAINT director_generated_config_activity FOREIGN KEY (last_activity_checksum) REFERENCES director_activity_log (checksum))

How can we fix this without restore of the director DB ?

Environment:

Icinga2 Version r2.13.3-1

Icinga Web 2 Version 2.8.4 Git commit 800378d819cb54d2fbb7b26c0d8ef19edbd16788 PHP Version 7.3.29 Git commit date 2021-07-27

director Version 1.8.1

Operating System: CentOS Linux 7 (Core) CPE OS Name: cpe:/o:centos:centos:7 Kernel: Linux 3.10.0-1160.59.1.el7.x86_64 Architecture: x86-64

The Icinga2 Core of our productive monitoring solution is deployed within AWS environment with use of AWS RDS database element.

[sol1-matt]

I've managed to trigger this same problem. The process for me was

1. update template
2. director crashed
3. restart icinga-director service
4. the change didn't show as saved in the template (could have been changed and not showing in the browser)
5. update the template again
6. director crashed again
7. restart icinga-director service
8. the change now showed as saved in the template
9. activity log showed two changes which were the same
10. deployment ran (from job)

I can't delete the duplicate director_activity_log entry or change the checksum on it due to the foreign key constraint listed above.

[sol1-matt]

The solution I came up with was to undo the deployment from the DB and remove the duplicate activity log entry

Find the duplicate activity log entry first and note the id To see what will be removed run the following queries

select checksum from director_activity_log where id = 3623164;
select * from director_generated_config where last_activity_checksum = (select checksum from director_activity_log where id = 3623164) limit 1;
select d.* from director_generated_config c LEFT JOIN director_deployment_log d ON c.checksum = d.config_checksum where c.last_activity_checksum = (select checksum from director_activity_log where id = 3623164) limit 1\G;

The deleting needs to happen in the reverse order I used the id found above to delete from the director_deployment_log

delete from director_deployment_log where id = 3674 limit 1;
delete from director_generated_config where last_activity_checksum = (select checksum from director_activity_log where id = 3623164) limit 1;
delete from director_activity_log where id = 3623164 limit 1;