UTF8, MySQL 4.1, and CFMX 7.0

I spent a good portion of my day trying to get unicode to work with MySQL 4.1 and CFMX 7.0. As it turns out, it’s actually pretty easy. I had the database tables created correctly. That I learned from earlier versions of MySQL. Just add this to the end of your CREATE TABLE command:

CHARACTER SET utf8;

I even had the connection string right in the data source form in the CFMX administrator:

useUnicode=true&characterEncoding=UTF8

The problem is that the connection string apparently doesn’t go in the “connection string” field. Since I’m using a newer MySQL driver than the one that shipped with CFMX 7 (that supports MySQL 4.x), I discovered that you actually have to append the connection string to the JDBC URL, like this:

jdbc:mysql://localhost:3306/dataBaseName?useUnicode=true&characterEncoding=UTF8

Once I made the change, instant Japanese! I hope this saves someone out there several hours of head scratching.

10 Responses to UTF8, MySQL 4.1, and CFMX 7.0

  1. Steve Ray says:
    February 18, 2005 at 5:40 pm

    Christian, any idea why the version of the driver which shipped with CF 7 doesn’t support 4.x? I saw that after the install and was surprised. So far, that driver is working fine on my 4.x datasources, but I’m wondering if that’s going to last.

  2. Christian Cantrell says:
    February 18, 2005 at 5:51 pm

    Licensing issues, I believe.The biggest issue is authenticating a connection since the type of encryption MySQL uses changed from 3.x to 4.x. Using the new driver is easy and free. You can get instructions here:http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=6ef0253

  4. Oguz Demirkapi says:
    February 18, 2005 at 7:40 pm

    … and here in this page also there are some Unicode problems.Check my name in the entry above :)

  5. yiming says:
    February 27, 2005 at 10:14 pm

    But how to use the exist database? we must recreate tables with “CHARACTER SET utf8″, or how to convert the exist into urf8,?

  6. Oguz Demirkapi says:
    March 3, 2005 at 5:23 pm

    This is really annoying problem.If you do not have a huge amount of databases there is a method that I made before.First use CF5 and export all database into XML file as Unicode. There are some good XML tools for CF5 such as soXML etc. After that process, you can read these XML files via CFMX and write them into database as Unicode.Just a simple suggestion.

  7. Nathan Given says:
    August 10, 2005 at 2:12 pm

    !!! I’ve spent too many hours banging my head against a wall and I still haven’t figured this out.View my post on MacromediaYou can see that I eventually moved to MS SQL Server because I could never get mysql to work.My question: I’m using the default mysql driver that shipped with CF 6.1 MX. Is the connection string box in the data source window the appropriate place for the characterEncoding variable, or should it go somewhere else?

  8. Mike Osterman says:
    August 23, 2005 at 2:39 pm

    At least for CFMX 6.1, one needs to use the 3.0.x strain of the connector rather than the 3.1.x variety.

  9. Grzegorz Bugaj says:
    February 1, 2006 at 7:44 am

    Thanks just trying to get this to work.

  10. Shailendra says:
    February 14, 2006 at 5:58 am

    I M inserting Mangal Font data into mysql using utf8 through C#. While retriving I M not getting original dataPLZ suggest