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

Jump to bottom

[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
Closed

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

icinga-migration opened this issue Jul 22, 2016 · 8 comments
Labels
area/db-ido Database output blocker Blocks a release or needs immediate attention bug Something isn't working
Milestone
2.5.0

Comments

@icinga-migration
Copy link

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
Copy link
Author

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

  • Priority changed from Normal to High

@icinga-migration
Copy link
Author

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
Copy link
Author

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
Copy link
Author

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
Copy link
Author

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

  • Relates set to 11280

@icinga-migration
Copy link
Author

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
Copy link
Author

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

  • Relates set to 12200

@icinga-migration
Copy link
Author

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

  • Relates set to 10735

@icinga-migration icinga-migration added blocker Blocks a release or needs immediate attention bug Something isn't working area/db-ido Database output labels Jan 17, 2017
@icinga-migration icinga-migration added this to the 2.5.0 milestone Jan 17, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
area/db-ido Database output blocker Blocks a release or needs immediate attention bug Something isn't working
Projects
None yet
Milestone
2.5.0
Development

No branches or pull requests
1 participant
@icinga-migration