Error SQL Character Set Error 1253

If I use any of my custom vars to filter a search im running into this error:

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8mb4', query was: SELECT so.name1 AS host_name, h.display_name COLLATE latin1_general_ci AS host_display_name, CASE WHEN hs.has_been_checked = 0 OR hs.has_been_checked IS NULL THEN 99 ELSE hs.current_state END AS host_state, so.name2 AS service_description, s.display_name COLLATE latin1_general_ci AS service_display_name, CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 99 ELSE ss.current_state END AS service_state, CASE WHEN (ss.scheduled_downtime_depth = 0 OR ss.scheduled_downtime_depth IS NULL) THEN 0 ELSE 1 END AS service_in_downtime, ss.problem_has_been_acknowledged AS service_acknowledged, CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, 0)) > 0 THEN 1 ELSE 0 END AS service_handled, ss.output AS service_output, ss.perfdata AS service_perfdata, ss.current_check_attempt || '/' || ss.max_check_attempts AS service_attempt, UNIX_TIMESTAMP(ss.last_state_change) AS service_last_state_change, s.icon_image AS service_icon_image, s.icon_image_alt AS service_icon_image_alt, ss.is_flapping AS service_is_flapping, ss.state_type AS service_state_type, CASE WHEN ss.current_state = 0 THEN CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 16 ELSE 0 END + CASE WHEN ss.problem_has_been_acknowledged = 1 THEN 2 ELSE CASE WHEN ss.scheduled_downtime_depth > 0 THEN 1 ELSE 4 END END ELSE CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 16 WHEN ss.current_state = 1 THEN 32 WHEN ss.current_state = 2 THEN 128 WHEN ss.current_state = 3 THEN 64 ELSE 256 END + CASE WHEN hs.current_state > 0 THEN 1024 ELSE CASE WHEN ss.problem_has_been_acknowledged = 1 THEN 512 ELSE CASE WHEN ss.scheduled_downtime_depth > 0 THEN 256 ELSE 2048 END END END END AS service_severity, ss.notifications_enabled AS service_notifications_enabled, ss.active_checks_enabled AS service_active_checks_enabled, ss.passive_checks_enabled AS service_passive_checks_enabled FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = 1 AND so.objecttype_id = 2
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id
LEFT JOIN icinga_customvariablestatus AS scv_notificationtimes ON s.service_object_id = scv_notificationtimes.object_id AND scv_notificationtimes.varname = 'notificationtimes' COLLATE latin1_general_ci WHERE (scv_notificationtimes.varvalue = '24/7') ORDER BY s.display_name COLLATE latin1_general_ci ASC LIMIT 100

#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(String, Array)
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(744): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#4 /usr/share/php/Icinga/Data/Db/DbConnection.php(301): Zend_Db_Adapter_Abstract->fetchRow(Object(Zend_Db_Select))
#5 /usr/share/php/Icinga/Data/SimpleQuery.php(579): Icinga\Data\Db\DbConnection->fetchRow(Object(Icinga\Module\Monitoring\Backend\Ido\Query\ServicestatusQuery))
#6 /usr/share/php/Icinga/Data/SimpleQuery.php(455): Icinga\Data\SimpleQuery->fetchRow()
#7 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(538): Icinga\Data\SimpleQuery->hasResult()
#8 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/list/services.phtml(17): Icinga\Module\Monitoring\DataView\DataView->hasResult()
#9 /usr/share/php/Icinga/Web/View.php(270): include(String)
#10 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run(String)
#11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render(NULL)
#12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript(String, NULL)
#13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(964): Zend_Controller_Action_Helper_ViewRenderer->render()
#14 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#15 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#16 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#17 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#18 /usr/share/php/Icinga/Application/Web.php(409): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#19 /usr/share/php/Icinga/Application/webrouter.php(104): Icinga\Application\Web->dispatch()
#20 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#21 {main}
SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8mb4'

#0 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement/Pdo.php(219): PDOStatement->execute(Array)
#1 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute(Array)
#2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute(Array)
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(232): Zend_Db_Adapter_Abstract->query(String, Array)
#4 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(744): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#5 /usr/share/php/Icinga/Data/Db/DbConnection.php(301): Zend_Db_Adapter_Abstract->fetchRow(Object(Zend_Db_Select))
#6 /usr/share/php/Icinga/Data/SimpleQuery.php(579): Icinga\Data\Db\DbConnection->fetchRow(Object(Icinga\Module\Monitoring\Backend\Ido\Query\ServicestatusQuery))
#7 /usr/share/php/Icinga/Data/SimpleQuery.php(455): Icinga\Data\SimpleQuery->fetchRow()
#8 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(538): Icinga\Data\SimpleQuery->hasResult()
#9 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/list/services.phtml(17): Icinga\Module\Monitoring\DataView\DataView->hasResult()
#10 /usr/share/php/Icinga/Web/View.php(270): include(String)
#11 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run(String)
#12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render(NULL)
#13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript(String, NULL)
#14 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(964): Zend_Controller_Action_Helper_ViewRenderer->render()
#15 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#16 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#17 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#18 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#19 /usr/share/php/Icinga/Application/Web.php(409): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#20 /usr/share/php/Icinga/Application/webrouter.php(104): Icinga\Application\Web->dispatch()
#21 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#22 {main}

As far as I can understand it is a problem with the Character Set but I really don’t have a clue what to do about.

Using these vars for applying notification rules for example is working just fine.
So it seems to just affect the usage in Icingaweb2.

Any hints on where to look for a solution?

I am running my setup with the latest stable versions of Icinga2, Icingaweb2 and the Director:

OS is Ubuntu 16.04 LTS (all packages up to date) and MariaDB for MySQL

I would really appreciate to understand what is causing the error and how to solve it myself :slight_smile:

Don’t set the charset for the IDO database resource, I believe yours is set to ‘utf8’ or similar.

Yes it was set to ‘utf8mb4’

No I left it blank and restarted the Icinga2 Service, Signed out and in again but the error message didn’t change. :frowning:

You need to also change the encoding on the table itself so that it’s latin1. (And just to be sure, what @dnsmichi meant was the resource configuration in Icinga Web 2, not Icinga 2.)

Alternatively you may also try out the changes proposed here:

With these you don’t need to change anything else. (You can also keep the charset in the resource configuration.)

Thanks a lot for the help @dnsmichi & @nilmerg it seems to be working now.

I changed the charset and collation using these commands in mysql:
ALTER DATABASE icinga COLLATE = 'latin1_general_ci';
&
ALTER DATABASE icinga CHARACTER SET = ‘latin1’;

But this wasn’t quite enough since it is also required to enter ‘latin1’ here:
image

If someone could confirm that all of what I wrote above is fine I would mark this as the answer.

I’m not entirely sure, but be aware that you may have only changed the default charset and collation. Already existing tables may still have the previous charset and collation.

Setting the charset in the resource configuration is not necessarily required unless the database and all of its tables/columns got latin1.

However, if you really need to change that much on your database I really recommend the pull request mentioned above. With this you don’t need to adjust anything.

1 Like