Databases
StackOverflow2013
Postsdbo
POMySQL "incorrect string value" error when save unicode string in Django
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL "incorrect string value" error when save unicode string in Django
    text
    copied!<p>I got strange error message when tried to save first_name, last_name to Django's auth_user model.</p> <p><strong>Failed examples</strong></p> <pre><code>user = User.object.create_user(username, email, password) user.first_name = u'Rytis' user.last_name = u'Slatkevičius' user.save() &gt;&gt;&gt; Incorrect string value: '\xC4\x8Dius' for column 'last_name' at row 104 user.first_name = u'Валерий' user.last_name = u'Богданов' user.save() &gt;&gt;&gt; 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() &gt;&gt;&gt; Incorrect string value: '\xC5\x82oj\xC4\x87' for column 'last_name' at row 104 </code></pre> <p><strong>Succeed examples</strong></p> <pre><code>user.first_name = u'Marcin' user.last_name = u'Król' user.save() &gt;&gt;&gt; SUCCEED </code></pre> <p><strong>MySQL settings</strong></p> <pre><code>mysql&gt; 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) </code></pre> <p><strong>Table charset and collation</strong></p> <p>Table auth_user has utf-8 charset with utf8_general_ci collation.</p> <p><strong>Results of UPDATE command</strong></p> <p>It didn't raise any error when updating above values to auth_user table by using UPDATE command.</p> <pre><code>mysql&gt; 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&gt; select last_name from auth_user where id=100; +---------------+ | last_name | +---------------+ | Slatkevi?iusa | +---------------+ 1 row in set (0.00 sec) </code></pre> <p><strong>PostgreSQL</strong></p> <p>The failed values listed above can be updated into PostgreSQL table when I switched the database backend in Django. It's strange.</p> <pre><code>mysql&gt; SHOW CHARACTER SET; +----------+-----------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+-----------------------------+---------------------+--------+ ... | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | ... </code></pre> <p>But from <a href="http://www.postgresql.org/docs/8.1/interactive/multibyte.html" rel="noreferrer">http://www.postgresql.org/docs/8.1/interactive/multibyte.html</a>, I found the following:</p> <pre><code>Name Bytes/Char UTF8 1-4 </code></pre> <p>Is it means unicode char has maxlen of 4 bytes in PostgreSQL but 3 bytes in MySQL which caused above error?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload