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'"
Statut:
Fermé
Priorité:
Faible
Assigné à:
Catégorie:
Supervision
Version cible:
Début:
20/01/2022
Echéance:
% réalisé:
100%
Temps estimé:
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.
Fichiers
Historique
Mis à jour par Pierre-Louis Bonicoli il y a plus d'un an
- Statut changé de Nouveau à Résolu
- Assigné à mis à Pierre-Louis Bonicoli
- % réalisé changé de 0 à 100
Le charset des bases icinga est 'utf8mb4':
MariaDB [(none)]> select * from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME like 'icinga%';
+--------------+--------------------+----------------------------+------------------------+----------+----------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | SCHEMA_COMMENT |
+--------------+--------------------+----------------------------+------------------------+----------+----------------+
| def | icinga2 | utf8mb4 | utf8mb4_general_ci | NULL | |
| def | icingaweb2 | utf8mb4 | utf8mb4_general_ci | NULL | |
+--------------+--------------------+----------------------------+------------------------+----------+----------------+
Sur la VM admin, les paramètres charset du fichier /icingaweb2/resources.ini ont été mis à jour et le service icinga2 a été redémarré (systemctl restart icinga2).
- L'interface web fonctionne toujours :)
- L'erreur mentionnée dans la description du ticket a bien disparu.
Mis à jour par Pierre-Louis Bonicoli il y a environ un an
- Version cible changé de Backlog à Sprint 2022 décembre