Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[dev.icinga.com #12210] Do not clear {host,service,contact}group_members tables on restart #4391

Closed
icinga-migration opened this issue Jul 22, 2016 · 8 comments

Comments

Projects
None yet
1 participant
@icinga-migration
Copy link
Member

commented Jul 22, 2016

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

Created by mfriedrich on 2016-07-22 14:35:29 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2016-07-25 15:15:07 +00:00)
Target Version: 2.5.0
Last Update: 2016-07-25 15:15:07 +00:00 (in Redmine)

Icinga Version: 2.4.10
Backport?: Not yet backported
Include in Changelog: 1

Those tables depend on the *group insert id. The main issue over restarts is to clean removed group memberships.

Changesets

2016-07-25 15:10:27 +00:00 by mfriedrich ce3d7ab

DB IDO: Do not clear {host,service,contact}group_members tables on restart

fixes #12210

2016-08-01 07:37:10 +00:00 by mfriedrich 32b37f0

Fix missing session_token column

refs #12210

2016-08-01 13:04:47 +00:00 by mfriedrich 7d78c53

Fix incorrect session_token index for PostgreSQL

refs #12210

2016-08-02 09:05:48 +00:00 by mfriedrich 7341727

Fix incorrect index drop in pgsql.sql

refs #12210

Relations:

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 25, 2016

Updated by mfriedrich on 2016-07-25 12:21:11 +00:00

  • Priority changed from Normal to High
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 25, 2016

Updated by mfriedrich on 2016-07-25 12:36:51 +00:00

Tests

Create

for (i in range(5)) {
  object HostGroup "12210-hg-" + i {
    assign where match("12210*", host.name)
  }
}

for (j in range(10)) {
  object Host "12210-host-" + j {
    check_command = "dummy"
  }
}

Mysql

MariaDB [icinga]> select hgo.name1 as hostgroup, ohs.name1 as host from icinga_hoststatus hs join icinga_objects ohs on hs.host_object_id=ohs.object_id join icinga_hostgroup_members hgm on hs.host_object_id=hgm.host_object_id join icinga_hostgroups hg on hgm.hostgroup_id=hg.hostgroup_id join icinga_objects hgo on hg.hostgroup_object_id=hgo.object_id where hgo.name1 like '12210%' order by hgo.name1, ohs.name1;
+------------+--------------+
| hostgroup  | host         |
+------------+--------------+
| 12210-hg-0 | 12210-host-0 |
| 12210-hg-0 | 12210-host-1 |
| 12210-hg-0 | 12210-host-2 |
| 12210-hg-0 | 12210-host-3 |
| 12210-hg-0 | 12210-host-4 |
| 12210-hg-0 | 12210-host-5 |
| 12210-hg-0 | 12210-host-6 |
| 12210-hg-0 | 12210-host-7 |
| 12210-hg-0 | 12210-host-8 |
| 12210-hg-0 | 12210-host-9 |
| 12210-hg-1 | 12210-host-0 |
| 12210-hg-1 | 12210-host-1 |
| 12210-hg-1 | 12210-host-2 |
| 12210-hg-1 | 12210-host-3 |
| 12210-hg-1 | 12210-host-4 |
| 12210-hg-1 | 12210-host-5 |
| 12210-hg-1 | 12210-host-6 |
| 12210-hg-1 | 12210-host-7 |
| 12210-hg-1 | 12210-host-8 |
| 12210-hg-1 | 12210-host-9 |
| 12210-hg-2 | 12210-host-0 |
| 12210-hg-2 | 12210-host-1 |
| 12210-hg-2 | 12210-host-2 |
| 12210-hg-2 | 12210-host-3 |
| 12210-hg-2 | 12210-host-4 |
| 12210-hg-2 | 12210-host-5 |
| 12210-hg-2 | 12210-host-6 |
| 12210-hg-2 | 12210-host-7 |
| 12210-hg-2 | 12210-host-8 |
| 12210-hg-2 | 12210-host-9 |
| 12210-hg-3 | 12210-host-0 |
| 12210-hg-3 | 12210-host-1 |
| 12210-hg-3 | 12210-host-2 |
| 12210-hg-3 | 12210-host-3 |
| 12210-hg-3 | 12210-host-4 |
| 12210-hg-3 | 12210-host-5 |
| 12210-hg-3 | 12210-host-6 |
| 12210-hg-3 | 12210-host-7 |
| 12210-hg-3 | 12210-host-8 |
| 12210-hg-3 | 12210-host-9 |
| 12210-hg-4 | 12210-host-0 |
| 12210-hg-4 | 12210-host-1 |
| 12210-hg-4 | 12210-host-2 |
| 12210-hg-4 | 12210-host-3 |
| 12210-hg-4 | 12210-host-4 |
| 12210-hg-4 | 12210-host-5 |
| 12210-hg-4 | 12210-host-6 |
| 12210-hg-4 | 12210-host-7 |
| 12210-hg-4 | 12210-host-8 |
| 12210-hg-4 | 12210-host-9 |
+------------+--------------+
50 rows in set (0.00 sec)

Postgresql

icinga=> select hgo.name1 as hostgroup, ohs.name1 as host from icinga_hoststatus hs join icinga_objects ohs on hs.host_object_id=ohs.object_id join icinga_hostgroup_members hgm on hs.host_object_id=hgm.host_object_id join icinga_hostgroups hg on hgm.hostgroup_id=hg.hostgroup_id join icinga_objects hgo on hg.hostgroup_object_id=hgo.object_id where hgo.name1 like '12210%' order by hgo.name1, ohs.name1;
 hostgroup  |     host
------------+--------------
 12210-hg-0 | 12210-host-0
 12210-hg-0 | 12210-host-1
 12210-hg-0 | 12210-host-2
 12210-hg-0 | 12210-host-3
 12210-hg-0 | 12210-host-4
 12210-hg-0 | 12210-host-5
 12210-hg-0 | 12210-host-6
 12210-hg-0 | 12210-host-7
 12210-hg-0 | 12210-host-8
 12210-hg-0 | 12210-host-9
 12210-hg-1 | 12210-host-0
 12210-hg-1 | 12210-host-1
 12210-hg-1 | 12210-host-2
 12210-hg-1 | 12210-host-3
 12210-hg-1 | 12210-host-4
 12210-hg-1 | 12210-host-5
 12210-hg-1 | 12210-host-6
 12210-hg-1 | 12210-host-7
 12210-hg-1 | 12210-host-8
 12210-hg-1 | 12210-host-9
 12210-hg-2 | 12210-host-0
 12210-hg-2 | 12210-host-1
 12210-hg-2 | 12210-host-2
 12210-hg-2 | 12210-host-3
 12210-hg-2 | 12210-host-4
 12210-hg-2 | 12210-host-5
 12210-hg-2 | 12210-host-6
 12210-hg-2 | 12210-host-7
 12210-hg-2 | 12210-host-8
 12210-hg-2 | 12210-host-9
 12210-hg-3 | 12210-host-0
 12210-hg-3 | 12210-host-1
 12210-hg-3 | 12210-host-2
 12210-hg-3 | 12210-host-3
 12210-hg-3 | 12210-host-4
 12210-hg-3 | 12210-host-5
 12210-hg-3 | 12210-host-6
 12210-hg-3 | 12210-host-7
 12210-hg-3 | 12210-host-8
 12210-hg-3 | 12210-host-9
 12210-hg-4 | 12210-host-0
 12210-hg-4 | 12210-host-1
 12210-hg-4 | 12210-host-2
 12210-hg-4 | 12210-host-3
 12210-hg-4 | 12210-host-4
 12210-hg-4 | 12210-host-5
 12210-hg-4 | 12210-host-6
 12210-hg-4 | 12210-host-7
 12210-hg-4 | 12210-host-8
 12210-hg-4 | 12210-host-9
(50 rows)

Update/Delete

for (i in range(5)) {
  object HostGroup "12210-hg-" + i {
    assign where match("12210*", host.name)
  }
}

for (j in range(5)) {
  object Host "12210-host-" + j {
    check_command = "dummy"
  }
}

MySQL

MariaDB [icinga]> select hgo.name1 as hostgroup, ohs.name1 as host from icinga_hoststatus hs join icinga_objects ohs on hs.host_object_id=ohs.object_id join icinga_hostgroup_members hgm on hs.host_object_id=hgm.host_object_id join icinga_hostgroups hg on hgm.hostgroup_id=hg.hostgroup_id join icinga_objects hgo on hg.hostgroup_object_id=hgo.object_id where hgo.name1 like '12210%' order by hgo.name1, ohs.name1;
+------------+--------------+
| hostgroup  | host         |
+------------+--------------+
| 12210-hg-0 | 12210-host-0 |
| 12210-hg-0 | 12210-host-1 |
| 12210-hg-0 | 12210-host-2 |
| 12210-hg-0 | 12210-host-3 |
| 12210-hg-0 | 12210-host-4 |
| 12210-hg-1 | 12210-host-0 |
| 12210-hg-1 | 12210-host-1 |
| 12210-hg-1 | 12210-host-2 |
| 12210-hg-1 | 12210-host-3 |
| 12210-hg-1 | 12210-host-4 |
| 12210-hg-2 | 12210-host-0 |
| 12210-hg-2 | 12210-host-1 |
| 12210-hg-2 | 12210-host-2 |
| 12210-hg-2 | 12210-host-3 |
| 12210-hg-2 | 12210-host-4 |
| 12210-hg-3 | 12210-host-0 |
| 12210-hg-3 | 12210-host-1 |
| 12210-hg-3 | 12210-host-2 |
| 12210-hg-3 | 12210-host-3 |
| 12210-hg-3 | 12210-host-4 |
| 12210-hg-4 | 12210-host-0 |
| 12210-hg-4 | 12210-host-1 |
| 12210-hg-4 | 12210-host-2 |
| 12210-hg-4 | 12210-host-3 |
| 12210-hg-4 | 12210-host-4 |
+------------+--------------+
25 rows in set (0.00 sec)

Postgresql

icinga=> select hgo.name1 as hostgroup, ohs.name1 as host from icinga_hoststatus hs join icinga_objects ohs on hs.host_object_id=ohs.object_id join icinga_hostgroup_members hgm on hs.host_object_id=hgm.host_object_id join icinga_hostgroups hg on hgm.hostgroup_id=hg.hostgroup_id join icinga_objects hgo on hg.hostgroup_object_id=hgo.object_id where hgo.name1 like '12210%' order by hgo.name1, ohs.name1;
 hostgroup  |     host
------------+--------------
 12210-hg-0 | 12210-host-0
 12210-hg-0 | 12210-host-1
 12210-hg-0 | 12210-host-2
 12210-hg-0 | 12210-host-3
 12210-hg-0 | 12210-host-4
 12210-hg-1 | 12210-host-0
 12210-hg-1 | 12210-host-1
 12210-hg-1 | 12210-host-2
 12210-hg-1 | 12210-host-3
 12210-hg-1 | 12210-host-4
 12210-hg-2 | 12210-host-0
 12210-hg-2 | 12210-host-1
 12210-hg-2 | 12210-host-2
 12210-hg-2 | 12210-host-3
 12210-hg-2 | 12210-host-4
 12210-hg-3 | 12210-host-0
 12210-hg-3 | 12210-host-1
 12210-hg-3 | 12210-host-2
 12210-hg-3 | 12210-host-3
 12210-hg-3 | 12210-host-4
 12210-hg-4 | 12210-host-0
 12210-hg-4 | 12210-host-1
 12210-hg-4 | 12210-host-2
 12210-hg-4 | 12210-host-3
 12210-hg-4 | 12210-host-4
(25 rows)
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 25, 2016

Updated by mfriedrich on 2016-07-25 13:55:40 +00:00

Works in a similar fashion for contact groups.

for (i in range(5)) {
  object HostGroup "12210-hg-" + i {
    assign where match("12210*", host.name)
  }
}

for (j in range(10)) {
  object Host "12210-host-" + j {
    check_command = "dummy"
  }
}

for (i in range(5)) {
  object ServiceGroup "12210-sg-" + i {
    assign where match("12210*", host.name)
  }
}

for (j in range(10)) {
  apply Service "12210-service-" + j use (j) {
    check_command = "dummy"
    assign where match("12210-*-" + j, host.name)
  }
}

for (i in range(5)) {
  object UserGroup "12210-ug-" + i {
    assign where match("12210*", user.name)
  }
}

for (j in range(10)) {
  object User "12210-user-" + j {
  }
}

icinga=> select cgo.name1 as contactgroup, co.name1 as contact from icinga_contacts c join icinga_objects co on c.contact_object_id=co.object_id join icinga_contactgroup_members cgm on c.contact_object_id=cgm.contact_object_id join icinga_contactgroups cg on cgm.contactgroup_id=cg.contactgroup_id join icinga_objects cgo on cg.contactgroup_object_id=cgo.object_id where cgo.name1 like '12210%' order by cgo.name1, co.name1;
 contactgroup |   contact
--------------+--------------
 12210-ug-0   | 12210-user-0
 12210-ug-0   | 12210-user-1
 12210-ug-0   | 12210-user-2
 12210-ug-0   | 12210-user-3
 12210-ug-0   | 12210-user-4
 12210-ug-0   | 12210-user-5
 12210-ug-0   | 12210-user-6
 12210-ug-0   | 12210-user-7
 12210-ug-0   | 12210-user-8
 12210-ug-0   | 12210-user-9
 12210-ug-1   | 12210-user-0
 12210-ug-1   | 12210-user-1
 12210-ug-1   | 12210-user-2
 12210-ug-1   | 12210-user-3
 12210-ug-1   | 12210-user-4
 12210-ug-1   | 12210-user-5
 12210-ug-1   | 12210-user-6
 12210-ug-1   | 12210-user-7
 12210-ug-1   | 12210-user-8
 12210-ug-1   | 12210-user-9
 12210-ug-2   | 12210-user-0
 12210-ug-2   | 12210-user-1
 12210-ug-2   | 12210-user-2
 12210-ug-2   | 12210-user-3
 12210-ug-2   | 12210-user-4
 12210-ug-2   | 12210-user-5
 12210-ug-2   | 12210-user-6
 12210-ug-2   | 12210-user-7
 12210-ug-2   | 12210-user-8
 12210-ug-2   | 12210-user-9
 12210-ug-3   | 12210-user-0
 12210-ug-3   | 12210-user-1
 12210-ug-3   | 12210-user-2
 12210-ug-3   | 12210-user-3
 12210-ug-3   | 12210-user-4
 12210-ug-3   | 12210-user-5
 12210-ug-3   | 12210-user-6
 12210-ug-3   | 12210-user-7
 12210-ug-3   | 12210-user-8
 12210-ug-3   | 12210-user-9
 12210-ug-4   | 12210-user-0
 12210-ug-4   | 12210-user-1
 12210-ug-4   | 12210-user-2
 12210-ug-4   | 12210-user-3
 12210-ug-4   | 12210-user-4
 12210-ug-4   | 12210-user-5
 12210-ug-4   | 12210-user-6
 12210-ug-4   | 12210-user-7
 12210-ug-4   | 12210-user-8
 12210-ug-4   | 12210-user-9
(50 rows)

Update/Delete

icinga=> select cgo.name1 as contactgroup, co.name1 as contact from icinga_contacts c join icinga_objects co on c.contact_object_id=co.object_id join icinga_contactgroup_members cgm on c.contact_object_id=cgm.contact_object_id join icinga_contactgroups cg on cgm.contactgroup_id=cg.contactgroup_id join icinga_objects cgo on cg.contactgroup_object_id=cgo.object_id where cgo.name1 like '12210%' order by cgo.name1, co.name1;
 contactgroup |   contact
--------------+--------------
 12210-ug-0   | 12210-user-0
 12210-ug-0   | 12210-user-1
 12210-ug-0   | 12210-user-2
 12210-ug-0   | 12210-user-3
 12210-ug-0   | 12210-user-4
 12210-ug-1   | 12210-user-0
 12210-ug-1   | 12210-user-1
 12210-ug-1   | 12210-user-2
 12210-ug-1   | 12210-user-3
 12210-ug-1   | 12210-user-4
 12210-ug-2   | 12210-user-0
 12210-ug-2   | 12210-user-1
 12210-ug-2   | 12210-user-2
 12210-ug-2   | 12210-user-3
 12210-ug-2   | 12210-user-4
 12210-ug-3   | 12210-user-0
 12210-ug-3   | 12210-user-1
 12210-ug-3   | 12210-user-2
 12210-ug-3   | 12210-user-3
 12210-ug-3   | 12210-user-4
 12210-ug-4   | 12210-user-0
 12210-ug-4   | 12210-user-1
 12210-ug-4   | 12210-user-2
 12210-ug-4   | 12210-user-3
 12210-ug-4   | 12210-user-4
(25 rows)
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 25, 2016

Updated by mfriedrich on 2016-07-25 14:51:37 +00:00

Modified the session token capabilities to use the instance_id and the session token. Otherwise multiple instances wouldn't work. That involves an update to the custom variable table indexes as well.

[2016-07-25 16:47:48 +0200] debug/IdoMysqlConnection: Query: DELETE FROM icinga_customvariables WHERE instance_id = 1 AND session_token <> 1469458067
[2016-07-25 16:47:48 +0200] debug/IdoMysqlConnection: Query: DELETE FROM icinga_customvariablestatus WHERE instance_id = 1 AND session_token <> 1469458067
[2016-07-25 16:47:48 +0200] debug/IdoMysqlConnection: Query: DELETE FROM icinga_hostgroup_members WHERE instance_id = 1 AND session_token <> 1469458067
[2016-07-25 16:47:48 +0200] debug/IdoMysqlConnection: Query: DELETE FROM icinga_servicegroup_members WHERE instance_id = 1 AND session_token <> 1469458067
[2016-07-25 16:47:48 +0200] debug/IdoMysqlConnection: Query: DELETE FROM icinga_contactgroup_members WHERE instance_id = 1 AND session_token <> 1469458067

Furthermore the query priority default is modified to 'Normal' as otherwise the lower priority DELETE tasks might not be executed last after the member update queries (e.g. if they fail when an object is not yet created and re-inserted at the end of the queue). This also fixes the problem that the "finish connect" method is really executed last and provides the proper time duration.

This patch also incorporates a change with the upset logic allowing it for queries without object reference. This minimizes queries on program status updates as well.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 25, 2016

Updated by mfriedrich on 2016-07-25 14:54:30 +00:00

  • Relates set to 11280
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 25, 2016

Updated by mfriedrich on 2016-07-25 15:15:07 +00:00

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

Applied in changeset ce3d7ab.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 25, 2016

Updated by mfriedrich on 2016-07-25 15:34:29 +00:00

  • Relates set to 12200
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 9, 2016

Updated by mfriedrich on 2016-08-09 07:52:22 +00:00

  • Relates set to 10735
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.