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.

Sign up for GitHub

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

Jump to bottom

near instant crash after installing module, SQL error #20

Closed
RobBuijs opened this issue Aug 8, 2017 · 8 comments
Closed

near instant crash after installing module, SQL error #20

RobBuijs opened this issue Aug 8, 2017 · 8 comments

Comments

@RobBuijs
Copy link

RobBuijs commented Aug 8, 2017 •
edited
Loading

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

  • Module version (System - About): 1.0
  • Icinga Web 2 version and modules (System - About): 2.4.1
  • Icinga 2 version (icinga2 --version): r2.7.0-1
  • Operating System and version: Debian 3.16.36-1+deb8u2
  • Webserver, PHP versions: Apache/2.4.10, PHP 5.6.30-0+deb8u1
  • Database: Postgres 9.4.12
@RobBuijs
Copy link
Author

RobBuijs commented Aug 8, 2017 •
edited
Loading

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
Copy link
Collaborator

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

@RobBuijs
Copy link
Author

RobBuijs commented Sep 7, 2017 •
edited
Loading

I'm running Postgres 9.4.12

(sorry for my late response!)

@ghost
Copy link

ghost commented Nov 14, 2017

+1 - having the same issue

@tagg
Copy link

tagg commented Jan 15, 2018

+1, same issue

@voiprodrigo
Copy link

voiprodrigo commented Mar 6, 2018 •
edited
Loading

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

@dnsmichi
Copy link

dnsmichi commented Mar 7, 2018

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

@Al2Klimov
Copy link
Member

Hello @RobBuijs and thank you for reporting!

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

Best,
AK

@mariuszkopowski mariuszkopowski mentioned this issue Apr 12, 2018
Closed
@ysijason ysijason mentioned this issue Jan 27, 2017
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
6 participants
@tagg @dnsmichi @Thomas-Gelf @voiprodrigo @Al2Klimov @RobBuijs