dave's wiki

Setup UTF-8 on ~MySQL#

Roller uses UTF-8 encoding uniformly. You must set up MySQL to use UTF-8 as the default encoding regardless of whether you take advantage of internationalization features. The instructions here describe how to set up MySQL to use UTF-8 as the default encoding. You can do this for the whole server or just for the database that you use with Roller. You only need to follow the instructions in one of the three sections below. It is recommended that you set UTF-8 as the default encoding for the whole server unless you have applications with conflicting requirements using the same MySQL server.

These instructions apply to MySQL 4.1.X. This material is covered in greater detail in Chapter 10 of the MySQL Manual.

Setting up UTF-8 as the default character encoding for the whole server#

Find the my.ini (Windows) or my.cnf (Unix) file for your server. For 4.1 installations on Unix, this is generally /etc/my.cnf (and you may need to create it). For Windows installations, you will generally have a C:\Windows\my.ini that defines a basedir directory value, and the remainder of the settings will be in a my.ini file in that basedir directory.

Find the [mysqld] section within the my.ini or my.cnf if there is one. If necessary, add one as shown below. Add a line (or replace any existing line) setting the value of the default-character-set variable with the following setting:

[mysqld]
default-character-set=utf8

Restart the MySQL server. This will only affect databases created after this point.

Notes:

1. There is no capitalization or hyphenation in "utf8" as it appears in the MySQL setting above.
2. If you have a setting for the value of the default-collation variable, comment it out; the MySQL server will determine the proper default based on the character set.
3. On Windows, you can also select default UTF-8 encoding by choosing the option "Best support for Multilingualism. Make UTF8 the default character set. ..." in the "Detailed Configuration" path of the "Server Instance Configuration Wizard" that comes with the distribution.

Setting up UTF-8 as the default character encoding for just one database (at creation time)#

If you can't set UTF-8 as the default character encoding server-wide (as directed above) because you have other MySQL applications requiring different default character encodings, you can set the default encoding for just the database used by Roller. In this case, use the following statement when you create the database

CREATE DATABASE roller DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

where roller is the name of the database that you are creating. You will need to use this instead of the regular CREATE DATABASE line during Roller installation.

For an ISP-controlled Server with an Assigned Database#

[Thanks to Lee Gwun Wai on the roller-user mailing list for providing some of the information for this section.]

In some hosted situations, the ISP controls the overall server configuration, and you are already assigned a created database. In this case, you must alter your existing database before creating your Roller tables. The appropriate command is

ALTER DATABASE roller DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

but replacing roller with the name of your existing assigned database.

In addition, you may also need to make the following adjustments to all of the JDBC connection URLs in your roller.xml file

• If your ISP has set up the overall server configuration to use a default character set other than UTF-8, you may get an "Illegal mix of collations" error. Try dropping the setting of the mysqlEncoding property (and the preceding ampersand sign, omit &mysqlEncoding=utf8).
• You may also see a "You have an error in your SQL Syntax" message. Please refer to the following FAQ answer . You may need to drop the setting of the useUnicode property (and the preceding ampersand sign, omit &useUnicode=true).