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

sql error #12

Closed ysijason closed 7 years ago

ysijason commented 7 years ago
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.varvalue AS operating system, COUNT(*) AS hosts_cnt, SUM(CASE WHEN hs.current_s' at line 1, query was: SELECT rollup.operating system, rollup.hosts_cnt, rollup.hosts_nok, rollup.hosts_unhandled_nok FROM (SELECT sub.operating system, SUM(hosts_cnt) AS hosts_cnt, SUM(hosts_nok) AS hosts_nok, SUM(hosts_unhandled_nok) AS hosts_unhandled_nok FROM (SELECT c_Operating System.varvalue AS operating system, 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_Operating System ON c_Operating System.varname = 'Operating System' AND c_Operating System.object_id = o.object_id GROUP BY c_Operating System.varvalue) AS sub GROUP BY (operating system) WITH ROLLUP) AS rollup ORDER BY (rollup.operating system IS NOT NULL) ASC, rollup.operating system 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

Guess this duplicates #6, otherwise please let me know. In the meantime please stay with lowercase vars for the Cube. This originated from a too-fast fix for a fix adding Icinga 1.x support :-/