Friday, June 6, 2008

Using UTF-8 in mysql

To use EJBCA-JBoss-MySQL with exotic UTF-8 characters you need to configure MySQL to create the tables with utf-8 character encoding, eotherwise you will get an exception when trying to save a subjectDN for a user.

To use utf8 in mysql you have to set the following options in my.cnf:
default-character-set=utf8
collation-server = utf8_general_ci

BEFORE you start MySQL, create the database and start JBoss.

To check the encoding of the tables:
mysql -u root -p
> use information_schema;
> select table_schema,table_name, table_collation from tables;
| ejbca2 | UserData | utf8_general_ci |
| ejbca2 | UserDataSourceData | utf8_general_ci |
| ejbcatest | AccessRulesData | latin1_swedish_ci |
| ejbcatest | AdminEntityData | latin1_swedish_ci |

Default when installing MySQL in ubuntu is to create with 'table_collation' latin1_swedish_c1', but we want 'utf8_general_ci', which it creates in a database created with the new settings in my.cnf.