3

UTF-8 in MySQL — Solving UTF-8 Issues in MySQL

UTF-8 in MySQL can be tricky. Sometimes the problem isn’t noticed right away. Here is how I solved my recent encounter with UTF-8 issues and MySQL.

I’ve just fixed an issue where values were not properly being stored as UTF-8 in MySQL. The problem turned out to be the need of a few my.cnf config settings. I don’t claim to know much about the inner workings of MySQL, simply because that is not where most of my time is spent. But I wanted to share the solution that worked in my particular case:

MySQL version: 5.1.41 (Ubuntu Linux)

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci; SET NAMES utf8;'
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

Tip On Ubuntu I created a utf8 config file at /etc/mysql/conf.d/utf8.cnf with the above contents and simply restarted mysql using sudo service mysql restart.

I had problems when I did not include skip-character-set-client-handshake. Adding it along with everything else fixed my UTF8 issues.

UTF-8 in MySQL Resources

Posted in | Tagged , , | 3 Responses

3 responses to “UTF-8 in MySQL — Solving UTF-8 Issues in MySQL”

  1. Suresh V
    Suresh V February 7, 2012 at 12:56 pm |

    I am having some problem. Please help.The problem is that I use Google SpreadSheets PHP API(using the help of your article “Saving Form Data to Google Spreadsheets Using PHP and the Google Docs API: “) to get data from the sheets. Everything works fine. Except that my PHP script displays spanish characters incorrectly.

    $row=$Col->getText();
    print “$row”;

    so something like ñer gets displayed as ñer.

    when I try and put the data in MySql. Same problem. I have changed my php.ini file, mysql settings. Everything really. I can give details of the settings. But nothing seems to work for me.

  2. Dimas
    Dimas February 7, 2012 at 11:45 pm |

    Try to track down where the bad conversion is happening (at which point in the process).

  3. Suresh V
    Suresh V February 8, 2012 at 9:04 am |

    Hi Dimas,

    I tried quite a lot of things. Everything is working except this spanish character issue. The below is the first instance where I get the row data from Zend GData API.

    $row=$Col->getText(); //getText is the Google API that returns the value in the row.
    print “$row”; // I print the content

    When I print it, it does not display the special characters properly.

    I tried various different things such as edit the PHP file and add:

    mbstring.language = Neutral ; Set default language to Neutral(UTF-8) (default)
    mbstring.internal_encoding = UTF-8 ; Set default internal encoding to UTF-8
    mbstring.encoding_translation = On ; HTTP input encoding translation is enabled
    mbstring.http_input = auto ; Set HTTP input character set dectection to auto
    mbstring.http_output = UTF-8 ; Set HTTP output encoding to UTF-8
    mbstring.detect_order = auto ; Set default character encoding detection order to auto
    mbstring.substitute_character = none ; Do not print invalid characters
    default_charset = UTF-8 ; Default character set for auto content type header
    mbstring.func_overload = 7 ; All non-multibyte-safe functions are overloaded with the mbstring alternatives

    Add in the below in MySql

    init_connect=’SET collation_connection = utf8_unicode_ci; SET NAMES utf8;’
    character-set-server=utf8
    collation-server=utf8_unicode_ci
    skip-character-set-client-handshake

    Change Database and Table properties

    ALTER DATABASE db_name
    CHARACTER SET utf8
    DEFAULT CHARACTER SET utf8
    COLLATE utf8_general_ci
    DEFAULT COLLATE utf8_general_ci
    ;

    ALTER TABLE tbl_name
    DEFAULT CHARACTER SET utf8
    COLLATE utf8_general_ci
    ;

    Call SETNAMES etc just after mysql open connection.

    $q=”SET NAMES ‘utf8′”;
    $r=mysql_query($q);
    mysql_query(“SET CHARACTER SET utf8”);

    But nothing seems to work. I guess I have another frustrating day ahead of me.