While trying to get the Graphite Icinga Web 2 Module to work I faced the issue, that everything seems to work quite nicely, beside that I wasn’t able to see the performance graphs.
Yes I did verify that the graphite web entry point provided the correct data. The Troubleshooting section of the Graphite Icinga Web 2 Module was very helpful here.
Anyway … at some point I inspected the not showing “pictures” and found out that when calling the URL I’m getting back:
SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8mb4', query was: SELECT hs.check_command AS host_check_command, hcv_check_command.varvalue AS _host_check_command FROM icinga_objects AS ho
INNER JOIN icinga_hosts AS h ON h.host_object_id = ho.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_check_command ON h.host_object_id = hcv_check_command.object_id AND hcv_check_command.varname = 'check_command' COLLATE latin1_general_ci WHERE (ho.name1 = 'localhost.test.org') ORDER BY h.display_name COLLATE latin1_general_ci ASC LIMIT 1
Seems I was facing with character hell of MySQL. So I digged a bit around:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 63839
Server version: 10.3.17-MariaDB-0+deb10u1 Debian 10
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [icinga2]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+--------------------------+--------------------+
10 rows in set (0.002 sec)
MariaDB [icinga2]> select * from icinga_dbversion;
+--------------+----------+---------+---------------------+---------------------+
| dbversion_id | name | version | create_time | modify_time |
+--------------+----------+---------+---------------------+---------------------+
| 1 | idoutils | 1.15.0 | 2018-11-28 11:51:50 | 2019-09-23 11:46:24 |
+--------------+----------+---------+---------------------+---------------------+
1 row in set (0.000 sec)
# grep ^PRETTY /etc/os-release
PRETTY_NAME="Debian GNU/Linux 10 (buster)"
# dpkg -l | grep -E "(mariadb-server|icinga)" | awk '{print $2 "\t " $3}'
icinga2 2.11.0-2.buster
icinga2-bin 2.11.0-2.buster
icinga2-common 2.11.0-2.buster
icinga2-ido-mysql 2.11.0-2.buster
icingacli 2.7.1-1.buster
icingaweb2 2.7.1-1.buster
icingaweb2-common 2.7.1-1.buster
icingaweb2-module-monitoring 2.7.1-1.buster
mariadb-server-10.3 1:10.3.17-0+deb10u1
mariadb-server-core-10.3 1:10.3.17-0+deb10u1
php-icinga 2.7.1-1.buster
vim-icinga2 2.11.0-2.buster
# grep "character set" /usr/share/icinga2-ido-mysql/schema/mysql.sql | grep -v latin1 | wc -l
0
# grep "character set" /usr/share/icinga2-ido-mysql/schema/mysql.sql | grep latin1 | wc -l
112
I found out that the IDO Resource in Icingaweb2 has not set a Character Set on default:
When setting the Character Set to latin1 it worked out well:
Anyway … when you prefer CLI, you can also adjust the charset statement in the [icinga_ido] section of /etc/icingaweb2/resources.ini.
