Configuring MySQL for UTF-8 Support

In the my.conf put following lines:

[mysql]
default-character-SET=utf8

[client]
default-character-SET=utf8

[mysqld]
init_connect='SET collation_connection = utf8_general_ci; SET NAMES utf8;'
character-set-server=utf8
default-character-SET=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

Then connect to the database and from the command line shell check settings:

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'character_set_client';

If any of these shows something else then 'utf8' then you have to correct it:

ALTER DATABASE tigasedb DEFAULT CHARACTER SET utf8;

You can now also test your database installation if it accepts UTF-8 data. Best way is just to create an account with UTF-8 characters:

call TigAddUserPlainPw('żółw@some.domain.com', 'żółw');

And then check of the account has been created:

SELECT * FROM tig_users WHERE user_id = 'żółw@some.domain.com';

If the last command gives you no results it means there is still something wrong with settings. You might also check you shell settings to make sure your command line shell supports UTF-8 characters and passes them correctly to MySQL:

export LANG=en_US.UTF-8
export LOCALE=UTF-8
export LESSCHARSET='utf-8'

It seems to me that MySQL 5.0.x also needs an extra parameters in the connection string: '&useUnicode=true&characterEncoding=UTF-8' while MySQL 5.1.x seems to not need it but it doesn’t hurt to have it for both versions. You have to edit 'etc/init.properties' file and append this to the database connection string.

For MySQL 5.1.x, however, you need also updated code for all database stored procedures and functions used by the Tigase. They are updated for Tigase version 4.4.x and up, for the time being if you use older version of the Tigase server you can reload stored procedures using the file from SVN.