mysql encoding problems

Question

I am dealing with some external APIs, and when I save to the db, I am receiving some encoding errors, All the content I am dealing with is in unicode; but the mysql encoding is set ti latin1.

It seems to work fine on my local system, but throws error on the server. The only difference between the environments is that the local runs python2.6 whereas the remote server runs, python2.5

Following are the mysql specifications:

mysql> SHOW VARIABLES LIKE "character\_set\_database";
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 | 
+------------------------+--------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | latin1 | 
| character_set_connection | latin1 | 
| character_set_database   | latin1 | 
| character_set_filesystem | binary | 
| character_set_results    | latin1 | 
| character_set_server     | latin1 | 
| character_set_system     | utf8   | 
+--------------------------+--------+
7 rows in set (0.00 sec)

And following is the error I encounter:

Incorrect string value: '\xCB\x8Cs&ae...' for column 'content' at row 1

What mysql setting changes should I do, to not deal with encoding issues again.

Answer 1

Firstly, a pet peeve: your source data can't be in unicode, because unicode isn't an encoding. Most likely it is in utf-8.

You're not showing the code which is generating the error, or the full traceback, so it's a bit hard to guess what's going on. But one thing to try would be to set the database character set to utf-8 as well, and see if that helps.

Answer 2

This post help me get my settings straight when using MySQL with Django.