Bad username charset when deploy from director

[majales]

Hi I have noticed problem when deploying users from director to Icinga. I always use real name in field Display name. For example my full name: Stanislav Černý. It is displayed correctly in Icinga Director, also in database looks like this:

MariaDB [director]> select object_name, display_name from icinga_user;

object_name	display_name
director-user-template	NULL
stanislav.cerny	Stanislav Černý

but when I do deploy, it changes and looks ugly like this: Contact details Stanislav Černý (stanislav.cerny)

also in database it looks distorted:

MariaDB [icinga]> select alias,email_address from icinga_contacts;

alias	email_address
Icinga 2 Admin	icinga@localhost
Stanislav Černý	stanislav.cerny@domain.com

Both resources are defined correctly with utf8

Is it safe to fix it directly in Icinga DB?

[majales]

Well finally it seems to be my mistake, because I followed guide which just uploaded db schema to DB (/usr/share/icinga2-ido-mysql/schema/mysql.sql ), but default mysql (mariadb) charset and collation on CentOS7 is "latin1" and "latin1_swedish_ci".

So question is: Can I fix it somehow? When I converted db and tables to "utf8" with collation "utf8_general_ci" I have got an error on icingaweb2 interface:

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8', query was: SELECT eh.host_name, eh.host_display_name, eh.service_description, eh.service_display_name, eh.object_type, eh.timestamp, eh.state, eh.output, eh.type FROM (SELECT sth.timestamp, sth.output, sth.type, sth.state, sth.object_type, sth.host_name, sth.service_description, sth.host_display_name, sth.service_display_name FROM (SELECT ('host') AS object_type, h.display_name COLLATE latin1_general_ci AS host_display_name, ho.name1 AS host_name, NULL AS service_description, NULL AS service_display_name, NULL AS service_host_name, (CASE WHEN hh.state_type = 1 THEN 'hard_state' ELSE 'soft_state' END) AS type, UNIX_TIMESTAMP(hh.state_time) AS timestamp, hh.object_id, hh.state, ('[ ' || hh.current_check_attempt || '/' || hh.max_check_attempts || ' ] ' || hh.output) AS output FROM icinga_statehistory AS hh INNER JOIN icinga_objects AS ho ON ho.object_id = hh.object_id AND ho.is_active = 1 AND ho.objecttype_id = 1 ...

[Thomas-Gelf]

Hi @majales,

this is not a Director issue, you'll find related ones in the icinga2 or icingaweb2 issue tracker. It's not possible to move issues here on GitHub, so I'll close this one.

Please note that IDO is treated as "a latin1-encoded DB carrying UTF-8 encoded data" for historic reasons going back to Nagios times. And while running it as utf8 might work (pay attention to configure your DB resource accordingly in Icinga Web 2) chances are good that things will then not behave as expected.

Personally I'd strongly suggest you to drop & recreate your IDO database with latin1 encoding. Converting collations with MySQL/MariaDB is a very challenging task and it's easy to hurt yourself when you never did so before. The upcoming IcingaDB will be strictly utf8 (mb4) from the beginning and not have this problems. I guess the IDO will never be officially "migrated" to UTF8. But you could open an issue in the Icinga 2 tracker to ask for adding explicit encoding/collation instructions to the schema. And to use a matching client encoding when connecting to the DB, of course.

Cheers, Thomas