Icinga\Exception\ProgrammingError in IdoQuery.php:981 Got invalid column: host

[K0nne]

Today I found the following syslog entry:

2018 Aug  2 08:04:33 icingamaster01 user.err icingaweb2[158730]:Icinga\Exception\ProgrammingError in /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php:981 with message: Icinga\Module\Monitoring\Backend\Ido\Query\HostgroupQuery : Got invalid column: host
    #0 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php(675): Icinga\Module\Monitoring\Backend\Ido\Query\IdoQuery->requireColumn(String)
    #1 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php(695): Icinga\Module\Monitoring\Backend\Ido\Query\IdoQuery->requireFilterColumns(Object(Icinga\Data\Filter\FilterMatch))
    #2 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php(710): Icinga\Module\Monitoring\Backend\Ido\Query\IdoQuery->requireFilterColumns(Object(Icinga\Data\Filter\FilterOr))
    #3 /usr/share/php/Icinga/Data/SimpleQuery.php(274): Icinga\Module\Monitoring\Backend\Ido\Query\IdoQuery->addFilter(Object(Icinga\Data\Filter\FilterOr))
    #4 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/Backend/Ido/Query/HostgroupsummaryQuery.php(71): Icinga\Data\SimpleQuery->applyFilter(Object(Icinga\Data\Filter\FilterOr))
    #5 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(493): Icinga\Module\Monitoring\Backend\Ido\Query\HostgroupsummaryQuery->addFilter(Object(Icinga\Data\Filter\FilterOr)
    #6 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(432): Icinga\Module\Monitoring\DataView\DataView->addFilter(Object(Icinga\Data\Filter\FilterOr))
    #7 /usr/share/php/Icinga/Web/Widget/FilterEditor.php(352): Icinga\Module\Monitoring\DataView\DataView->applyFilter(Object(Icinga\Data\Filter\FilterOr))
    #8 /usr/share/php/Icinga/Web/Controller.php(254): Icinga\Web\Widget\FilterEditor->handleRequest(Object(Icinga\Web\Request))
    #9 /usr/share/icingaweb2/modules/monitoring/application/controllers/ListController.php(714): Icinga\Web\Controller->setupFilterControl(Object(Icinga\Module\Monitoring\DataView\Hostgroupsummary), NULL, NULL, Array)
    #10 /usr/share/icingaweb2/modules/monitoring/application/controllers/ListController.php(579): Icinga\Module\Monitoring\Controllers\ListController->filterQuery(Object(Icinga\Module\Monitoring\DataView\Hostgroupsummary))
    #11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(507): Icinga\Module\Monitoring\Controllers\ListController->hostgroupsAction()
    #12 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
    #13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
    #14 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
    #15 /usr/share/php/Icinga/Application/webrouter.php(104): Icinga\Application\Web->dispatch()
    #16 /usr/share/icingaweb2/public/index.

Expected Behavior

No errors ;)

Current Behavior

For all hosts which were crated prior to the update, their host custom vars are not shown in the host-tab. New hosts, which were created today, and alle the services are not affected.

Possible Solution

Steps to Reproduce (for bugs)

unknown so far

Context

Yesterday we updated our environment from Icinga2 2.8.2 -> 2.9.1 and Icinga Web2 2.5.1 -> 2.6.0.

Your Environment

• Icinga Web 2 version and modules (System - About):

Icinga Web 2 Version
2.6.0
Git Commit
cfe6c7b
Git Commit Datum
2018-07-19
Copyright
© 2013-2018 Das Icinga Projekt

businessprocess 2.1.0
monitoring 2.6.0
pnp 1.1.0

• Version used (icinga2 --version):
  icinga2 - The Icinga 2 network monitoring daemon (version: r2.9.1-1)

Copyright (c) 2012-2018 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: Red Hat Enterprise Linux Server
Platform version: 7.4 (Maipo)
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:
  RHEL 7.4

• Enabled features (icinga2 feature list):
  Disabled features: command compatlog debuglog elasticsearch gelf graphite influxdb livestatus mainlog opentsdb statusdata
  Enabled features: api checker ido-mysql notification perfdata syslog

• Config validation (icinga2 daemon -C):
  [2018-08-02 11:23:21 +0200] information/cli: Icinga application loader (version: r2.9.1-1)
  [2018-08-02 11:23:21 +0200] information/cli: Loading configuration file(s).
  [2018-08-02 11:23:23 +0200] information/ConfigItem: Committing config item(s).
  [2018-08-02 11:23:23 +0200] information/ApiListener: My API identity: icingamaster01
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 72495 Services.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 1 SyslogLogger.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 40 HostGroups.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 1 NotificationComponent.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 1 NotificationCommand.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 80782 Notifications.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 1 IcingaApplication.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 16876 Hosts.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 1 ApiListener.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 1 PerfdataWriter.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 1 CheckerComponent.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 13 Zones.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 18 Endpoints.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 4 ApiUsers.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 1 IdoMysqlConnection.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 248 CheckCommands.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 3 TimePeriods.
  [2018-08-02 11:23:28 +0200] information/ConfigItem: Instantiated 1 User.
  [2018-08-02 11:23:28 +0200] information/ScriptGlobal: Dumping variables to file '/var/cache/icinga2/icinga2.vars'
  [2018-08-02 11:23:28 +0200] information/cli: Finished validating the configuration file(s).

• If you run multiple Icinga 2 instances, the zones.conf file (or icinga2 object list --type Endpoint and icinga2 object list --type Zone) from all affected nodes.

[lippserd]

Hi,

Thanks for the report. I can confirm this issue.

Best,
Eric

[ekeih]

Hi,
we had the same issue. We thought it was related to the fact that our cluster died in a different way (Icinga/icinga2#6507) during the update.
But if you have the same problem, I guess it is an unrelated problem.

As a workaround we truncated the following tables:

1. Stop Icinga2.
2. Backup your database.
3. Truncate the tables:

mysql> truncate icinga_hosts;
Query OK, 0 rows affected (0.06 sec)

mysql> truncate icinga_services;
Query OK, 0 rows affected (0.13 sec)

mysql> truncate icinga_customvariables;
Query OK, 0 rows affected (0.31 sec)

4. Start Icinga2.
5. In our setup it took about 6 minutes for Icinga2 to write everything into the database again.

Best regards
Max

[ekeih]

@lippserd Do you think our workaround is a valid approach to "solve" the issue? We did this in our stage environment and until now it seems to work fine. Now we need to decide whether we update our live environment. So in case the same happens in our live environment, can we safely truncate the tables or would you advise to wait for a proper fix from you?
Thank you in advance!

Update: We had no issues during the update of our live environment.

[ekeih]

FYI: Icinga/icinga2#6569 and https://github.com/Icinga/icinga2/pull/6572/files include an official workaround.

[lippserd]

@ekeih Yes, the workaround is perfectly fine.

@K0nne This exception is thrown if an invalid column is used for filters in one of our views. In this case it is the host column in the hostgroups view. This is the case insensitive version of host_name and much slower in query execution. I doubt that this filter is of any real use in that view and so I'll close this.