Closed Rolf-Zi closed 5 years ago
mj84
commented
5 years ago Today I encountered the same problem, but this error seems to be related to users with specific filters (monitoring/filter/objects) set. I could not reproduce this issue with a user who has no filters set.
mj84
commented
5 years ago Seems to be a duplicate of #3564
Rolf-Zi
commented
5 years ago Yes, you are right. The problem occurs if I set a filter to a hostgroup:
cat roles.ini [Administrators] users = "admin" permissions = "*" monitoring/filter/objects = "hostgroup_name=Linux"
If I remove the filter, then it works.
lippserd
commented
5 years ago Hi,
I close this as duplicate of #3564. There's already a PR open which hopefully fixes this issue. Could you please test that and provide feedback.
Best, Eric
If I view the Overview -> Contacts I get an SQL error
Expected Behavior
We except to see the list of contacts
Current Behavior
Produces this SQL Error:
SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "ho" LINE 5: ..._hostgroup_members AS hgm ON hgm.host_objectid = ho.object... ^, query was: SELECT DISTINCT c.contact_name, c.contact_alias, c.contact_email, c.contact_pager, c.contact_notify_service_timeperiod, c.contact_notify_host_timeperiod FROM (SELECT c.contact_id, co.name1 AS contact, co.name1 AS contact_name, c.alias AS contact_alias, c.email_address AS contact_email, c.pager_address AS contact_pager, c.contact_object_id, c.host_notifications_enabled AS contact_has_host_notfications, c.service_notifications_enabled AS contact_has_service_notfications, c.can_submit_commands AS contact_can_submit_commands, c.notify_service_recovery AS contact_notify_service_recovery, c.notify_service_warning AS contact_notify_service_warning, c.notify_service_critical AS contact_notify_service_critical, c.notify_service_unknown AS contact_notify_service_unknown, c.notify_service_flapping AS contact_notify_service_flapping, c.notify_service_downtime AS contact_notify_service_downtime, c.notify_host_recovery AS contact_notify_host_recovery, c.notify_host_down AS contact_notify_host_down, c.notify_host_unreachable AS contact_notify_host_unreachable, c.notify_host_flapping AS contact_notify_host_flapping, c.notify_host_downtime AS contact_notify_host_downtime, ht.alias AS contact_notify_host_timeperiod, st.alias AS contact_notify_service_timeperiod 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_timeperiods AS ht ON ht.timeperiod_object_id = c.host_timeperiod_object_id LEFT JOIN icinga_timeperiods AS st ON st.timeperiod_object_id = c.service_timeperiod_object_id LEFT JOIN icinga_hostgroup_members AS hgm ON hgm.host_object_id = ho.object_id LEFT JOIN icinga_hostgroups AS hg ON hg.hostgroup_id = hgm.hostgroup_id LEFT JOIN icinga_objects AS hgo ON hgo.object_id = hg.hostgroup_object_id AND hgo.is_active = 1 AND hgo.objecttype_id = 3 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 AND ho.is_active = 1 LEFT JOIN icinga_services AS s ON s.host_object_id = ho.object_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_servicegroup_members AS sgm ON sgm.service_object_id = s.service_object_id LEFT JOIN icinga_servicegroups AS sg ON sg.servicegroup_id = sgm.servicegroup_id LEFT JOIN icinga_objects AS sgo ON sgo.object_id = sg.servicegroup_object_id AND sgo.is_active = 1 AND sgo.objecttype_id = 4 WHERE ( (TRUE OR (hgo.name1 = 'Linux' OR hgo.name1 = 'ESX') OR (hgo.name1 = 'Solaris' OR sgo.name1 = 'db2' OR sgo.name1 = 'innodb') ) ) GROUP BY co.object_id, c.contact_id, ht.timeperiod_id, st.timeperiod_id UNION ALL SELECT c.contact_id, co.name1 AS contact, co.name1 AS contact_name, c.alias AS contact_alias, c.email_address AS contact_email, c.pager_address AS contact_pager, c.contact_object_id, c.host_notifications_enabled AS contact_has_host_notfications, c.service_notifications_enabled AS contact_has_service_notfications, c.can_submit_commands AS contact_can_submit_commands, c.notify_service_recovery AS contact_notify_service_recovery, c.notify_service_warning AS contact_notify_service_warning, c.notify_service_critical AS contact_notify_service_critical, c.notify_service_unknown AS contact_notify_service_unknown, c.notify_service_flapping AS contact_notify_service_flapping, c.notify_service_downtime AS contact_notify_service_downtime, c.notify_host_recovery AS contact_notify_host_recovery, c.notify_host_down AS contact_notify_host_down, c.notify_host_unreachable AS contact_notify_host_unreachable, c.notify_host_flapping AS contact_notify_host_flapping, c.notify_host_downtime AS contact_notify_host_downtime, ht.alias AS contact_notify_host_timeperiod, st.alias AS contact_notify_service_timeperiod 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_timeperiods AS ht ON ht.timeperiod_object_id = c.host_timeperiod_object_id LEFT JOIN icinga_timeperiods AS st ON st.timeperiod_object_id = c.service_timeperiod_object_id LEFT JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id LEFT JOIN icinga_objects AS ho ON ho.object_id = h.host_object_id AND ho.is_active = 1 LEFT JOIN icinga_hostgroup_members AS hgm ON hgm.host_object_id = ho.object_id LEFT JOIN icinga_hostgroups AS hg ON hg.hostgroup_id = hgm.hostgroup_id LEFT JOIN icinga_objects AS hgo ON hgo.object_id = hg.hostgroup_object_id AND hgo.is_active = 1 AND hgo.objecttype_id = 3 LEFT JOIN icinga_servicegroup_members AS sgm ON sgm.service_object_id = s.service_object_id LEFT JOIN icinga_servicegroups AS sg ON sg.servicegroup_id = sgm.servicegroup_id LEFT JOIN icinga_objects AS sgo ON sgo.object_id = sg.servicegroup_object_id AND sgo.is_active = 1 AND sgo.objecttype_id = 4 WHERE ( (TRUE OR (hgo.name1 = 'Linux' OR hgo.name1 = 'ESX') OR (hgo.name1 = 'Solaris' OR sgo.name1 = 'db2' OR sgo.name1 = 'innodb') ) ) GROUP BY co.object_id, c.contact_id, ht.timeperiod_id, st.timeperiod_id) AS c ORDER BY c.contact_name ASC LIMIT 25
0 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute(Array)
1 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute(Array)
2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(String, Array)
3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(744): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
4 /usr/share/php/Icinga/Data/Db/DbConnection.php(313): Zend_Db_Adapter_Abstract->fetchRow(Object(Zend_Db_Select))
5 /usr/share/php/Icinga/Data/SimpleQuery.php(579): Icinga\Data\Db\DbConnection->fetchRow(Object(Icinga\Module\Monitoring\Backend\Ido\Query\ContactQuery))
6 /usr/share/php/Icinga/Data/SimpleQuery.php(455): Icinga\Data\SimpleQuery->fetchRow()
7 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(538): Icinga\Data\SimpleQuery->hasResult()
8 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/list/contacts.phtml(13): Icinga\Module\Monitoring\DataView\DataView->hasResult()
9 /usr/share/php/Icinga/Web/View.php(259): include(String)
10 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run(String)
11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render(NULL)
12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript(String, NULL)
13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(964): Zend_Controller_Action_Helper_ViewRenderer->render()
14 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
15 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
16 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
17 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
18 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
19 /usr/share/php/Icinga/Application/webrouter.php(104): Icinga\Application\Web->dispatch()
20 /usr/share/icingaweb2/public/index.php(4): require_once(String)
21 {main}
SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "ho" LINE 5: ..._hostgroup_members AS hgm ON hgm.host_objectid = ho.object... ^
0 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement/Pdo.php(219): PDOStatement->execute(Array)
1 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute(Array)
2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute(Array)
3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(String, Array)
4 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(744): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
5 /usr/share/php/Icinga/Data/Db/DbConnection.php(313): Zend_Db_Adapter_Abstract->fetchRow(Object(Zend_Db_Select))
6 /usr/share/php/Icinga/Data/SimpleQuery.php(579): Icinga\Data\Db\DbConnection->fetchRow(Object(Icinga\Module\Monitoring\Backend\Ido\Query\ContactQuery))
7 /usr/share/php/Icinga/Data/SimpleQuery.php(455): Icinga\Data\SimpleQuery->fetchRow()
8 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(538): Icinga\Data\SimpleQuery->hasResult()
9 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/list/contacts.phtml(13): Icinga\Module\Monitoring\DataView\DataView->hasResult()
10 /usr/share/php/Icinga/Web/View.php(259): include(String)
11 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run(String)
12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render(NULL)
13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript(String, NULL)
14 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(964): Zend_Controller_Action_Helper_ViewRenderer->render()
15 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
16 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
17 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
18 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
19 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
20 /usr/share/php/Icinga/Application/webrouter.php(104): Icinga\Application\Web->dispatch()
21 /usr/share/icingaweb2/public/index.php(4): require_once(String)
22 {main}
Possible Solution
With Version 2.5.3 it works. With V2.6.x I get the exception above.
I get the same error if I execute the query with the psql client. If I change the order of the "LEFT JOIN" statements the select query works (move "LEFT JOIN icinga_objects AS ho" up)
Steps to Reproduce (for bugs)
Context
Your Environment
icinga2 --version): r2.10.0-1icinga2 feature list): api checker command ido-pgsql mainlog notificationicinga2 daemon -C): OKzones.conffile (oricinga2 object list --type Endpointandicinga2 object list --type Zone) from all affected nodes.