[dev.icinga.com #11598] Invalid SQL queries for PostgreSQL #2390

icinga-migration · 2016-04-15T09:40:07Z

This issue has been migrated from Redmine: https://dev.icinga.com/issues/11598

Created by greatexpectations on 2016-04-15 09:40:07 +00:00

Assignee: (none)
Status: Resolved (closed on 2016-04-17 23:03:58 +00:00)
Target Version: 2.3.1
Last Update: 2016-04-17 23:09:37 +00:00 (in Redmine)

Hi,

Icingaweb: 2.3.0 rpm packages from Icinga RHEL repository
Database: PostgreSQL 9.2
OS: CentOS 7 x64

Clicking on either hostnames or service names from any list view (e.g. Overview -> Hosts, Overview -> Services, etc.) results in the following error message:

For hosts:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "cgo.name1" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT cgo.name1 AS contactgroup_name, cg.alias AS contactgr...
^, query was: SELECT cgo.name1 AS contactgroup_name, cg.alias AS contactgroup_alias FROM icinga_contactgroups AS cg
INNER JOIN icinga_objects AS cgo ON cgo.object_id = cg.contactgroup_object_id AND cgo.is_active = 1 AND cgo.objecttype_id = 11
LEFT JOIN icinga_host_contactgroups AS hcg ON hcg.contactgroup_object_id = cg.contactgroup_object_id
LEFT JOIN icinga_hosts AS h ON h.host_id = hcg.host_id
LEFT JOIN icinga_objects AS ho ON ho.object_id = h.host_object_id AND ho.is_active = 1 AND ho.objecttype_id = 1 WHERE (ho.name1 = 'icinga-test') GROUP BY cg.contactgroup_id


#0 /usr/share/php/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/php/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#2 /usr/share/php/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Select), Array)
#3 /usr/share/php/Zend/Db/Adapter/Abstract.php(756): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#4 /usr/share/php/Icinga/Data/Db/DbConnection.php(260): Zend_Db_Adapter_Abstract->fetchRow(Object(Zend_Db_Select))
#5 /usr/share/php/Icinga/Data/SimpleQuery.php(577): Icinga\Data\Db\DbConnection->fetchRow(Object(Icinga\Module\Monitoring\Backend\Ido\Query\ContactgroupQuery))
#6 /usr/share/php/Icinga/Data/SimpleQuery.php(453): Icinga\Data\SimpleQuery->fetchRow()
#7 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(539): Icinga\Data\SimpleQuery->hasResult()
#8 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/show/components/contacts.phtml(22): Icinga\Module\Monitoring\DataView\DataView->hasResult()
#9 /usr/share/php/Icinga/Web/View.php(204): include('zend.view:///us...')
#10 /usr/share/php/Zend/View/Abstract.php(888): Icinga\Web\View->_run('/usr/share/icin...')
#11 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/partials/object/detail-content.phtml(28): Zend_View_Abstract->render('show/components...')
#12 /usr/share/php/Icinga/Web/View.php(204): include('zend.view:///us...')
#13 /usr/share/php/Zend/View/Abstract.php(888): Icinga\Web\View->_run('/usr/share/icin...')
#14 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/host/show.phtml(15): Zend_View_Abstract->render('partials/object...')
#15 /usr/share/php/Icinga/Web/View.php(204): include('zend.view:///us...')
#16 /usr/share/php/Zend/View/Abstract.php(888): Icinga\Web\View->_run('/usr/share/icin...')
#17 /usr/share/php/Zend/Controller/Action/Helper/ViewRenderer.php(912): Zend_View_Abstract->render('host/show.phtml')
#18 /usr/share/php/Zend/Controller/Action/Helper/ViewRenderer.php(933): Zend_Controller_Action_Helper_ViewRenderer->renderScript('host/show.phtml', NULL)
#19 /usr/share/php/Zend/Controller/Action/Helper/ViewRenderer.php(972): Zend_Controller_Action_Helper_ViewRenderer->render()
#20 /usr/share/php/Zend/Controller/Action/HelperBroker.php(277): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#21 /usr/share/php/Zend/Controller/Action.php(527): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#22 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch('showAction')
#23 /usr/share/php/Zend/Controller/Front.php(954): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#24 /usr/share/php/Icinga/Application/Web.php(384): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#25 /usr/share/php/Icinga/Application/webrouter.php(109): Icinga\Application\Web->dispatch()
#26 /usr/share/icingaweb2/public/index.php(4): require_once('/usr/share/php/...')
#27 {main}

For services:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "cgo.name1" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT cgo.name1 AS contactgroup_name, cg.alias AS contactgr...
^, query was: SELECT cgo.name1 AS contactgroup_name, cg.alias AS contactgroup_alias FROM icinga_contactgroups AS cg
INNER JOIN icinga_objects AS cgo ON cgo.object_id = cg.contactgroup_object_id AND cgo.is_active = 1 AND cgo.objecttype_id = 11
LEFT JOIN icinga_service_contactgroups AS scg ON scg.contactgroup_object_id = cg.contactgroup_object_id
LEFT JOIN icinga_services AS s ON s.service_id = scg.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 WHERE (so.name1 = 'icinga-test' AND so.name2 = 'disk') GROUP BY cg.contactgroup_id


#0 /usr/share/php/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/php/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#2 /usr/share/php/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Select), Array)
#3 /usr/share/php/Zend/Db/Adapter/Abstract.php(756): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#4 /usr/share/php/Icinga/Data/Db/DbConnection.php(260): Zend_Db_Adapter_Abstract->fetchRow(Object(Zend_Db_Select))
#5 /usr/share/php/Icinga/Data/SimpleQuery.php(577): Icinga\Data\Db\DbConnection->fetchRow(Object(Icinga\Module\Monitoring\Backend\Ido\Query\ContactgroupQuery))
#6 /usr/share/php/Icinga/Data/SimpleQuery.php(453): Icinga\Data\SimpleQuery->fetchRow()
#7 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(539): Icinga\Data\SimpleQuery->hasResult()
#8 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/show/components/contacts.phtml(22): Icinga\Module\Monitoring\DataView\DataView->hasResult()
#9 /usr/share/php/Icinga/Web/View.php(204): include('zend.view:///us...')
#10 /usr/share/php/Zend/View/Abstract.php(888): Icinga\Web\View->_run('/usr/share/icin...')
#11 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/partials/object/detail-content.phtml(28): Zend_View_Abstract->render('show/components...')
#12 /usr/share/php/Icinga/Web/View.php(204): include('zend.view:///us...')
#13 /usr/share/php/Zend/View/Abstract.php(888): Icinga\Web\View->_run('/usr/share/icin...')
#14 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/service/show.phtml(8): Zend_View_Abstract->render('partials/object...')
#15 /usr/share/php/Icinga/Web/View.php(204): include('zend.view:///us...')
#16 /usr/share/php/Zend/View/Abstract.php(888): Icinga\Web\View->_run('/usr/share/icin...')
#17 /usr/share/php/Zend/Controller/Action/Helper/ViewRenderer.php(912): Zend_View_Abstract->render('service/show.ph...')
#18 /usr/share/php/Zend/Controller/Action/Helper/ViewRenderer.php(933): Zend_Controller_Action_Helper_ViewRenderer->renderScript('service/show.ph...', NULL)
#19 /usr/share/php/Zend/Controller/Action/Helper/ViewRenderer.php(972): Zend_Controller_Action_Helper_ViewRenderer->render()
#20 /usr/share/php/Zend/Controller/Action/HelperBroker.php(277): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#21 /usr/share/php/Zend/Controller/Action.php(527): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#22 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch('showAction')
#23 /usr/share/php/Zend/Controller/Front.php(954): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#24 /usr/share/php/Icinga/Application/Web.php(384): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#25 /usr/share/php/Icinga/Application/webrouter.php(109): Icinga\Application\Web->dispatch()
#26 /usr/share/icingaweb2/public/index.php(4): require_once('/usr/share/php/...')
#27 {main}

The error message seems about right; you cannot select regular columns or scalar values when using the GROUP BY clause.

Regards

Changesets

2016-04-17 23:01:26 +00:00 by elippmann f66daa6

Fix unwanted change of ContactgroupQuery::$groupBase

Oops!

fixes #11598

Relations:

relates #11598

The text was updated successfully, but these errors were encountered:

icinga-migration · 2016-04-15T09:44:45Z

Updated by mfriedrich on 2016-04-15 09:44:45 +00:00

https://twitter.com/snet84/status/720719299070070784

icinga-migration · 2016-04-15T13:37:19Z

Updated by jandd on 2016-04-15 13:37:19 +00:00

I changed $groupBase in modules/monitoring/library/Monitoring/Backend/Ido/Query/ContactgroupQuery.php to:

    protected $groupBase = array(                                                 
        'contactgroups' => array('cg.contactgroup_id'),                                                                                 
        'contactgroup' => array('cgo.name1'));

which fixed this issue in our icingaweb2/PostgreSQL setup. The issue has been introduced in 2.3.0.

icinga-migration · 2016-04-16T09:03:42Z

Updated by greatexpectations on 2016-04-16 09:03:42 +00:00

jandd wrote:

I changed $groupBase in modules/monitoring/library/Monitoring/Backend/Ido/Query/ContactgroupQuery.php to:

[...]

which fixed this issue in our icingaweb2/PostgreSQL setup. The issue has been introduced in 2.3.0.

I can confirm that the above change fixes the issue for our environment as well.

Since this issue made it undetected into a release RPM, I guess that PostgreSQL is a second-tier db platform for development of Icingaweb2. I think that is unfortunate because PostgreSQL is actually pretty compliant with SQL standards (think SQL-92, SQL:1999, SQL:2003 etc.) whereas MySQL is notoriously not enforcing compliant (and thus, more easily portable) SQL code. Maybe some automated tests using PostgreSQL could be incorportated into the build process to counter that tendency?

icinga-migration · 2016-04-17T23:03:53Z

Updated by elippmann on 2016-04-17 23:03:53 +00:00

Subject changed from Icinga Web 2.3.0: invalid SQL queries for PostgreSQL to Invalid SQL queries for PostgreSQL
Target Version set to 2.3.1

Hi all,

Sorry for that bug. We get a bugfix release on its way asap.

Best regards,
Eric

icinga-migration · 2016-04-17T23:03:58Z

Updated by elippmann on 2016-04-17 23:03:58 +00:00

Status changed from New to Resolved
Done % changed from 0 to 100

Applied in changeset f66daa6.

icinga-migration · 2016-04-17T23:05:52Z

Updated by purplecarrot on 2016-04-17 23:05:52 +00:00

@lippser Thanks, is the new release scheduled soon or should we patch the existing 2.3.0 packages?

icinga-migration · 2016-04-17T23:09:37Z

Updated by elippmann on 2016-04-17 23:09:37 +00:00

New packages should be available in 30 minutes :)

icinga-migration · 2016-04-18T07:06:29Z

Updated by mfriedrich on 2016-04-18 07:06:29 +00:00

Relates set to 11603

icinga-migration closed this as completed Apr 17, 2016

icinga-migration added bug Something isn't working area/monitoring Affects the monitoring module labels Jan 17, 2017

icinga-migration added this to the 2.3.1 milestone Jan 17, 2017

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[dev.icinga.com #11598] Invalid SQL queries for PostgreSQL #2390

[dev.icinga.com #11598] Invalid SQL queries for PostgreSQL #2390

icinga-migration commented Apr 15, 2016

icinga-migration commented Apr 15, 2016

icinga-migration commented Apr 15, 2016

icinga-migration commented Apr 16, 2016

icinga-migration commented Apr 17, 2016

icinga-migration commented Apr 17, 2016

icinga-migration commented Apr 17, 2016

icinga-migration commented Apr 17, 2016

icinga-migration commented Apr 18, 2016

[dev.icinga.com #11598] Invalid SQL queries for PostgreSQL #2390

[dev.icinga.com #11598] Invalid SQL queries for PostgreSQL #2390

Comments

icinga-migration commented Apr 15, 2016

icinga-migration commented Apr 15, 2016

icinga-migration commented Apr 15, 2016

icinga-migration commented Apr 16, 2016

icinga-migration commented Apr 17, 2016

icinga-migration commented Apr 17, 2016

icinga-migration commented Apr 17, 2016

icinga-migration commented Apr 17, 2016

icinga-migration commented Apr 18, 2016