views:

1356

answers:

3

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?

A: 

You aren't trying to save unicode strings, you're trying to save bytestrings in the UTF-8 encoding. Make them actual unicode string literals:

user.last_name = u'Slatkevičius'

or (when you don't have string literals) decode them using the utf-8 encoding:

user.last_name = lastname.decode('utf-8')
Thomas Wouters
@Thomas, i tried exactly as what you said but it still raise same errors.
jack
A: 

Use unicode literals:

user = User.object.create_user(username, email, password)
user.first_name = u'Rytis'
user.last_name = u'Slatkevičius'
user.save()
Ignacio Vazquez-Abrams
I tried this but didn't work. I updated my question with some succeed examples.
jack
+1  A: 

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?

jack
I'm having a similar problem, but I don't agree that this is a valid solution. When you `.encode('unicode_escape')` you're not actually storing unicode characters in the database. You're forcing all the clients to unencode before using them, which means it won't work properly with django.admin or all sorts of other things.
muudscope