Trouble with utf-8

Published on and tagged with cakephp  mysql  problem  tip

Yesterday, I had some trouble with utf-8. The character set of my tables is utf-8, and I use utf-8 as encoding for my pages, too. No problem, you would think (and it was no problem up to now). But yesterday, I noticed that special characters like öüä are not displayed correctly. I do not know if the problem was caused by the latest version of CakePHP, or if the cause was the upgrade to MySQL 5.0. Anyway, I could fix it. I had to add the following line to my /etc/mysql/my.cnf file, the configuration file for MySQL:

[mysqld]
init-connect = 'SET NAMES utf8'

If you do not have access to this file, you can alternatively execute the statement “SET NAMES utf8” before you do any sql queries (I have not tested this approach).

8 comments baked

  • cake baker » The right encoding for your Ajax views

    […] Yesterday, I wrote about my troubles with UTF-8. A very similar problem I had today: the content I requested by Ajax had the wrong encoding. I found the solution relatively fast: I have to set the content type in my Ajax layout. Here is my modified Ajax layout: <?php header(’Content-type: text/html;charset=UTF-8′); echo $content_for_layout; ?> […]

  • Reen

    hi, I had the same problem, but I can’t change the my.cnf. So I made a custom AppModel:
    class AppModel extends Model {
    function __construct() {
    parent::__construct();
    if(!defined(‘GLOBAL_UTF8’)) {
    $this->query(‘SET NAMES “utf8″‘);
    define(‘GLOBAL_UTF8’, TRUE);
    }
    }
    }
    I introduced a constant GLOBAL_UTF8 to prevent the query from executing with every Model.

  • leo

    I have already added these lines in my.cnf
    [mysqld]
    init-connect = ‘SET NAMES utf8’

    but when i connect to the database and run
    show variables like ‘character_set%’;

    it still gets

    | character_set_client | latin1 |
    | character_set_connection | latin1 |
    | character_set_database | utf8 |
    | character_set_results | latin1

    what is wrong? Thanks a lot!

  • cakebaker

    Hm, difficult to say what’s wrong. Maybe a stupid question, but have you restarted MySQL?

  • leo

    Sorry it’s my own mistake. I log in using the super user account, that’s why the command doesn’t execute.

    Sorry again!

  • cake baker » Using UTF-8 as encoding for the database connection

    […] I already wrote about this topic some time ago. In that post I described an approach using the configuration file of MySQL, and mentioned casually that you could execute “SET NAMES utf8″ if you do not have access to the MySQL configuration file. But I did not show a solution for that case… […]

  • Michał Roszka

    Alternatively the encoding setting might be put in app/config/database.php as shown below:

    “mysql”,
    “connect” => “mysql_connect”,
    “host” => “localhost”,
    “login” => “your_username”,
    “password” => “your password”,
    “database” => “database_name”,
    “prefix” => “”,
    “encoding” => “utf8”
    );
    }
    ?>

  • cakebaker

    @Michal: Yes, that’s a better approach. Thanks for pointing out to this setting.

Bake a comment




(for code please use <code>...</code> [no escaping necessary])

© daniel hofstetter. Licensed under a Creative Commons License