search

Icinga / icingaweb2-module-cube

Drill-down view for Icinga web 2 based on custom variables
GNU General Public License v2.0
45 stars 12 forks source link

near instant crash after installing module, SQL error #20

Closed RobBuijs closed 6 years ago

RobBuijs commented 7 years ago

Expected Behavior

After installing and enabeling the module I expect it to work.

Current Behavior

When I start using the module, it crashes as soon as I add a dimension.

Steps to Reproduce (for bugs)

  1. install module, enable.
  2. click reporting > cube
  3. Click "add dimension"
  4. I only have "os" in the drop-down.
  5. select "os"
  6. Crash
  7. error on screen:
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "WITH ROLLUP"
LINE 4: ...t_id GROUP BY c_os.varvalue) AS sub GROUP BY (os) WITH ROLLU...
^, query was: SELECT rollup.os, rollup.hosts_cnt, rollup.hosts_nok, rollup.hosts_unhandled_nok FROM (SELECT sub.os, SUM(hosts_cnt) AS hosts_cnt, SUM(hosts_nok) AS hosts_nok, SUM(hosts_unhandled_nok) AS hosts_unhandled_nok FROM (SELECT c_os.varvalue AS os, COUNT(*) AS hosts_cnt, SUM(CASE WHEN hs.current_state = 0 THEN 0 ELSE 1 END) AS hosts_nok, SUM(CASE WHEN hs.current_state != 0 AND hs.problem_has_been_acknowledged = 0 AND hs.scheduled_downtime_depth = 0 THEN 1 ELSE 0 END) AS hosts_unhandled_nok FROM icinga_objects AS o
INNER JOIN icinga_hosts AS h ON o.object_id = h.host_object_id AND o.is_active = 1
LEFT JOIN icinga_hoststatus AS hs ON hs.host_object_id = h.host_object_id
LEFT JOIN icinga_customvariablestatus AS c_os ON c_os.varname = 'os' AND c_os.object_id = o.object_id GROUP BY c_os.varvalue) AS sub GROUP BY (os) WITH ROLLUP) AS rollup ORDER BY (rollup.os IS NOT NULL) ASC, rollup.os ASC, (rollup.hosts_cnt IS NOT NULL) ASC, rollup.hosts_cnt ASC, (rollup.hosts_nok IS NOT NULL) ASC, rollup.hosts_nok ASC, (rollup.hosts_unhandled_nok IS NOT NULL) ASC, rollup.hosts_unhandled_nok ASC

#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(232): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Select), Array)
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(725): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#4 /usr/share/icingaweb2/modules/cube/library/Cube/DbCube.php(66): Zend_Db_Adapter_Abstract->fetchAll(Object(Zend_Db_Select))
#5 /usr/share/icingaweb2/modules/cube/library/Cube/CubeRenderer.php(175): Icinga\Module\Cube\DbCube->fetchAll()
#6 /usr/share/icingaweb2/modules/cube/library/Cube/Cube.php(290): Icinga\Module\Cube\CubeRenderer->render(Object(Icinga\Web\View))
#7 zend.view:///usr/share/icingaweb2/modules/cube/application/views/scripts/index/index.phtml(18): Icinga\Module\Cube\Cube->render(Object(Icinga\Web\View))
#8 /usr/share/php/Icinga/Web/View.php(231): include('zend.view:///us...')
#9 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run('/usr/share/icin...')
#10 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render('index/index.pht...')
#11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript('index/index.pht...', NULL)
#12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(964): Zend_Controller_Action_Helper_ViewRenderer->render()
#13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#14 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#15 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch('indexAction')
#16 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#17 /usr/share/php/Icinga/Application/Web.php(389): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#18 /usr/share/php/Icinga/Application/webrouter.php(109): Icinga\Application\Web->dispatch()
#19 /usr/share/icingaweb2/public/index.php(4): require_once('/usr/share/php/...')
#20 {main}

Your Environment

RobBuijs commented 7 years ago

Changed to the git version, I get this error:


LINE 4: ...object_id GROUP BY c_os.varvalue) AS sub GROUP BY ROLLUP (os...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts., query was: SELECT rollup.os, rollup.hosts_cnt, rollup.hosts_nok, rollup.hosts_unhandled_nok FROM (SELECT sub.os, SUM(hosts_cnt) AS hosts_cnt, SUM(hosts_nok) AS hosts_nok, SUM(hosts_unhandled_nok) AS hosts_unhandled_nok FROM (SELECT c_os.varvalue AS os, COUNT(*) AS hosts_cnt, SUM(CASE WHEN hs.current_state = 0 THEN 0 ELSE 1 END) AS hosts_nok, SUM(CASE WHEN hs.current_state != 0 AND hs.problem_has_been_acknowledged = 0 AND hs.scheduled_downtime_depth = 0 THEN 1 ELSE 0 END) AS hosts_unhandled_nok FROM icinga_objects AS o
INNER JOIN icinga_hosts AS h ON o.object_id = h.host_object_id AND o.is_active = 1
LEFT JOIN icinga_hoststatus AS hs ON hs.host_object_id = h.host_object_id
LEFT JOIN icinga_customvariablestatus AS c_os ON c_os.varname = 'os' AND c_os.object_id = o.object_id GROUP BY c_os.varvalue) AS sub GROUP BY ROLLUP (os)) AS rollup ORDER BY (rollup.os IS NOT NULL) ASC, rollup.os ASC, (rollup.hosts_cnt IS NOT NULL) ASC, rollup.hosts_cnt ASC, (rollup.hosts_nok IS NOT NULL) ASC, rollup.hosts_nok ASC, (rollup.hosts_unhandled_nok IS NOT NULL) ASC, rollup.hosts_unhandled_nok ASC

#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(232): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Select), Array)
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(725): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#4 /usr/share/icingaweb2/modules/cube/library/Cube/DbCube.php(66): Zend_Db_Adapter_Abstract->fetchAll(Object(Zend_Db_Select))
#5 /usr/share/icingaweb2/modules/cube/library/Cube/CubeRenderer.php(175): Icinga\Module\Cube\DbCube->fetchAll()
#6 /usr/share/icingaweb2/modules/cube/library/Cube/Cube.php(290): Icinga\Module\Cube\CubeRenderer->render(Object(Icinga\Web\View))
#7 zend.view:///usr/share/icingaweb2/modules/cube/application/views/scripts/index/index.phtml(18): Icinga\Module\Cube\Cube->render(Object(Icinga\Web\View))
#8 /usr/share/php/Icinga/Web/View.php(231): include('zend.view:///us...')
#9 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run('/usr/share/icin...')
#10 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render('index/index.pht...')
#11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript('index/index.pht...', NULL)
#12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(964): Zend_Controller_Action_Helper_ViewRenderer->render()
#13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#14 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#15 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch('indexAction')
#16 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#17 /usr/share/php/Icinga/Application/Web.php(389): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#18 /usr/share/php/Icinga/Application/webrouter.php(109): Icinga\Application\Web->dispatch()
#19 /usr/share/icingaweb2/public/index.php(4): require_once('/usr/share/php/...')
#20 {main}```
Thomas-Gelf commented 7 years ago

@RobBuijs: what database (and version) are you using?

RobBuijs commented 7 years ago

I'm running Postgres 9.4.12

(sorry for my late response!)

ghost commented 6 years ago

+1 - having the same issue

tagg commented 6 years ago

+1, same issue

voiprodrigo commented 6 years ago

ROLLUP requires pgsql 9.5 or above. https://www.postgresql.org/docs/9.5/static/release-9-5.html

dnsmichi commented 6 years ago

Discussion here: https://monitoring-portal.org/t/state-of-cube-module-sql-error-after-install/1541

Al2Klimov commented 6 years ago

Hello @RobBuijs and thank you for reporting!

I'm afraid @voiprodrigo is right and you need PgSQL >= v9.5 (see #24).

Best, AK