As other web developers in China (and other non-latin language locations) probably know, UTF-8 is something that we need for Chinese to work correctly, however despite asking for UTF8 in the table structure, it doesn’t mean that MySQL or PHP is going to accede to our wishes.

Here is how we at Computer Solutions cope with forcing MySQL to UTF-8, despite its unwillingness!

First off, we need to configure the MySQL config via the my.cnf file to force the defaults to utf-8, instead of latin1.

Most systems my.cnf should be in either /etc/my.cnf or /etc/mysql/my.cnf (assuming *nix)

Make sure the following lines are added under mysqld:

[mysqld]
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci

Restart mysql (debian or similar init.d should be /etc/init.d/mysql restart)

Lets check that worked -

mysql -u root -p
show variables like "%character%";

If you see results like this:

show variables like "%character%";
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Then it worked.   If it doesn’t and you see something similar to below

mysql> show variables like "%character%";
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Then we need to re-edit /etc/my.cnf

Add this under the [mysqld] lines we edited earlier.

skip-character-set-client-handshake

then restart mysql (/etc/init.d/mysql restart )   and recheck.

That forces MySQL to *use* what we <expletive deleted> well told it too!

That unfortunately isn’t the end of the story.  If like us, you’ve been merrily using UTF-8 data inside Latin1 encoded tables accidentally, you’ll still need to export your data in the correct format, and reimport it.

This can be done as follows:

Use MySQLDump with an explicit encoding passed to it:

mysqldump --user=username --password=password --default-character-set=latin1 \
 --skip-set-charset myscrewedupdb > dump.sql

This will give us a dump of the data in the correct format, albeit with the wrong encoding specified in the file.  You’ll need to edit the file to change latin1 to utf8   eg

sed -i "" 's/latin1/utf8/g' dump.sql

..and then re-import the data (I *strongly* suggest you do this to a NEW db for testing, as you DON’T want to lose data now, do you!)

mysql --user=username --password=password --default-character-set=utf8 \
newtestdbname < dump.sql

With a bit of luck, your data should be intact, and you should now be basking in the glory of UTF-8′ness, ready for the 20th Century etc!

2 Comments to “UTF-8, MySQL woes and how to force it to work!”

  • Richard says:

    Just to let you know, I’ve been reading your blog since we arrived to Shanghai last August. You actually helped out with a Mac issue on Shanghai Expat (thanks!) and I discovered your blog.

    Keep up the great work. I like the easy going style of your writing and find the eclectic mix of topic very interesting (and often odd for a blog entitled “Computer Solutions”), but that’s what I find interesting. From making biltong to providing the “crib notes” to the driver’s test to search engine optimization, I think it’s great.

    I will mention that this UTF8 post has been here for a while (hint, hint). I’m waiting for the next post. What will it be? Top 10 Sushi restaurants in Shanghai? How to get a Metro membership card? Cleanest bathrooms in Puxi? Guide to taxis? I’m waiting with anticipation…

    Your fan in Jin Qiao.

  • Hi Richard,

    I have about 1 billion projects I’m involved in, as well as normal office day to day stuff. While I intend to update the blog at least twice a month, I don’t always get a chance to!

    Don’t worry, I’ll keep being eclectic.

    Hopefully the glut of posts for this month will keep you satisfied for a while :)

Post comment

Archives

Categories

Most Popular Posts

Tags

Apache Apple Apple Mail arm7 biltong china china telecom Chinese Spyware Removal Howto coffee cool debian dns dvd firmware foscam Google hacking how to howto icp Image ipcam Kitto lg dv340 licence Mac Moganshan nc745 nuvoton outage Picture problem region free hack Resize Search Engine Optimization SEO shanghai south africa spam taobao Thoughts Time Machine Tuning uclinux video

Recent Comments

  • jocc: finally. I done the installation properly. I made a USB drive (at less 8GB) formatted wih GUID labelled...
  • Lawrence Sheed: It gets an ip address via DHCP, so you need to provide DHCP service on something like a router or...
  • Denis: I Can noit connected to cam after RESET! What is my ip by factory ?
  • Lawrence Sheed: Yes, you need to have had a license for at least one year. Thats correct. When I’m back...
  • Lawrence Sheed: First off, do a list of your partitions. in the boot loader – ls If you really have erased...

PHOTOSTREAM

loz - yamaha - IMGP0106SDIM0437SDIM0436SDIM0435SDIM0434SDIM0433SDIM0432SDIM0431SDIM0430SDIM0429SDIM0428SDIM0427SDIM0426SDIM0425SDIM0424SDIM0423SDIM0422SDIM0421