[dev.icinga.com #2389] Mysql 1267: Illegal mix of collations (latin1_general_cs,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/2389

Created by changsisu on 2012-03-01 07:58:30 +00:00

Assignee: (none)
Status: Rejected (closed on 2012-03-11 19:55:21 +00:00)
Target Version: (none)
Last Update: 2014-12-08 14:37:35 +00:00 (in Redmine)

Icinga Version: 1.10.0
OS Version: any

---

Service discriptions containing Chinese characters can cause mysql error code 1267.
Here is the full log:
Mar 1 15:36:12 ADMIN-01 ido2db: Error: database query failed for 'SELECT object_id FROM icinga_objects WHERE instance_id=1 AND objecttype_id=2 AND name1='MISC-ADMIN-01' AND name2='SMS Send Check - IDC生产环境报警手机短信发送检测'' - '1267: Illegal mix of collations (latin1_general_cs,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=''

The service above is defined like this:
define service{
use generic-service ; Name of service template to use
host_name MISC-ADMIN-01
service_description SMS Send Check - IDC生产环境报警手机短信发送检测
check_command check_icinga_sms

1. notifications_enabled 0
   max_check_attempts 1
   check_interval 1440
   notification_interval 1440
   notification_period 1622
   }

---

Relations:

• relates #2389
• relates #2389

[icinga-migration]

Updated by changsisu on 2012-03-02 01:47:06 +00:00

I'm running icinga 1.61

[icinga-migration]

Updated by Tommi on 2012-03-02 19:19:20 +00:00

within objects table definition name fields are hardcode to latin1, which is the standard within the most mysql database installations.

CREATE TABLE IF NOT EXISTS icinga_objects (
  object_id serial,
  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';

In your case there will be a mismatch between table definition and database definition if database charset is UTF or other one multibyte charset.

You can try to change the column characterset definitions for yourselve to match your database settings. (remove it or set it to your charset). But there are much more fields than for the objects table affected. for chance this will be changed in later versions, but maybe this can break backward compatibility

[icinga-migration]

Updated by mfriedrich on 2012-03-02 22:36:23 +00:00

the collation with cs = case sensitive is there for a reason. mainly to drop former binary selects which where dropping performance. if you remove cs mysql won't make a difference between HOSTNAME and hostname e.g. - that's a bug and unwanted behaviour.

[icinga-migration]

Updated by Tommi on 2012-03-03 21:09:28 +00:00

i think the problem is more to fill the latin charset field with chinese characters.
A problem may occur server internal or between client and servers. Means databse, icinga and ido2db must be enhancend in terms of multibyte character supprt. from the docu (http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html) i would say to be fully UTF ready icinga database should be defined as

CREATE DATABASE icinga
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci; --or _cs

From now, every connection(regardless of ido2db or icinga-web) to the DB should use characterset UTF8 with

SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';

or change it in my.ini as default

collation_server=utf8_unicode_ci
character_set_server=utf8
skip-character-set-client-handshake 

Afterwards, a the apps (ido2db/icingaweb) must be enabled to store/retrieve data in UTF8 (maybe with libintl or similar).

Other databases types are affected as well, if we enable ido2db with multibyte chars for mysql. But at least in near future we should think about the general solution. in oracle i had already problemes with differend sizes of a string compared to bytes.

[icinga-migration]

Updated by Tommi on 2012-03-11 19:55:21 +00:00

• Status changed from New to Rejected

currently, icinga project team cannot implement full multibyte charset support. But you are invited to help and send a patch.

[icinga-migration]

Updated by mfriedrich on 2014-12-08 14:37:35 +00:00

• Project changed from 18 to Core, Classic UI, IDOUtils
• Category set to IDOUtils
• Icinga Version set to 1
• OS Version set to any

[icinga-migration]

Updated by mfriedrich on 2015-03-12 16:56:09 +00:00

• Relates set to 8723

[icinga-migration]

Updated by mfriedrich on 2015-03-12 16:56:34 +00:00

• Relates set to 7782