MySQL “incorrect string value” error when save unicode string in Django

StackOverflow

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: "xC4x8Dius" for column "last_name" at row 104

user.first_name = u"–í–∞–ª–µ—Ä–∏–π"
user.last_name = u"–ë–æ–≥–¥–∞–Ω–æ–≤"
user.save()
>>> Incorrect string value: "xD0x92xD0xB0xD0xBB..." for column "first_name" at row 104

user.first_name = u"Krzysztof"
user.last_name = u"Szukiełojć"
user.save()
>>> Incorrect string value: "xC5x82ojxC4x87" 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?

Answer rating: 160

None of these answers solved the problem for me. The root cause being:

You cannot store 4-byte characters in MySQL with the utf-8 character set.

MySQL has a 3 byte limit on utf-8 characters (yes, it"s wack, nicely summed up by a Django developer here)

To solve this you need to:

  1. Change your MySQL database, table and columns to use the utf8mb4 character set (only available from MySQL 5.5 onwards)
  2. Specify the charset in your Django settings file as below:

settings.py

DATABASES = {
    "default": {
        "ENGINE":"django.db.backends.mysql",
        ...
        "OPTIONS": {"charset": "utf8mb4"},
    }
}

Note: When recreating your database you may run into the "Specified key was too long" issue.

The most likely cause is a CharField which has a max_length of 255 and some kind of index on it (e.g. unique). Because utf8mb4 uses 33% more space than utf-8 you"ll need to make these fields 33% smaller.

In this case, change the max_length from 255 to 191.

Alternatively you can edit your MySQL configuration to remove this restriction but not without some django hackery

UPDATE: I just ran into this issue again and ended up switching to PostgreSQL because I was unable to reduce my VARCHAR to 191 characters.





Get Solution for free from DataCamp guru