Icingadb cleanup duplicate entries due to environment_id

thesefer · May 7, 2025, 6:09pm

Unfortunately something bad happened due to my HA setup which went unnoticed until now. I have duplicate entries in the database for my hosts.

I completely uninstalled the second master and removed all icinga2 traces for now so I can clean up everything. Meaning I am in a single master setup now.

I already deleted all hosts from the director which were affacted but the duplicate entries remained in the database (and are still shown in icingaweb with all services associated).

How can I properly clean up my database now? I have only found this issue but no help how to clean up the mess I produced: Icinga2db Web show double object · Issue #532 · Icinga/icingadb · GitHub

I guess I have to remove from host where environmental_id the wrong one. But where else in the database do I have to look for more traces?

EDIT: What I also do not understands, what’s wrong with the encoding?

+----------------------+------------------------------------------+
| id                   | name                                     |
+----------------------+------------------------------------------+
| ,�y���^��$��t6.{�ܮ           | 2cfb79e0efef5e1caa8124d3fd74362e7bf3dcae |
| �
   �łF�␦��
5ޠ�@�O           | 900ce7c58246ee9b1afce90a1035dea0de40d44f |
+----------------------+------------------------------------------+

character set

SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

collation

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+

icingadb

 SELECT default_character_set_name FROM information_schema.SCHEMATA  WHERE schema_name = "icingadb";
+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8mb4                    |
+----------------------------+

moreamazingnick · May 7, 2025, 8:05pm

the ids are stored in binary there is nothing wrong with the encoding.

you can look up the evironmentid on the current master:

/var/lib/icinga2/icingadb.env

to create queries to cleanup the icingadb database.

here is an example how to make the binary encoded ids human readable:

select hex(environment_id) from host limit 1;

thesefer · May 8, 2025, 7:05am

ok, I was worried about the gibberish text but maybe a misunderstand on my part (I’ll research the topic)

I already know the correct ID and which to remove because

I already deleted all hosts from the director which were affacted but the duplicate entries remained in the database (and are still shown in icingaweb with all services associated).

This means only the wrong one is left (and I verified it before to be sure).

I proceeded to remove all entries from host, services and environment where environmental_id is the wrong one. Is this enough to be safe now?

Best regards

moreamazingnick · May 8, 2025, 2:20pm

This means only the wrong one is left (and I verified it before to be sure).

hopefully you did you remove everything with the wrong environment id?
have a look at:

github.com/Icinga/icingadb

schema/mysql/schema.sql

main

-- IcingaDB | (c) 2019 Icinga GmbH | GPLv2+

SET SESSION sql_mode = 'STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION';
SET SESSION innodb_strict_mode = 1;

DROP FUNCTION IF EXISTS get_sla_ok_percent;
DELIMITER //
CREATE FUNCTION get_sla_ok_percent(
  in_host_id binary(20),
  in_service_id binary(20),
  in_start_time bigint unsigned,
  in_end_time bigint unsigned
)
RETURNS decimal(7, 4)
READS SQL DATA
BEGIN
  DECLARE result decimal(7, 4);
  DECLARE row_event_time bigint unsigned;
  DECLARE row_event_type enum('state_change', 'downtime_start', 'downtime_end', 'end');
  DECLARE row_event_prio int;

This file has been truncated. show original

and double check, there are hostgroups and so on which also have this environment_id.

If you decide to setup a new second master make sure the have the same environment id.

thesefer · May 8, 2025, 3:11pm

I checked some tables where environment_id occurs. But I cannot read them because they are encoded (binary) like above.

I hope cleaning in host, group, environment is enough for now. At least the setup was running again and after adding them again everything looked fine. The second master came up properly after the re-setup with the correct environment_id.