MySQL "incorrect string value" error when save unicode string in Django
django, mysql, python, unicode, utf-8
Solution
I just figured out one method to avoid above errors.
Save to database
user.first_name = u'Rytis'.encode('unicode_escape')
user.last_name = u'Slatkevičius'.encode('unicode_escape')
user.save()
>>> SUCCEED
print user.last_name
>>> Slatkevi\u010dius
print user.last_name.decode('unicode_escape')
>>> Slatkevičius
Is this the only method to save strings like that into a MySQL table and decode it before rendering to templates for display?
Problem
I got strange error message when tried to save first_name, last_name to Django's auth_user model. Failed examples ``` user = User.object.create_user(username, email, password) user.first_name = u'Rytis' user.last_name = u'Slatkevičius' user.save() >>> Incorrect string value: '\xC4\x8Dius' for column 'last_name' at row 104 user.first_name = u'Валерий' user.last_name = u'Богданов' user.save() >>> Incorrect string value: '\xD0\x92\xD0\xB0\xD0\xBB...' for column 'first_name' at row 104 user.first_name = u'Krzysztof' user.last_name = u'Szukiełojć' user.save() >>> Incorrect string value: '\xC5\x82oj\xC4\x87' for column 'last_name' at row 104 ``` Succeed examples ``` user.first_name = u'Marcin' user.last_name = u'Król' user.save() >>> SUCCEED ``` MySQL settings ``` mysql> show variables like 'char%'; +--------------------------+----------------------------+ | 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) ``` Table charset and collation Table auth_user has utf-8 charset with utf8_general_ci collation. Results of UPDATE command It didn't raise any error when updating above values to auth_user table by using UPDATE command. ``` mysql> update auth_user set last_name='Slatkevičiusa' where id=1; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select last_name from auth_user where id=100; +---------------+ | last_name | +---------------+ | Slatkevi?iusa | +---------------+ 1 row in set (0.00 sec) ``` PostgreSQL The failed values listed above can be updated into PostgreSQL table when I switched the database backend in Django. It's strange. ``` mysql> SHOW CHARACTER SET; +----------+-----------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+-----------------------------+---------------------+--------+ ... | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | ... ``` But from http://www.postgresql.org/docs/8.1/interactive/multibyte.html, I found the following: ``` Name Bytes/Char UTF8 1-4 ``` Is it means unicode char has maxlen of 4 bytes in PostgreSQL but 3 bytes in MySQL which caused above error?