Saiweb

The problem

We’ve all been in this position at some point, working for a company who wants to internationalize their website, and so their mySQL CMS data …

But all is not so well as just using ‘SET NAMES utf8′ and changing all ‘charset’ on tables to utf8,

You may fall foul of seeing content like Á£

This is due to the fact in this case the latin1 encoded £ has not properly been converted to utf8 and as such is not rendering correctly, this is true of most ‘multibyte’ characters.

The solution

What you need to do is actually convert the character set to utf8, in order to do this your going to need to run it through a program you could use iconv if you are already familiar with it, however if your system has python installed you can grab a copy of my sysadmin program which has iconv like functionality but is far more user friendly.

What you will need

• Text Editor (vi/nano/pico/emacs)
• Python 2.4 or higher
• SED package
• Sysadmin program
• mySQL

Preparing the file

This assumes the database is currently using latin1, in theory this could be any encoding.

Get a dump of the database:

Now you have to be aware of what you need to replace using SED, you can’t just replace all instances of ‘latin1′ as Murphy’s law being as it is means that somewhere there will be ‘latin1′ in the physical content, especially for instance if I was using a mysql dump from this blog.

As such you need to replace the following:

If your database dump is small enough (sub 100mb) you can edit this line directly in your text editor, alternatively you can do the following.

Now you need to replace all instances of ‘CHARSET=latin1′

Now we have to run the file through the charset converter

If your sql dump is over 30mb, you will be prompted to confirm you wish to proceed, please remember that this will load the entire file into memory, so ensure you have enough available system memory before proceeding, I also suggest not running this on a production server.

If any characters could not be converted you will be alerted to their exact position within the file, from there you will either need to use sed to replace the character or use your text editor.

If all went well you now have ./databasename-latin1.sql.utf-8 (note the utf-8 extension), you now have a complete utf8 mySQL dump, all you need do now is import the dump.

Further reading

1. Force mySQL utf8 connections
2. mySQL backup script

The problem that most people face when setting up a UTF-8 database in mySQL is that without calling ‘SET NAMES’ in the mySQL client prior to issuing any queries (PHP, C++ etc …) that the client connection will actually in most cases default to  latin-1.

However as of mySQL 5.x or higher you can issue a statement in the my.cnf file calling init_connect.

This will trigger a series of defined commands / queries every time a non super user connects (So if you are using root to connect to your mySQL database, stop reading now and slap yourself HARD).

i.e.

UPDATE 04/09/09

my mySQL version 5.0.45 x64 only picks up the last entry of init_connect

Use this example in this case:

Restart mySQL and check the mysqld.log has not returned any errors (Or your event viewer if you are using windows).

Every client connection will now default to utf-8 encoding and not latin-1, removing the need to add a SET NAMES call on every connection.

This will work for PHP, C++, ruby etc… as the client encoding is now handeled server side, rather that waiting on the client to issue a SET NAMES command.

UPDATE 30/03/09: Added “skip-character-set-client-handshake” this ignores the clients request to set the connection charset, this info courtesy of “wardo” http://word.wardosworld.com/?p=164

UPDATE 10/09/09

Been having some issues with this working the workaround is to add this config as a single line: