Openfire to mysql charset problem

Openfire Openfire Support
1

Hi,

I run my Openfire with mysql db. I have to use Polish charsets in Openfire for example to use Polish fonts like “?”, “?”, “?” in user accounts names however when I do so, Openfire puts character “?” instead. Then, from openfire side everythink seems to work fine (openfire shows polish fonts) until I restart it. After that, openfire shows trash characters on screen. Do enyone knows how to force openfire to put proper Polish (or other non English - for example Czech, Croatian or similar) charsets into mysql db?

Ps. I use latin2 - latin2_general_ci as mysql charset in openfire db.

2

Is your mysql configured for multilingual support? That is a configuration option during the setup of mysql.

3

If it comes to mysql server itself I’ve got following charset variables set:

character_set_client latin1

character_set_connection latin1

character_set_database latin1

character_set_filesystem binary

character_set_results latin1

character_set_server latin1

character_set_system utf8

character_sets_dir /usr/share/mysql/charsets/

collation_connection latin1_swedish_ci

collation_database latin1_swedish_ci

collation_server latin1_swedish_ci

I’ve discovered in Openfire documentation following note connected with this issue:

"MySQL does not have proper Unicode support, which makes supporting data in non-Western languages difficult. However, the MySQL JDBC driver has a workaround which can be enabled by adding ?useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8 to the URL of the JDBC driver. You can edit the conf/openfire.xml file to add this value.

Note: If the mechanism you use to configure a JDBC URL is XML-based, you will need to use the XML character literal & to separate configuration parameters, as the ampersand is a reserved character for XML."

I’ve added this entry to my openfire.xml (replacing “&” with “&”) and changed character set in openfire’s db to utf-8 but I still have problems with Polish fonts when trying to add new user using openfire interface. For example, when I’m trying to add user with name “?o?”, openfire puts to jiveUsers.name “?o?” instead and from openfire everything looks just fine until restart of it. After restart, in openfire administration interface, I see his name as “?o?”. When I add this user from mysql, in openfire interface i can see his name as “£o¶”. Irritating, isin’t it? My current openfire.xml database section is following:

<database>

<defaultProvider>

<driver>com.mysql.jdbc.Driver</driver>

<serverURL>jdbc:mysql://localhost:3306/openfire?useUnicode=true&chara cterEncoding=UTF-8&characterSetResults=UTF-8</serverURL>

<username>openfire</username>

<password>XXX</password>

<minConnections>5</minConnections>

<maxConnections>5000</maxConnections>

<connectionTimeout>1.0</connectionTimeout>

</defaultProvider>

</database>

4

After hours which I’ve spent on reading docummentation and threads of openfire support I’ve done following things:

  1. I’ve created openfire’s db with utf8 support (mysql server config is still set to latin1 however I can’t change it now because there is other running db on it)

  2. I’ve modified openfire.xml adding “?useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8” to “<serverURL>jdbc:mysql://localhost:3306/openfire</serverURL>”.

  3. I’ve changed openfire’s mysql JDBC connector to 5.1.5 version

  4. I’ve change’d ISO encoding to "encoding=“UTF-8” in openfire/plugins/admin/webapp/WEB-INF/admin.tld and web.xml file

  5. I’ve even added INSTALL4J_ADD_VM_PARAMS="-Dfile.encoding=UTF-8" in openfire/bin/openfire file

Unfortunately, changes had no results on this problem… Well, maybe tommorow I’ll try this on new db with mysql server set to utf8 instead of latin1 (however personally I think it’s quite stupid for mysql if it wll work because why mysql developers would allow to create db’s with different charset encoding then if it is useless?). I’ve read somwhere about about adding “<mysql><useUnicode>true</useUnicode></mysql>” to some file? Do enyone knows where do I have to add it? I’m beggining to run out of ideas, so if anyone had similar issue and soleved it successfully, please HELP!

5

Well well well…

I’ve finally made milestone proggress… I’ve started configuration of openfire and mysql from the begining. I’ve configured mysql server to utf-8, set db encoding to utf8, added “?useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8” to “<serverURL>jdbc:mysql://localhost:3306/openfire</serverURL>” of

openfire.xml and changed ISO encoding to "encoding=“UTF-8” in openfire/plugins/admin/webapp/WEB-INF/admin.tld and web.xml file. After openfire reastart I haven’t noticed any proggress. Polish characters where still visible as “?” during connection using mysql client while in openfire admin interface everything was ok. However I’ve found somwhere on web similar topic connected with php/mysql encoding problems in which there was suggestion to write “set names utf8” in mysql client. After that, everything seems to be ok and Polish characters are visible correctly from mysql client and admin interface.

6

I was helped to change the settings on the MySQL Server, file my.ini:
default-character-set = utf8
character-set-server = utf8