Errors: SQL and web

[linuxmail]

hi,

I tried to test configure multiple hosts (e.G. downtime etc.) but I get errors:

• Modify

`Failed to load icinga_host "backup.4lin.net"

#0 /usr/share/icingaweb2/modules/director/library/Director/Data/Db/DbObject.php(1044): Icinga\Module\Director\Data\Db\DbObject->loadFromDb()
#1 /usr/share/icingaweb2/modules/director/library/Director/Objects/IcingaObject.php(2318): Icinga\Module\Director\Data\Db\DbObject::load('backup.4lin.net', Object(Icinga\Module\Director\Db))
#2 /usr/share/icingaweb2/modules/director/library/Director/Web/Controller/ObjectController.php(330): Icinga\Module\Director\Objects\IcingaObject::loadByType('host', 'backup.4lin.net', Object(Icinga\Module\Director\Db))
#3 /usr/share/icingaweb2/modules/director/library/Director/Web/Controller/ObjectController.php(32): Icinga\Module\Director\Web\Controller\ObjectController->loadObject()
#4 /usr/share/icingaweb2/modules/director/application/controllers/HostController.php(20): Icinga\Module\Director\Web\Controller\ObjectController->init()
#5 /usr/share/php/Icinga/Web/Controller/ActionController.php(133): Icinga\Module\Director\Controllers\HostController->init()
#6 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(59): Icinga\Web\Controller\ActionController->__construct(Object(Icinga\Web\Request), Object(Icinga\Web\Response), Array)
#7 /usr/share/php/Zend/Controller/Front.php(954): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#8 /usr/share/php/Icinga/Application/Web.php(384): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#9 /usr/share/php/Icinga/Application/webrouter.php(109): Icinga\Application\Web->dispatch()
#10 /usr/share/icingaweb2/public/index.php(4): require_once('/usr/share/php/...')
#11 {main}`

• Show host state

`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_hostname ON h.host_object_id = hcv_hostname.object_id AND hcv_hostname.varname = 'hostname' COLLATE latin1_general_ci WHERE (hcv_hostname.varvalue = 'backup') 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/php/Zend/Db/Statement.php(303): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/php/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#2 /usr/share/php/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Select), Array)
#3 /usr/share/php/Zend/Db/Adapter/Abstract.php(811): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#4 /usr/share/php/Icinga/Data/Db/DbConnection.php(298): 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/php/Zend/Controller/Action.php(516): Icinga\Module\Monitoring\Controllers\ListController->hostsAction()
#8 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch('hostsAction')
#9 /usr/share/php/Zend/Controller/Front.php(954): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#10 /usr/share/php/Icinga/Application/Web.php(384): 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}`

• icingaweb2 2.3.4+fix-1~debmon8+1
• icinga2 2.5.4-1~debmon8+4

[netzwerkgoettin]

Hi,

same here; for me, it is "COLLATE latin1_general_ci" in "LEFT JOIN icinga_customvariablestatus" that breaks this SQL statement - unfortunately, I cannot see how to remove it. Without COLLATE, everything seems to work fine...

Best regards,
Marianne

[Thomas-Gelf]

Are you using utf8 as an encoding for your IDO? Not facing any other issues? Or didn't you configure anything and are running on a system using utf8 as a default for all MySQL/MariaDB client connections?

I guess I could easily fix this by first checking the encoding defined for your DB resource. However, as most people have been running into issues with anything but latin1 for the IDO I didn't even expect to meet such. The DB itself should have no problems, but also the monitoring module could have some issues in your environment. Can you filter for custom variables over there?

[netzwerkgoettin]

All icinga2-ido-mysql tables have "latin1_swedish_ci" als "Collation" when checking via "SHOW TABLE STATUS"; this seems to be the default (using MariaDB 5.5.53-1ubuntu0.14.04.1), I never configured that by hand. Everything works fine (except writing äöüß in comments or scheduled downtimes). Hmmm.

[linuxmail]

hi,
same for me: latin1_swedish_ci and Debian Jessie with 10.0.28-0+deb8u1

cu denny

[bodsch]

Is this issue realy fixed?

I use the latest icingaweb2 Version (2.4.1) and the cube module from master and have already this Problem:

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_environment ON h.host_object_id = hcv_environment.object_id AND hcv_environment.varname = 'environment' COLLATE latin1_general_ci WHERE (hcv_environment.varvalue = 'development') 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/webapps/icingaweb2-2.4.1/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/webapps/icingaweb2-2.4.1/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute(Array)
#2 /usr/share/webapps/icingaweb2-2.4.1/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(232): Zend_Db_Adapter_Abstract->query('SELECT so.name1...', Array)
#3 /usr/share/webapps/icingaweb2-2.4.1/library/vendor/Zend/Db/Adapter/Abstract.php(799): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#4 /usr/share/webapps/icingaweb2-2.4.1/library/Icinga/Data/Db/DbConnection.php(316): Zend_Db_Adapter_Abstract->fetchPairs(Object(Zend_Db_Select))
#5 /usr/share/webapps/icingaweb2-2.4.1/library/Icinga/Data/SimpleQuery.php(626): Icinga\Data\Db\DbConnection->fetchPairs(Object(Icinga\Module\Monitoring\Backend\Ido\Query\HostserviceproblemsummaryQuery))
#6 /usr/share/webapps/icingaweb2-2.4.1/modules/monitoring/application/controllers/ListController.php(113): Icinga\Data\SimpleQuery->fetchPairs()
#7 /usr/share/webapps/icingaweb2-2.4.1/library/vendor/Zend/Controller/Action.php(507): Icinga\Module\Monitoring\Controllers\ListController->hostsAction()
#8 /usr/share/webapps/icingaweb2-2.4.1/library/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch('hostsAction')
#9 /usr/share/webapps/icingaweb2-2.4.1/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#10 /usr/share/webapps/icingaweb2-2.4.1/library/Icinga/Application/Web.php(389): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#11 /usr/share/webapps/icingaweb2-2.4.1/library/Icinga/Application/webrouter.php(109): Icinga\Application\Web->dispatch()
#12 /usr/share/webapps/icingaweb2-2.4.1/public/index.php(4): require_once('/usr/share/weba...')
#13 {main}

All Databases are created with UTF-8 (hey, its 2017! :) )
And i found many COLLATE latin1_general_ci fragments into icingaweb2 code.

[riponbanik]

I have resolved graphite plugin issue by modifying /etc/my.cnf.d/server.cnf. Please remember to restart mysqld (sudo service mysqld restart)

from
#character_set_server = utf8
#collation_server = utf8_general_ci

to
character_set_server = latin1
collation_server = latin1_general_ci

[bodsch]

this should work, but i think that's is the wrong way.
as background, all our databases are with utf8 character set.
the right way (and the hardest) is fix the sql statements in the icingaweb2 code.
but that's far from easy.

[Thomas-Gelf]

@bodsch: fixing those statements is easy, give me or one of my colleagues half an hour, and we are all done. Problem is that we did not want to do so, as for historic reasons the whole Nagios/Icinga ecosystem erroneously assumes to find UTF8-encoded data in a latin1-encoded database when talking to the IDO. This includes many tools not under our controls and for sure also tools we are not even aware of. Given that we wanted IcingaDB to be a thing as of today, last year we didn't want to waste too much time on IDO.

However, since some operating systems changed the default encoding for MariaDB, more and more people are facing issues as the Icinga/IDO schema doesn't specify an encoding at all - and therefore used to make wrong assumptions for a long time. Initially everything looks fine, but as soon as someone searches for custom variables, it breaks. It's not Icinga's fault, it existed before. We've had the chance to fix it, just - we didn't.

@lippserd: I have a pragmatic proposal, we could change the Icinga Web 2 monitoring module to adapt it's behavior based on DB resource character set configuration as follows:

• latin: apply the current workarounds
• utf8mb4: assume that the user knows what he is doing, that the DB has such encoding and that Icinga 2 lives on a system with a correct default client character set setting. Drop the workarounds and apply new ones where required for case insensitive filters combined with case sensitive columns
• utf8: well... the user might be wrong, we don't know. As it never really worked that way, and as utf8 (not utf8mb4) is historic bullshit in MySQL/MariaDB, we could then refuse to work, throw out a warning, tell the user to opt for either latin or real utf8 - whatever you prefer.

This could help us to get rid of this annoying problem. What do you think?

Cheers,
Thomas

[bodsch]

@Thomas-Gelf thanks for your explain!

my idea was not so simple:
I want take the character set from the configuration property and change the sql statements dynamicly. My first try was an disaster! :)
My PHP skills are not so good anymore. And before I produce any more problems or bugs, I prefer to keep my hands off it and let the pros get ahead of me.

[Thomas-Gelf]

@bodsch: it's probably tricky to fix all the details. As you can see here, ndoutils hard-coded some columns to latin1. It still looks like this, and this leaves users with a different default charset at schema creation time with a weird mix of encodings in their database. Checking only the connection setting doesn't help, as the DB will look different when you moved an older database to a newer DB version (or just dist-upgraded your system). It's a complete mess.

You can give the following patch a try:

--- a/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php
+++ b/modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php
@@ -701,6 +701,8 @@ abstract class IdoQuery extends DbQuery
             $this->initializeForOracle();
         } elseif ($dbType === 'pgsql') {
             $this->initializeForPostgres();
+        } elseif ($dbType === 'mysql') {
+            $this->initializeForMysql();
         }
         $this->joinBaseTables();
         $this->select->columns($this->columns);
@@ -1227,6 +1229,18 @@ abstract class IdoQuery extends DbQuery
         return self::$idoVersion;
     }
 
+    protected function initializeForMysql()
+    {
+        $config = $this->ds->getDbAdapter()->getConfig();
+        if (false !== strpos(
+                $config['driver_options'][\PDO::MYSQL_ATTR_INIT_COMMAND],
+                'utf8mb4'
+        )) {
+            $this->customVarsJoinTemplate =
+                '%1$s = %2$s.object_id AND LOWER(%2$s.varname) = %3$s';
+        }
+    }
+
     /**
      * Return the name of the primary key column for the given table name
      *

It will behave differently only when you explicitly set the DB resource configuration to utf8mb4.

Cheers,
Thomas

PS: I'll probably not be around for most of the day, as I'm enjoying my holidays... well... at least, I should 😆

PPS: Similar actions might be required elsewhere of for other columns, but this should at least fix one of the biggest problems: all queries joining custom variables. When facing more problems please check initializeForOracle() to get an idea of how to address them. Just: copying the same logic will not work. For example you cannot just replace latin1_general_ci with utf8_general_ci for columns with explicit latin1 encoding I guess. The evil ones are those with a collation defined in the schema, and unfortunately all of those have been added at Icinga times.

[Thomas-Gelf]

Update: I slightly changed above patch

[gjuric]

I am affected by this also, using MariaDB from Debian Stretch and installing Icinga2 from Icinga repository.

This is how my database was created:

CREATE TABLE `icinga_objects` (
  `object_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `instance_id` bigint(20) unsigned DEFAULT '0',
  `objecttype_id` bigint(20) unsigned DEFAULT '0',
  `name1` varchar(128) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT '',
  `name2` varchar(128) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
  `is_active` smallint(6) DEFAULT '0',
  PRIMARY KEY (`object_id`),
  KEY `objecttype_id` (`objecttype_id`,`name1`,`name2`),
  KEY `objects_objtype_id_idx` (`objecttype_id`),
  KEY `objects_name1_idx` (`name1`),
  KEY `objects_name2_idx` (`name2`),
  KEY `objects_inst_id_idx` (`instance_id`),
  KEY `sla_idx_obj` (`objecttype_id`,`is_active`,`name1`)
) ENGINE=InnoDB AUTO_INCREMENT=2711 DEFAULT CHARSET=utf8mb4 COMMENT='Current and historical objects of all kinds'

[maniac0s]

I have the same issue.

@Thomas-Gelf "utf8mb4: assume that the user knows what he is doing, that the DB has such encoding and that Icinga 2 lives on a system with a correct default client character set setting. Drop the workarounds and apply new ones where required for case insensitive filters combined with case sensitive columns"

I never set this charset. I used the script provided to create the table, never edited it by hand. Still my table is utf8mb4.

[Thomas-Gelf]

@maniac0s: I know. Linux distros / databases changed their defaults over time and NDO/IDO never cared about specifying an encoding for it's tables - it's a big mess. There is not much the cube can do about this, I'd suggest to add your feedback to related issue like #5412 (Icinga2) or #2508 (Icinga Web 2) - sorry for that.

I'll close this issue, because unless we duplicate a lot of logic this cannot be fixed in our add-on modules.

Cheers,
Thomas

NB: I'd love to reopen this when someone comes around with a better idea