Long running SQL query with Icingaweb 2.5.2

[gvde]

Expected Behavior

When viewing the page for a host (e.g. /icingaweb2/monitoring/host/show?host=cacti) it should show up and not hang and then time out.

Current Behavior

With 2.5.2 it hangs and times out. With 2.5.1 it did not.

Possible Solution

Workaround: downgrade back to 2.5.1

Steps to Reproduce (for bugs)

Upgrade to 2.5.2 and click on any host name in the dashboard.

Context

Upgrade to latest version of icingaweb2.

Your Environment

• Icinga Web 2 version and modules (System - About): 2.5.2
• Version used (icinga2 --version):

icinga2 - The Icinga 2 network monitoring daemon (version: r2.8.4-1)

Copyright (c) 2012-2017 Icinga Development Team (https://www.icinga.com/)
License GPLv2+: GNU GPL version 2 or later <http://gnu.org/licenses/gpl2.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

Application information:
  Installation root: /usr
  Sysconf directory: /etc
  Run directory: /run
  Local state directory: /var
  Package data directory: /usr/share/icinga2
  State path: /var/lib/icinga2/icinga2.state
  Modified attributes path: /var/lib/icinga2/modified-attributes.conf
  Objects path: /var/cache/icinga2/icinga2.debug
  Vars path: /var/cache/icinga2/icinga2.vars
  PID path: /run/icinga2/icinga2.pid

System information:
  Platform: CentOS Linux
  Platform version: 7 (Core)
  Kernel: Linux
  Kernel version: 3.10.0-693.21.1.el7.x86_64
  Architecture: x86_64

Build information:
  Compiler: GNU 4.8.5
  Build host: unknown

• Operating System and version: CentOS Linux release 7.4.1708 (Core)
• Enabled features (icinga2 feature list):

Disabled features: compatlog debuglog elasticsearch gelf influxdb livestatus opentsdb perfdata syslog
Enabled features: api checker command graphite ido-mysql mainlog notification statusdata

MariaDB Version:

mysql  Ver 15.1 Distrib 10.2.8-MariaDB, for Linux (x86_64) using  EditLine wrapper
rh-mariadb102-mariadb-10.2.8-5.el7.x86_64

Configured restrictions: (monitoring/filter/objects) None

Number of hosts and services: 329 hosts, 2780 services

Long running (~5 minutes) SQL query according to mariadb processlist:

SELECT co.name1 AS contact_name, c.alias COLLATE latin1_general_ci AS contact_alias, c.email_address COLLATE latin1_general_ci AS contact_email, c.pager_address AS contact_pager FROM icinga_contacts AS c  INNER JOIN icinga_objects AS co ON co.object_id = c.contact_object_id AND co.is_active = 1  LEFT JOIN icinga_service_contacts AS sc ON sc.contact_object_id = c.contact_object_id  LEFT JOIN icinga_services AS s ON s.service_id = sc.service_id  LEFT JOIN icinga_objects AS so ON so.object_id = s.service_object_id AND so.is_active = 1 AND so.objecttype_id = 2  LEFT JOIN icinga_host_contacts AS hc ON hc.contact_object_id = c.contact_object_id  LEFT JOIN icinga_hosts AS h ON h.host_id = hc.host_id  LEFT JOIN icinga_objects AS ho ON (ho.object_id = h.host_object_id OR ho.object_id = s.host_object_id) AND ho.is_active = 1 WHERE (ho.name1 = 'cacti') GROUP BY co.object_id, c.contact_id;

EXPLAIN of query:

+------+-------------+-------+--------+---------------------------+-------------------+---------+----------------------------+-------+---------------------------------------------------------------------+
| id   | select_type | table | type   | possible_keys             | key               | key_len | ref                        | rows  | Extra                                                               |
+------+-------------+-------+--------+---------------------------+-------------------+---------+----------------------------+-------+---------------------------------------------------------------------+
|    1 | SIMPLE      | ho    | ref    | PRIMARY,objects_name1_idx | objects_name1_idx | 131     | const                      |    16 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | c     | ALL    | contacts_object_id_idx    | NULL              | NULL    | NULL                       |    35 | Using where; Using join buffer (flat, BNL join)                     |
|    1 | SIMPLE      | co    | eq_ref | PRIMARY                   | PRIMARY           | 8       | icinga.c.contact_object_id |     1 | Using where                                                         |
|    1 | SIMPLE      | sc    | ALL    | NULL                      | NULL              | NULL    | NULL                       | 24081 | Using where; Using join buffer (flat, BNL join)                     |
|    1 | SIMPLE      | s     | eq_ref | PRIMARY                   | PRIMARY           | 8       | icinga.sc.service_id       |     1 | Using where                                                         |
|    1 | SIMPLE      | hc    | ALL    | NULL                      | NULL              | NULL    | NULL                       |  2619 | Using where; Using join buffer (flat, BNL join)                     |
|    1 | SIMPLE      | h     | eq_ref | PRIMARY                   | PRIMARY           | 8       | icinga.hc.host_id          |     1 | Using where                                                         |
+------+-------------+-------+--------+---------------------------+-------------------+---------+----------------------------+-------+---------------------------------------------------------------------+
7 rows in set (0.01 sec)

[dupondje]

Same issue here on Ubuntu 16.04. Host services overview is completely broken!

[kevinstiller]

Same issue over here. Fully updated CentOS 7 with new version. Had to restore from backup :-(

[dnsmichi]

Why not install 2.5.3 which has been released last week?