The Linux Bloke

Let’s say you need to do a website that must support multiple languages for cultures as diverse as Japan, France, Russia, Saudi Arabia, and Brazil, as well as the US. This can be quite a daunting task, with all kinds of unexpected gotchas.

The ideal character set of choice is, of course, UTF8. Alas, you will note that most of the systems you’ll need to use defaults to LATIN1, including MySQL. If your site is written in PHP, that also by default is set to LATIN1.

I find it quite puzzling that in this day and age of globalization that many of the tools don’t default to UTF8. And there are major issues with this, because everything in the chain of delivery must either be set to UTF8 or can handle UTF8 or you’ll see bizarreness when you attempt to display the characters of some languages. You will probably see a series of question marks (”??? ??? ?????”) instead of the actual words. Sometimes you may see a series of squares. Or maybe it looks like total garbage.

To debug charset issues, you must be certain that everything in the delivery chain is set for UTF8. I can’t stres this enough.

For example, on one project, the MySQL database was properly set to UTF8, but we kept seeing LATIN1 creep in from somewhere. The site was driven by PHP, and we made sure PHP was set to UTF8, but there were still issues. It turned out that PDO/mysqli was still defaulting to LATIN1, which was revealed by looking at the results of the following query issued through PHP:

SHOW VARIABLES LIKE “character%”;

Which should result in:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

But instead we saw:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Clearly demonstrating that there was a connection issue. However, we were able to, as a quick fix, issue the following query on that connection:

SET NAMES ‘utf8′;

Which fixed the problem, though requiring us to run that query on every new connection. I am sure there is a better approach, but we didn’t have time to find it.

But to give you an idea, this is the chain we had to check for UTF8:

• MySQL Server
• MySQL Driver/PDO Wrapper
• PHP
• Browser

If you are interacting with MySQL through the command-line client, then make sure you launch it thusly:

mysql –default-character-set=utf8

Or have the appropriate settings in the [client] section of my.cnf.

The character set headaches are not just limited to MySQL, but any interacting systems, web services, etc. Carefully checking the chain to ensure that every part of that chain defaults to UTF8 is essential to saving the day for the world of localized globalization!