Project

General

Profile

Demande #5758

icinga2: filtre sur un champ custom: erreur "SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8mb4'"

Added by Pierre-Louis Bonicoli 4 months ago.

Status:
Nouveau
Priority:
Faible
Assignee:
-
Category:
Supervision
Target version:
Start date:
01/20/2022
Due date:
% Done:

0%

Estimated time:

Description

En créant à partir de cette page un filtre sur la propriété Host Hote avec la valeur maine.cluster.chapril.org:

L'erreur ci-dessous apparaît après avoir cliqué sur le bouton Apply (lien):

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, ss.check_command AS service_check_command, CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN CASE ss.should_be_scheduled WHEN 1 THEN UNIX_TIMESTAMP(ss.next_check) + (ss.normal_check_interval * 60) ELSE NULL END ELSE UNIX_TIMESTAMP(ss.next_check) + (CASE WHEN COALESCE(ss.current_state, 0) > 0 AND ss.state_type = 0 THEN ss.retry_check_interval ELSE ss.normal_check_interval END * 60) + (CEIL(ss.execution_time + ss.latency) * 2) END AS service_next_update 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 hcv_hote ON s.host_object_id = hcv_hote.object_id AND hcv_hote.varname = 'hote' COLLATE latin1_general_ci WHERE (hcv_hote.varvalue = 'maine.cluster.chapril.org') ORDER BY s.display_name COLLATE latin1_general_ci ASC LIMIT 25

#0 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute()
#1 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute()
#2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query()
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(744): Zend_Db_Adapter_Pdo_Abstract->query()
#4 /usr/share/php/Icinga/Data/Db/DbConnection.php(349): Zend_Db_Adapter_Abstract->fetchRow()
#5 /usr/share/php/Icinga/Data/SimpleQuery.php(584): Icinga\Data\Db\DbConnection->fetchRow()
#6 /usr/share/php/Icinga/Data/SimpleQuery.php(460): Icinga\Data\SimpleQuery->fetchRow()
#7 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(538): Icinga\Data\SimpleQuery->hasResult()
#8 /usr/share/icingaweb2/modules/monitoring/application/views/scripts/list/services.phtml(18): Icinga\Module\Monitoring\DataView\DataView->hasResult()
#9 /usr/share/php/Icinga/Web/View.php(248): include(String)
#10 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run()
#11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render()
#12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript()
#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()
#17 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch()
#18 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch()
#19 /usr/share/php/Icinga/Application/webrouter.php(99): 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()
#1 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute()
#2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute()
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query()
#4 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(744): Zend_Db_Adapter_Pdo_Abstract->query()
#5 /usr/share/php/Icinga/Data/Db/DbConnection.php(349): Zend_Db_Adapter_Abstract->fetchRow()
#6 /usr/share/php/Icinga/Data/SimpleQuery.php(584): Icinga\Data\Db\DbConnection->fetchRow()
#7 /usr/share/php/Icinga/Data/SimpleQuery.php(460): Icinga\Data\SimpleQuery->fetchRow()
#8 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(538): Icinga\Data\SimpleQuery->hasResult()
#9 /usr/share/icingaweb2/modules/monitoring/application/views/scripts/list/services.phtml(18): Icinga\Module\Monitoring\DataView\DataView->hasResult()
#10 /usr/share/php/Icinga/Web/View.php(248): include(String)
#11 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run()
#12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render()
#13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript()
#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()
#18 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch()
#19 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch()
#20 /usr/share/php/Icinga/Application/webrouter.php(99): Icinga\Application\Web->dispatch()
#21 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#22 {main}

Cela ressemble à cette erreur https://github.com/Icinga/icingaweb2/issues/2392, la solution indiquée est de ne pas utiliser autre chose que latin1 au niveau de la connexion à la base de données.


Files

Screenshot_erreur_icinga.png (12 KB) Screenshot_erreur_icinga.png Pierre-Louis Bonicoli, 01/20/2022 01:48 AM

Also available in: Atom PDF