[dev.icinga.com #7765] DB IDO: Duplicate entry icinga_{host,service}dependencies

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/7765

Created by chaen on 2014-11-20 16:55:37 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2015-02-12 12:25:03 +00:00)
Target Version: 2.3.0
Last Update: 2015-02-12 12:25:03 +00:00 (in Redmine)

Icinga Version: 2.1.1

---

Hi,

Starting with a fresh database, I see this kind of error in the logs. The problem is that then the DB is not filled at all, and it is thus impossible to know what is the services it concerns.

[2014-11-20 17:46:23 +0100] critical/IdoMysqlConnection: Error "Duplicate entry '1-0-353983-372747-0-1-0-0-0-0' for key 'instance_id'" when executing query "INSERT INTO icinga_servicedependencies (dependent_service_object_id, fail_on_critical, fail_on_ok, fail_on_unknown, fail_on_warning, inherits_parent, instance_id, service_object_id) VALUES (372747, '0', '0', '0', '0', '1', 1, 353983)"
Context:
        (0) Reconnecting to MySQL IDO database 'ido-mysql'

[2014-11-20 17:46:23 +0100] critical/IdoMysqlConnection: Exception during database operation: Verify that your database is operational!
[2014-11-20 17:46:23 +0100] information/IdoMysqlConnection: MySQL IDO instance id: 1 (schema version: '1.11.7')

My configuration is quite large, so it is very difficult to find anything with grep (I tried and failed). Any idea how I could proceed ?
Thanks a lot !
Chris

Changesets

2015-02-12 12:19:27 +00:00 by (unknown) 9d97094

DB IDO: Fix duplicate entries in {host,service}depencencies table

fixes #7765

2015-02-16 10:38:00 +00:00 by (unknown) e4c5c9f

DB IDO: Fix PostgreSQL schema file

refs #7765

---

Relations:

• relates #8425
• relates #7765

[icinga-migration]

Updated by gbeutner on 2014-11-20 20:17:03 +00:00

Do you have multiple service dependencies which have the same parent and child? This is probably a bug in the IDO schema (i.e. there's a UNIQUE contraint on the parent/child while there obviously shouldn't be any).

[icinga-migration]

Updated by chaen on 2014-11-21 07:51:47 +00:00

Well this is what I was trying to find out in my configuration but I did not manage. It would actually be great to have a tool to see what is impacted by a given apply rule, but this is for another ticket ;-)
I am not sure I would blame it on IDO schema. Indeed, there is a unique constraint, but it is quite restrictive
UNIQUE KEY `instance_id` (`instance_id`,`config_type`,`service_object_id`,`dependent_service_object_id`,`dependency_type`,`inherits_parent`,`fail_on_ok`,`fail_on_warning`,`fail_on_unknown`,`fail_on_critical`)
And eventhough it probably does not harm too much to have the same dependency multiple times in the DB, if it is the case, it is a flaw in my configuration that I would like to track down :-)
Could this be part of the configuration check ?

[icinga-migration]

Updated by chaen on 2014-11-21 12:00:25 +00:00

For information, I managed to find where was my problem by removing the unique constraint. Then, the db was loaded, and I could query the icinga_servicedependencies to find the duplicates.

[icinga-migration]

Updated by chaen on 2014-11-21 12:19:59 +00:00

So after some investigation, I even what in the configuration created this mess. Following suggestions of Michael, we have this dependency defined
apply Dependency "disable-host-service-notifications" to Service { disable_checks = true assign where true }

He said "If parent_host_name is omitted, it will use the host_name the apply rule is matched for. That's a complex yet undocumented feature". Actually, it's more than "undocumented", the doc even says the opposite " The parent_host_name and parent_service_name attributes are mandatory for service dependencies". But fine :-)
So this rule will create a dependency for each service of a host, against the host it runs on, and thus an entry in the DB.

I now have another dependency that makes a service, running in many hosts dependent on one particular host
apply Dependency "myOtherDep" to Service { parent_host_name = "specificHost" disable_checks = true disable_notifications = true assign where service.name == "serviceThatRunsInManyPlaces" }

By mystake, serviceThatRunsInManyPlaces was also applied to specificHost, and hence there was 2 dependencies applied between serviceThatRunsInManyPlaces and specificHost : disable-host-service-notifications and myOtherDep.

Maybe the easiest fix would be to use 'INSERT INGORE INTO' instead of just 'INSERT INTO'. But I think that such a 'double dependency' still deserves a warning when doing the checkconfig, and it would be even more awesome if one could know which dependencies they are.

[icinga-migration]

Updated by gbeutner on 2014-11-21 14:41:16 +00:00

• Assigned to set to mfriedrich

I guess the easiest fix would be to remove the unique contraint.

[icinga-migration]

Updated by gbeutner on 2014-11-21 14:41:25 +00:00

• Target Version set to 2.2.1

[icinga-migration]

Updated by mfriedrich on 2014-11-25 09:50:11 +00:00

• Target Version changed from 2.2.1 to 2.3.0

[icinga-migration]

Updated by gbeutner on 2015-02-02 07:46:45 +00:00

• Status changed from New to Assigned

icinga_scheduleddowntimes has the same problem:

[2015-02-02 08:45:41 +0100] critical/IdoMysqlConnection: Error "Duplicate entry '1-340-2015-02-02 07:45:30-2423' for key 'instance_id'" when executing query "INSERT INTO icinga_scheduleddowntime (author_name, comment_data, downtime_type, duration, endpoint_object_id, entry_time, instance_id, internal_downtime_id, is_fixed, object_id, scheduled_end_time, scheduled_start_time) VALUES ('icingaadmin', 'Scheduled downtime for backup', '1', '0', 16, FROM_UNIXTIME(1422863130), 1, '2423', '1', 340, FROM_UNIXTIME(1422928800), FROM_UNIXTIME(1422925200))"

[icinga-migration]

Updated by mfriedrich on 2015-02-09 12:27:23 +00:00

• Estimated Hours set to 8

[icinga-migration]

Updated by mfriedrich on 2015-02-12 09:09:09 +00:00

Apply rules generate multiple dependency objects with their own unique name. The problem with the old Icinga 1.x schema is that there's a constraint only for all dependency attributes, but not the name itself.

This issue can only be reproduced with classic dependencies (host-host, service-service) which are dumped to the backend. The schema does not support writing host-service and service-host dependencies.

object Host "7765-host-01" {
  address = "127.0.0.1"
  check_command = "hostalive"
}
object Host "7765-host-02" {
  address = "127.0.0.1"
  check_command = "hostalive"
}

apply Dependency "7765-dep-host-01" to Host {
  parent_host_name = "7765-host-01"
  assign where host.name == "7765-host-02"
}
apply Dependency "7765-dep-host-02" to Host {
  parent_host_name = "7765-host-01"
  assign where match("7765*", host.name)
}
apply Dependency "7765-dep-host-03" to Host {
  parent_host_name = "7765-host-01"
  assign where regex("^7765*", host.name)
}

object Service "7765-service-01" {
  check_command = "dummy"
  host_name = "7765-host-01"
}
object Service "7765-service-02" {
  check_command = "dummy"
  host_name = "7765-host-02"
}

apply Dependency "7765-dep-service-01" to Service {
  parent_host_name = "7765-host-01"
  parent_service_name = "7765-service-01"
  assign where host.name == "7765-host-02" && service.name == "7765-service-02"
}
apply Dependency "7765-dep-service-02" to Service {
  parent_host_name = "7765-host-01"
  parent_service_name = "7765-service-01"
  assign where service.name == "7765-service-02"
}
apply Dependency "7765-dep-service-03" to Service {
  parent_host_name = "7765-host-01"
  parent_service_name = "7765-service-01"
  assign where match("7765*", service.name)
}

[2015-02-12 10:03:53 +0100] debug/HostDbObject: host dependencies for '7765-host-01'
[2015-02-12 10:03:53 +0100] debug/HostDbObject: parent host: 7765-host-01
[2015-02-12 10:03:53 +0100] debug/IdoMysqlConnection: Query: INSERT INTO icinga_hostdependencies (dependent_host_object_id, fail_on_down, fail_on_up, host_object_id, inherits_parent, instance_id) VALUES (58353, '0', '1', 58353, '1', 1)
[2015-02-12 10:03:53 +0100] debug/HostDbObject: parent host: 7765-host-01
[2015-02-12 10:03:53 +0100] debug/IdoMysqlConnection: Query: INSERT INTO icinga_hostdependencies (dependent_host_object_id, fail_on_down, fail_on_up, host_object_id, inherits_parent, instance_id) VALUES (58353, '0', '1', 58353, '1', 1)
[2015-02-12 10:03:53 +0100] critical/IdoMysqlConnection: Error "Duplicate entry '1-0-58353-58353-0-1-1-0-0' for key 'instance_id'" when executing query "INSERT INTO icinga_hostdependencies (dependent_host_object_id, fail_on_down, fail_on_up, host_object_id, inherits_parent, instance_id) VALUES (58353, '0', '1', 58353, '1', 1)"

[icinga-migration]

Updated by mfriedrich on 2015-02-12 09:09:26 +00:00

• Description updated

[icinga-migration]

Updated by mfriedrich on 2015-02-12 12:08:19 +00:00

• Subject changed from Duplicate entry icinga_servicedependencies to DB IDO: Duplicate entry icinga{host,service}dependencies_

The downtimes problem is different. I have created a new issue for that in #8425

Removing the constraint from the *dependences tables solves the problem, and it does not hurt to have multiple dependencies from the same type inside the database.

[icinga-migration]

Updated by mfriedrich on 2015-02-12 12:09:03 +00:00

• Relates set to 8425

[icinga-migration]

Updated by mfriedrich on 2015-02-12 12:23:08 +00:00

• Relates set to 8426

[icinga-migration]

Updated by Anonymous on 2015-02-12 12:25:03 +00:00

• Status changed from Assigned to Resolved
• Done % changed from 0 to 100

Applied in changeset 9d97094.