Hi everyone,
we have a problem with umlauts in the names of persons. In the import of the director everything looks well but in the contact details it doesn’t work.
That’s my ressource config from icinga web2:
Icinga Web 2 Version is 2.6.3
Has anybody an idea what the reason could be?
Alicia
Hi,
how’s the IDO database resource configured?
How does the data look when you connect to mysql on the CLI and execute this query? (replace CONTACTNAME)
select * from icinga_contacts c join icinga_objects co on c.contact_object_id=co.object_id where co.name1='CONTACTNAME'\G
Cheers,
Michael
Hi,
good point to look at. The output of the query shows the same problem like the webinterface, so I should to search there.
The configuration of the IDO lookks like this:
and the charset of the mysql database like this:
Thanks for the help!
Hmmm try removing the charset in the IDO resource whether this changes anything.
Cheers,
Michael
Hi,
the interface shows correct what is in the database. Changing the charset of the IDO didn’t changed anything. We have checked now, that the database can show the charactes correct:
At the moment it seems to be a problem of the import of the …/master/users.conf to the IDO. The users.conf file everything is correct.
I manually changed a user in the ido which now is displayed correct in the database and in the webinterface:
MariaDB [icinga]> select contact_id, alias, email_address from icinga_contacts where email_address like '%test%';
+------------+--------------+----------------+
| contact_id | alias | email_address |
+------------+--------------+----------------+
| 546 | ÄaÜuÖoßs | test@ |
| 547 | äüö | test@ |
+------------+--------------+----------------+
Hi,
UTF8 and IDO is like … meh.
I’ve just tested this here with 2.11.0-rc1, which works without problems.
object Host "äöü" {
check_command = "dummy"
display_name = "üöä"
}
object User "öüä" {
display_name = "üäö"
}
$ mysql -V
mysql Ver 15.1 Distrib 10.4.6-MariaDB, for osx10.14 (x86_64) using readline 5.1
$ mysql icinga -e "show variables like '%character%';"
+--------------------------+--------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/Cellar/mariadb/10.4.6/share/mysql/charsets/ |
+--------------------------+--------------------------------------------------------+
$ cat /usr/local/icinga/icinga2/etc/icinga2/features-enabled/ido-mysql.conf
/**
* The IdoMysqlConnection type implements MySQL support
* for DB IDO.
*/
object IdoMysqlConnection "ido-mysql" {
//user = "icinga"
//password = "icinga"
//host = "localhost"
//database = "icinga"
}
$ cat /usr/local/icinga/icingaweb2/etc/resources.ini
[icingaweb_db]
type = "db"
db = "mysql"
host = "localhost"
port = ""
dbname = "icingaweb2"
username = "icingaweb2"
password = "icingaweb2"
charset = ""
use_ssl = "0"
[icinga_ido]
type = "db"
db = "mysql"
host = "localhost"
port = ""
dbname = "icinga"
username = "icinga"
password = "icinga"
charset = ""
use_ssl = "0"
Which Icinga 2 version are you using: icinga2 --version
Also, which schema version is involed: mysql icinga -e 'select * from icinga_dbversion;'
The client settings and its character set might be interesting as well.
Please also post your findings as text with enclosed 3 backticks rather than screenshots. Then we can copy-paste stuff instead of typing this again.
One last shot - we’ve replaced our UTF8 validation and escaping library with 2.11, this may have an influence here as well. https://github.com/Icinga/icinga2/pull/7014
Cheers,
Michael
Thanks for you time invest!
The used versions are:
icinga2 - The Icinga 2 network monitoring daemon (version: r2.10.5-1)
root@icingaserver:~# mysql icinga -e 'select * from icinga_dbversion;'
+--------------+----------+---------+---------------------+---------------------+
| dbversion_id | name | version | create_time | modify_time |
+--------------+----------+---------+---------------------+---------------------+
| 1 | idoutils | 1.14.3 | 2019-06-07 13:06:10 | 2019-06-07 13:06:10 |
+--------------+----------+---------+---------------------+---------------------+
mysql Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Maybe it’s not so important and it’s something what I didn’t made well while installation because I couldn’t finde anything about similar problems anywhere 
Do you mean the client with the webbrowser?
Hi,
hmmm still unclear to me. Can you add some more details on your setup to reliably get a test case?
This means, you’re defining the user inside the Director, and then deploying it to Icinga 2, right?
If so, please add the following outputs from querying the REST API (e.g. with the director ApiUser or a new one). Replace USERNAME and PASSWORD.
curl -k -s -u director:PASSWORD 'https://localhost:5665/v1/objects/users/USERNAME`
Further, please extend the SQL query to also show the collation for the specific icinga db user.
mysql -u icinga -picinga icinga -e "SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';"
With client I mean the MySQL client. That is from the CLI with the mysql command which mimics how Icinga 2’s IDO feature writes to the database itself.
Also, please add how you did setup the mysql-server instance - everything as default, or modified something in there when asked?
Last but not least, which distribution and version is that exactly?
Cheers,
Michael
Hi,
yes you’re right. I define the users inside the Director (normaly with a AD Import) and deploy it then to Icinga 2.
root@icingaserver:~# curl -k -s -u director:password 'https://localhost:5665/v1/objects/users/test2'
{"results":[{"attrs":{"__name":"test2","active":true,"display_name":"äüö","email":"test@ekom21.de","enable_notifications":true,"groups":["Icinga2-Benutzer"],"ha_mode":0.0,"last_notification":0.0,"name":"test2","original_attributes":null,"package":"director","pager":"","paused":false,"period":"","source_location":{"first_column":1.0,"first_line":7742.0,"last_column":19.0,"last_line":7742.0,"path":"/var/lib/icinga2/api/packages/director/153794df-047a-4d01-92e5-679a6c7efba1/zones.d/master/users.conf"},"states":null,"templates":["test2","Icinga2-Benutzer"],"type":"User","types":null,"vars":null,"version":0.0,"zone":"master"},"joins":{},"meta":{},"name":"test2","type":"User"}]}
root@icingaserver:~# mysql -u icinga -ppassword icinga -e "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_german2_ci |
| collation_server | utf8mb4_general_ci |
+--------------------------+--------------------+
root@icingaserver:~# cat /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf
#
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#
[mysql]
# Default is Latin1, if you need UTF-8 set this (also in server section)
default-character-set = utf8mb4
[mysql_upgrade]
[mysqladmin]
[mysqlbinlog]
[mysqlcheck]
[mysqldump]
[mysqlimport]
[mysqlshow]
[mysqlslap]
Setup of the mysql-server is everything default and like in the icinga2 documentation https://icinga.com/docs/icinga2/latest/doc/02-getting-started/
Distribution is Debian GNU/Linux 9 (stretch) (9.9).
More details about the setup I maybe not have, it’s a really simple setup with only one server (planned as test-setup, now partially used for production).
Kind regards,
Alicia
Hi,
I prefer collecting every little detail as it helps with analysing first. You’re doing a very good job on this 
If analysis doesn’t help, one needs a Debian Stretch VM with the respective components. It takes longer to find the problem, but my time is really limited currently with 2.11 tests.
So, where to start?
I wanted to see the REST API query because it would already return garbage if the core doesn’t validate this as UTF8. Therefore we can rule out the Director and just test with native config objects (if anyone wants to reproduce this).
Next is the MySQL server, if that’s the default, no more analysis on history apt logs and so on.
collation_data with utf8mb4_german2_ci looks different to my systems. This sounds like the default when your locale is German or you need to explicitly configure this.
Short google leads me to an addition in MariaDB.
MariaDB 10.0.5 added the utf8_german2_ci, utf8mb4_german2_ci, ucs2_german2_ci, utf16_german2_ci and utf32_german2_ci collations.
Wondering if this may have an influence if the connection is utf8mb4_general_ci but the database is utf8mb4_german2_ci.
Can you create the dummy example in the icinga2.conf temporarily on the server, and let it dump it do the IDO database? Well, and then again the result query and yet better, a database dump for the icinga_objects and icinga_contacts tables - remove all entries from there which expose your company’s data.
Cheers,
Michael
Good morning,
this is something I changed this week while searching a solution for the problem. Befor the change it was also utf8mb4_general_ci like in your configuration.
I have made a dummy.conf in my conf.d/ directory
1 object User "öüä" {
2 display_name = "üäö"
3 email = "test@"
4 }
MariaDB [icinga]> select contact_id, alias, email_address from icinga_contacts where email_address like '%test%';
+------------+--------------+----------------+
| contact_id | alias | email_address |
+------------+--------------+----------------+
| 546 | ÄaÜuÖoßs | test@ |
| 547 | äüö | test@ |
| 548 | üäö | test@|
+------------+--------------+----------------+
That it what I meant as I wrote
There the users also looks fine.
I will need a little more time to make the dumps and remove everything from our company.
Sorry, at the moment I can’t find a good way to remove every company specific data…
Best regards,
Alicia
Hi there,
I have took a look into the /usr/share/icinga2-ido-mysql/schema/mysql.sql and found there, that many character set’s are to latin1? I imagen this could be the problem…
# vim /usr/share/icinga2-ido-mysql/schema/mysql.sql
...
--
-- Table structure for table icinga_objects
--
CREATE TABLE IF NOT EXISTS icinga_objects (
object_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
objecttype_id bigint 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 default 0,
PRIMARY KEY (object_id),
KEY objecttype_id (objecttype_id,name1,name2)
) ENGINE=InnoDB COMMENT='Current and historical objects of all kinds';
-- --------------------------------------------------------
...
--
-- Table structure for table icinga_contacts
--
CREATE TABLE IF NOT EXISTS icinga_contacts (
contact_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
instance_id bigint unsigned default 0,
config_type smallint default 0,
contact_object_id bigint unsigned default 0,
alias varchar(255) character set latin1 default '',
email_address varchar(255) character set latin1 default '',
pager_address varchar(64) character set latin1 default '',
host_timeperiod_object_id bigint unsigned default 0,
service_timeperiod_object_id bigint unsigned default 0,
host_notifications_enabled smallint default 0,
service_notifications_enabled smallint default 0,
can_submit_commands smallint default 0,
notify_service_recovery smallint default 0,
notify_service_warning smallint default 0,
notify_service_unknown smallint default 0,
notify_service_critical smallint default 0,
notify_service_flapping smallint default 0,
notify_service_downtime smallint default 0,
notify_host_recovery smallint default 0,
notify_host_down smallint default 0,
notify_host_unreachable smallint default 0,
notify_host_flapping smallint default 0,
notify_host_downtime smallint default 0,
config_hash varchar(64) DEFAULT NULL,
PRIMARY KEY (contact_id),
UNIQUE KEY instance_id (instance_id,config_type,contact_object_id)
) ENGINE=InnoDB COMMENT='Contact definitions';
-- --------------------------------------------------------
...
Are there different versions of the file?
Hi,
no that’s mainly the culprit with the IDO schema having the old latin1 character set. It is a dangerous mix. I now remember some of the issues, like this or this. Maybe try to set the charset in the DB resource to latin1 first.
Cheers,
Michael
Thank you so much! That fixed the problem! Hopefully I will not find some issue because of this change, but for the Moment it seems perfect
It should work, and the work underway for a new backend fully supports UTF8 avoiding such character set dances. Then you can also use emojis in your object names - Icinga 2 supports it inside the DSL, just the IDO database doesn’t 
Ive had the same problem and this helps me also. The charset field was empty and changed it to “latin1” and everything works. Iam using 2.7.1-1 (Debian Buster).
Best regards
TImo