Getting error opening History "createTicketLinks() must be of the type string, null returned" #697
Comments
|
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. You mean if a Downtime event in the database exists without an comment, this may cause this error? |
|
I'm currently more interested in how this can occur 🤔 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: Only the field downtime_id have entries with NULL. |
|
It gets even more interesting. Please show the output of:
and
|
|
|
The 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 I could suggest to remove the invalid entries in 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. |
nilmerg
removed
the
needs-feedback
|
We Are running a single Maria DB instance. |
Could be related to retention which leverages the missing constraints. @pbirokas Do you have retention enabled? Please share |
|
Here are the part from icingadb/config.yml All icinga related DBs was setup with those lines:
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, |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Describe the bug
When trying to open the history from some Services or Hosts, I am getting an error. Other work as expected
To Reproduce
I have no idea!
Expected behavior
Expected to see the history of all items.
Screenshots
Your Environment
Include as many relevant details about the environment you experienced the problem in
icinga2 --version): r2.14.0-1php --version): PHP 7.2.24The text was updated successfully, but these errors were encountered: