Show Host Status returns DB Charset Error

[geotekberlin]

All databases have utf8 collation and are configured in icingaweb2 to use utf8 charset, but Show Host Status consistently throws errors similar to:

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8', query was: SELECT so.name1 AS host_name, SUM( CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, 0)) > 0 THEN 0 ELSE 1 END ) AS unhandled_service_count FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = 1
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id AND ss.current_state > 0
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN (SELECT ho.object_id FROM icinga_hosts AS h
INNER JOIN icinga_objects AS ho ON ho.object_id = h.host_object_id AND ho.is_active = 1 AND ho.objecttype_id = 1
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = ho.object_id
LEFT JOIN icinga_customvariablestatus AS hcv_disk_smb_hostname ON h.host_object_id = hcv_disk_smb_hostname.object_id AND hcv_disk_smb_hostname.varname = 'disk_smb_hostname' COLLATE latin1_general_ci WHERE (hcv_disk_smb_hostname.varvalue = 'u122205.your-storagebox.de') LIMIT 25) AS h ON h.object_id = s.host_object_id GROUP BY so.name1 HAVING (SUM(
CASE
WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, 0)) > 0
THEN 0
ELSE 1
END
) > 0)

#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(799): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#4 /usr/share/php/Icinga/Data/Db/DbConnection.php(316): Zend_Db_Adapter_Abstract->fetchPairs(Object(Zend_Db_Select))
#5 /usr/share/php/Icinga/Data/SimpleQuery.php(626): Icinga\Data\Db\DbConnection->fetchPairs(Object(Icinga\Module\Monitoring\Backend\Ido\Query\HostserviceproblemsummaryQuery))
#6 /usr/share/icingaweb2/modules/monitoring/application/controllers/ListController.php(113): Icinga\Data\SimpleQuery->fetchPairs()
#7 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(507): Icinga\Module\Monitoring\Controllers\ListController->hostsAction()
#8 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch('hostsAction')
#9 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#10 /usr/share/php/Icinga/Application/Web.php(389): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#11 /usr/share/php/Icinga/Application/webrouter.php(109): Icinga\Application\Web->dispatch()
#12 /usr/share/icingaweb2/public/index.php(4): require_once('/usr/share/php/...')
#13 {main}

I have no idea where latin1_general_ci comes into play. Any ideas?

[Thomas-Gelf]

I'm slightly confused - does this happen in the Icinga Web 2 monitoring module or when being in the Cube? And does the rest of Icinga Web 2 work fine? Host/service lists, choosing single hosts or services? Historically, the IDO has never been utf8, and it has not been treated as such.

Honestly I do not even know if that would really work without any issues :p All things we built from scratch are UTF8-only, but the IDO is something we inherited from looooong time ago. There will be a successor, but for now I'd strongly suggest to not work with UTF8 for that single DB resource.

@lippserd: did recently anything change in that relation? Do we support UTF8 for the IDO?

[geotekberlin]

Thomas, this error shows up only in the Icinga2 Web frontend after enabling the Cube module, adding an arbitraty dimension (doesn't matter which one), clicking on the Cube link to see more details and then clicking on "Show host status". Outside of the Cube menu item Icingaweb2 does not show any SQL errors whatsoever and works as expected.

[Thomas-Gelf]

Interesting, thank you! I'll try to reproduce this

[nilmerg]

This is a dupe of Icinga/icingaweb2#2508 which is fixed since Icinga Web 2 v2.6. Closed.