Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
icinga-migration opened this issue Apr 15, 2016 · 8 comments
Milestone

Comments

@icinga-migration
Copy link
Member

@icinga-migration icinga-migration commented Apr 15, 2016

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:

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 15, 2016

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

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

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 15, 2016

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

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 16, 2016

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

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 17, 2016

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

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 17, 2016

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

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 17, 2016

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

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 17, 2016

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

New packages should be available in 30 minutes :)

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 18, 2016

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

  • Relates set to 11603
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
1 participant
You can’t perform that action at this time.