Icingaweb2 SQLSTATE syntax error on modify a service for director

Icinga Web
, ,
1

This is a new icinga2 install with icingaweb2 that we’re populating to carry out a migration from an older instance.
Data is being populated into icinga2 via icingadirector.
Icingaweb2 authentication is setup to use backend=ldap and uses our ldap AD servers for authentication and group assignment.

I can login, browse, etc, all ok until I try to modify/view any service in icingadirector via icingaweb2 I get the following Mysql error:

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 '?) AND ((allowed_roles IS NULL OR allowed_roles LIKE '%\"Administrators\"%' OR a' at line 1, query was: SELECT director_datalist_entry.entry_name, director_datalist_entry.entry_value FROM director_datalist_entry WHERE (list_id = ?) AND ((allowed_roles IS NULL OR allowed_roles LIKE '%\"Administrators\"%' OR allowed_roles LIKE '%\"nops\"%')) ORDER BY entry_value ASC

The error seems to be relating to (list_id = ?) and suggests the question mark isn’t being substituted correctly in the code.
This happens regardless of which username I login as, if I use a local user (rather than an ldp user) the same error occurs so I don’t think it’s the ldap component itself, it seems to more be down to the list_id not being populated.

Below is the full output to try and help pinpoint which bit of code is failing here:

#0 /usr/share/icinga-php/vendor/vendor/shardj/zf1-future/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute()
#1 /usr/share/icinga-php/vendor/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute()
#2 /usr/share/icinga-php/vendor/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Pdo/Abstract.php(265): Zend_Db_Adapter_Abstract->query()
#3 /usr/share/icinga-php/vendor/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Abstract.php(814): Zend_Db_Adapter_Pdo_Abstract->query()
#4 /usr/share/icingaweb2/modules/director/library/Director/DataType/DataTypeDatalist.php(113): Zend_Db_Adapter_Abstract->fetchPairs()
#5 /usr/share/icingaweb2/modules/director/library/Director/DataType/DataTypeDatalist.php(30): Icinga\Module\Director\DataType\DataTypeDatalist->getEntries()
#6 /usr/share/icingaweb2/modules/director/library/Director/Objects/DirectorDatafield.php(213): Icinga\Module\Director\DataType\DataTypeDatalist->getFormElement()
#7 /usr/share/icingaweb2/modules/director/library/Director/Web/Form/IcingaObjectFieldLoader.php(380): Icinga\Module\Director\Objects\DirectorDatafield->getFormElement()
#8 /usr/share/icingaweb2/modules/director/library/Director/Web/Form/IcingaObjectFieldLoader.php(183): Icinga\Module\Director\Web\Form\IcingaObjectFieldLoader->createElements()
#9 /usr/share/icingaweb2/modules/director/library/Director/Web/Form/IcingaObjectFieldLoader.php(200): Icinga\Module\Director\Web\Form\IcingaObjectFieldLoader->getElements()
#10 /usr/share/icingaweb2/modules/director/library/Director/Web/Form/DirectorObjectForm.php(459): Icinga\Module\Director\Web\Form\IcingaObjectFieldLoader->prepareElements()
#11 /usr/share/icingaweb2/modules/director/library/Director/Web/Form/DirectorObjectForm.php(823): Icinga\Module\Director\Web\Form\DirectorObjectForm->prepareFields()
#12 /usr/share/icingaweb2/modules/director/library/Director/Web/Form/QuickForm.php(589): Icinga\Module\Director\Web\Form\DirectorObjectForm->onRequest()
#13 /usr/share/icingaweb2/modules/director/library/Director/Web/Form/QuickForm.php(603): Icinga\Module\Director\Web\Form\QuickForm->setRequest()
#14 /usr/share/icingaweb2/modules/director/library/Director/Web/Form/QuickForm.php(382): Icinga\Module\Director\Web\Form\QuickForm->getRequest()
#15 /usr/share/icingaweb2/modules/director/application/controllers/ServiceController.php(183): Icinga\Module\Director\Web\Form\QuickForm->handleRequest()
#16 /usr/share/icinga-php/vendor/vendor/shardj/zf1-future/library/Zend/Controller/Action.php(516): Icinga\Module\Director\Controllers\ServiceController->editAction()
#17 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch()
#18 /usr/share/icinga-php/vendor/vendor/shardj/zf1-future/library/Zend/Controller/Front.php(954): Icinga\Web\Controller\Dispatcher->dispatch()
#19 /usr/share/php/Icinga/Application/Web.php(294): Zend_Controller_Front->dispatch()
#20 /usr/share/php/Icinga/Application/webrouter.php(105): Icinga\Application\Web->dispatch()
#21 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#22 {main}
  • Icinga Web 2 version - 2.12.2
  • Used modules and their versions
Loaded Libraries
icinga/icinga-php-library	0.14.1
icinga/icinga-php-thirdparty	0.12.1
Loaded Modules
director		1.11.2
incubator		0.22.0
monitoring		2.12.2
pnp		1.0.1
pulsant-theme		1.0.1
  • Web browser used - Chrome
  • Icinga 2 version used (icinga2 --version) r2.14.3-1
  • PHP version used (php --version) 8.1.2-1ubuntu2.19
  • Server operating system and version Ubuntu 22.04.5
2

I’ve found the offending code which is:

/usr/share/icingaweb2/modules/director/library/Director/DataType/DataTypeDatalist.php

    protected function getEntries(QuickForm $form)
    {
        /** @var DirectorObjectForm $form */
        $db = $form->getDb()->getDbAdapter();
        $roles = Acl::instance()->listRoleNames();
        $select = $db->select()
            ->from('director_datalist_entry', ['entry_name', 'entry_value'])
            ->where('list_id = ?', $this->getSetting('datalist_id'))
            ->order('entry_value ASC');

The where statement here is not being populated correctly from datalist_id. Commenting this line out has allowed the page to be rendered.
checking, I have NO datalists defined at all in icinga director, so this perhaps is the issue?
I’ll copy this problem over to the director forum as I suspect it’s more director than it is icingaweb2.

Anyone else had this issue?

3

I can not remember having had such a problem, but I would recommend to open an Issue on