[Workaround] Database character set broken

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:
ido_resource

When setting the Character Set to latin1 it worked out well:

ido_resource_latin1

Anyway … when you prefer CLI, you can also adjust the charset statement in the [icinga_ido] section of /etc/icingaweb2/resources.ini.

1 Like

You should change the ido characterset to latin1

Hi Carsten,

as you can see, it is exactly what I did. :slight_smile:

I bet your database characterset is not latin1. If your databse is not created with latin1 you will have problems. Übüntü and some other now dont use latin1 as default charset anymore.

Regards from the Philippines,
Carsten

1 Like

Okay … the schema used to create the database is /usr/share/icinga2-ido-mysql/schema/mysql.sql, let’s see what is used to create the tables:

# grep "character set" /usr/share/icinga2-ido-mysql/schema/mysql.sql | cut -d" "  -f5- | sort | uniq 
character set latin1,
character set latin1 collate latin1_general_cs  default '',
character set latin1 collate latin1_general_cs default NULL,
character set latin1  default '',
character set latin1 default NULL,
character set latin1  default NULL,

I think it’s latin1 used explicite, but ¯_(ツ)_/¯