Icinga
powershellgallery
would it be possible to add the new versions to the powershellgallery?
Hi,
this is happening because you have a downtime event with no comment attached to it. (i.e. a downtime without a comment) This is actually not possible because it's required.
Have you migrated from IDO and the time you got this the list might have included an event before the migration?
We Setup this installation directly with icingadb. So, there was no migration.
You mean if a Downtime event in the database exists without an comment, this may cause this error? How can I Fix this? Is there another possibility then just redure retention on downtime?
I'm currently more interested in how this can occur :thinking:
Please share the output of the following query while you're connected to the icingadb database:
SELECT h.downtime_history_id, dh.downtime_id
FROM history h
LEFT JOIN downtime_history dh ON h.downtime_history_id = dh.downtime_id
WHERE h.event_type IN ('downtime_end', 'downtime_start')
AND (h.downtime_history_id IS NULL OR dh.downtime_id IS NULL);
I got ~103K entries out of this query.
It looks like this:
downtime_history_id downtime_id
£ÚJ¼Ô Ãÿ^X ^E^PN\0>¦<92><81>Èó NULL
<91>ÍnM£<9d>¼)°<83>^QX<90>`|<9c>Y^T+÷ NULL
¥^\xñ8&<84>B<93>^S^Yl<9c>¥ê0w¼ë NULL
¡¤J¼4ñuµ^B^V¥<96>a^Q<82>&^[<93>^W` NULL
J·Ã^U<95>U,¯^C¡<93>þ^Vv<86>JîÑ<93>¯ NULL
YÄ®¢°¢DO!¬>Lý^]^K¶ Eb^U NULL
Qá<8e>qbó^FÜÅëèë^^<89>?o\\]À` NULL
<8a>§T^\¿0a͵BîlªÕ l ø^WX NULL
âC~G<92><8e><8a>ll<8d>AÐ Ì1<96>¥<91>`J NULL
ièûnÿ0^H^Kä^LÄ-c^E^L[5FOR NULL
Í8=óê^Bl^Aýî Äe%8VH<9c> NULL
v^S<88><95>ÐKâ\n<8c>jÛ^?DØD<8b>£2"U NULL
Only the field downtime_id have entries with NULL.
It gets even more interesting. Please show the output of:
SHOW CREATE TABLE history
and
SELECT * FROM icingadb_schema
MariaDB [icingadb]> SHOW CREATE TABLE history;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| history | CREATE TABLE `history` (
`id` binary(20) NOT NULL COMMENT 'sha1(environment.name + event_type + x...) given that sha1(environment.name + x...) = *_history_id',
`environment_id` binary(20) NOT NULL COMMENT 'environment.id',
`endpoint_id` binary(20) DEFAULT NULL COMMENT 'endpoint.id',
`object_type` enum('host','service') NOT NULL,
`host_id` binary(20) NOT NULL COMMENT 'host.id',
`service_id` binary(20) DEFAULT NULL COMMENT 'service.id',
`notification_history_id` binary(20) DEFAULT NULL COMMENT 'notification_history.id',
`state_history_id` binary(20) DEFAULT NULL COMMENT 'state_history.id',
`downtime_history_id` binary(20) DEFAULT NULL COMMENT 'downtime_history.downtime_id',
`comment_history_id` binary(20) DEFAULT NULL COMMENT 'comment_history.comment_id',
`flapping_history_id` binary(20) DEFAULT NULL COMMENT 'flapping_history.id',
`acknowledgement_history_id` binary(20) DEFAULT NULL COMMENT 'acknowledgement_history.id',
`event_type` enum('state_change','ack_clear','downtime_end','flapping_end','comment_remove','comment_add','flapping_start','downtime_start','ack_set','notification') NOT NULL,
`event_time` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_history_event_time` (`event_time`) COMMENT 'History filtered/ordered by event_time',
KEY `idx_history_acknowledgement` (`acknowledgement_history_id`),
KEY `idx_history_comment` (`comment_history_id`),
KEY `idx_history_downtime` (`downtime_history_id`),
KEY `idx_history_flapping` (`flapping_history_id`),
KEY `idx_history_notification` (`notification_history_id`),
KEY `idx_history_state` (`state_history_id`),
KEY `idx_history_host_service_id` (`host_id`,`service_id`,`event_time`) COMMENT 'Host/service history detail filter'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
MariaDB [icingadb]> SELECT * FROM icingadb_schema;
+----+---------+-------------------+
| id | version | timestamp |
+----+---------+-------------------+
| 1 | 3 | 20221128140440000 |
| 2 | 4 | 20240108153719000 |
+----+---------+-------------------+
2 rows in set (0.000 sec)
The history table in your database seems to miss vital constraints, which would disallow what you're experiencing: Missing entries in downtime_history referenced by entries in history.
Since the schema adds these constraints since 1.0.0-rc2 (i.e. since schema version 2), these should exist even if you've used Icinga DB v1.0.0-rc1. But I think this can safely be ruled out, since your schema table only includes an entry for version 3, which is v1.0.0 stable.
Though, this still doesn't explain why the constraints are missing. And, why Icinga DB doesn't insert accompanying entries in downtime_history or why they were deleted in case they ever existed.
I could suggest to remove the invalid entries in history, but I think that is only a temporary solution and doesn't prevent new invalid entries.
I'll transfer the issue to the Icinga DB repository, the folks there should be able to provide more insight.
In the meantime, please also tell us the database vendor plus version and whether you use a high-availability database cluster.
We Are running a single Maria DB instance.
[root@icinga ~]# mysql --version
mysql Ver 15.1 Distrib 10.3.39-MariaDB, for Linux (x86_64) using readline 5.1
I could suggest to remove the invalid entries in
history, but I think that is only a temporary solution and doesn't prevent new invalid entries.
Could be related to retention which leverages the missing constraints. @pbirokas Do you have retention enabled? Please share config.yml#retention. And how did you setup the database? Did you remove the constraints? Else, I have no explanation why they would be missing.
Here are the part from icingadb/config.yml
retention:
history-days: 90
sla-days: 90
options:
acknowledgement: 90
comment: 90
downtime: 90
flapping: 90
notification: 90
state: 90
All icinga related DBs was setup with those lines:
CREATE DATABASE icingadb;
CREATE USER 'icingadb'@'localhost' IDENTIFIED BY '************';
GRANT ALL ON icingadb.* TO 'icingadb'@'localhost';
CREATE DATABASE icinga;
GRANT SELECT, INSERT, UPDATE, DELETE, DROP, CREATE VIEW, INDEX, EXECUTE ON icinga.* TO 'icinga'@'localhost' IDENTIFIED BY '************';
CREATE DATABASE icingaweb2;
CREATE USER 'icingaweb2'@'localhost' IDENTIFIED BY '************';
GRANT ALL ON icingaweb2.* TO 'icingaweb2'@'localhost';
CREATE DATABASE icinga_director CHARACTER SET 'utf8';
CREATE USER director@localhost IDENTIFIED BY '************';
GRANT ALL ON icinga_director.* TO director@localhost;
CREATE DATABASE vspheredb CHARACTER SET 'utf8mb4' COLLATE utf8mb4_bin;
CREATE USER vspheredb@localhost IDENTIFIED BY '************';
GRANT ALL ON vspheredb.* TO vspheredb@localhost;
Did you remove the constraints?
No, I didn remove those in the DB.
@pbirokas To be honest, I have no clue why the constraints wouldn't be there but you should clean the invalid rows and add the constraints.
@lippserd, since I am not expirienced in sql, can you provide me the queries do delete those entries and setup the constrains?
Hi @pbirokas,
Please excuse the late reply. Please stop Icinga DB, execute the following statements in the Icinga DB database to delete incorrect/incomplete history data and make sure to add the constraints afterwards:
-- Delete notification events from the `history' table if its dependent row is missing in the `notification_history' table.
DELETE h FROM history h
LEFT JOIN notification_history sh ON sh.id = h.notification_history_id
WHERE h.event_type = "notification" AND sh.id IS NULL;
-- Delete rows from the `notification_history' table if its parent row is missing in the `history' table.
DELETE sh FROM notification_history sh
LEFT JOIN history h ON h.notification_history_id = sh.id
WHERE h.notification_history_id IS NULL;
-- Delete state events from the `history' table if its dependent row is missing in the `state_history' table.
DELETE h FROM history h
LEFT JOIN state_history sh ON sh.id = h.state_history_id
WHERE h.event_type = "state_change" AND sh.id IS NULL;
-- Delete rows from the `state_history' table if its parent row is missing in the `history' table.
DELETE sh FROM state_history sh
LEFT JOIN history h ON h.state_history_id = sh.id
WHERE h.state_history_id IS NULL;
-- Delete downtime events from the `history' table if its dependent row is missing in the `downtime_history' table.
DELETE h FROM history h
LEFT JOIN downtime_history sh ON sh.downtime_id = h.downtime_history_id
WHERE h.event_type IN ("downtime_start", "downtime_end") AND sh.downtime_id IS NULL;
-- Delete rows from the `downtime_history' table if its parent row is missing in the `history' table.
DELETE sh FROM downtime_history sh
LEFT JOIN history h ON h.downtime_history_id = sh.downtime_id
WHERE h.downtime_history_id IS NULL;
-- Delete comment events from the `history' table if its dependent row is missing in the `comment_history' table.
DELETE h FROM history h
LEFT JOIN comment_history sh ON sh.comment_id = h.comment_history_id
WHERE h.event_type IN ("comment_add", "comment_remove") AND sh.comment_id IS NULL;
-- Delete rows from the `comment_history' table if its parent row is missing in the `history' table.
DELETE sh FROM comment_history sh
LEFT JOIN history h ON h.comment_history_id = sh.comment_id
WHERE h.comment_history_id IS NULL;
-- Delete flapping events from the `history' table if its dependent row is missing in the `flapping_history' table.
DELETE h FROM history h
LEFT JOIN flapping_history sh ON sh.id = h.flapping_history_id
WHERE h.event_type IN ("flapping_start", "flapping_end") AND sh.id IS NULL;
-- Delete rows from the `flapping_history' table if its parent row is missing in the `history' table.
DELETE sh FROM flapping_history sh
LEFT JOIN history h ON h.flapping_history_id = sh.id
WHERE h.flapping_history_id IS NULL;
-- Delete acknowledgement events from the `history' table if its dependent row is missing in the `acknowledgement_history' table.
DELETE h FROM history h
LEFT JOIN acknowledgement_history sh ON sh.id = h.acknowledgement_history_id
WHERE h.event_type IN ("ack_set", "ack_clear") AND sh.id IS NULL;
-- Delete rows from the `acknowledgement_history' table if its parent row is missing in the `history' table.
DELETE sh FROM acknowledgement_history sh
LEFT JOIN history h ON h.acknowledgement_history_id = sh.id
WHERE h.acknowledgement_history_id IS NULL;
Best regards, Eric
Did you remove the constraints? Else, I have no explanation why they would be missing.
I second that, these constraints were part of the CREATE TABLE history statement from v1.0.0 on: https://github.com/Icinga/icingadb/blob/v1.0.0/schema/mysql/schema.sql#L1253-L1287
So without any hint how they could have gone missing, there isn't much we can do here.
