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?