MySQL incorrect string value error when save unicode string in Django

Question

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 Slatkevicius  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 Szukielojc  user save    gt  gt  gt  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    gt  gt  gt  SUCCEED   MySQL settings  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    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 gt  update auth user set last name  Slatkeviciusa  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    PostgreSQL  The failed values listed above can be updated into PostgreSQL table when I switched the database backend in Django  It s strange   mysql gt  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

User · Answer

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 Slatkevicius    or  when you don t have string literals  decode them using the utf-8 encoding   user last name   lastname decode  utf-8

User · Answer

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     Change your MySQL database  table and columns to use the utf8mb4 character set  only available from MySQL 5 5 onwards  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

User · Answer

If you have this problem here s a python script to change all the columns of your mysql database automatically       usr bin env python import MySQLdb  host    localhost  passwd    passwd  user    youruser  dbname    yourdbname   db   MySQLdb connect host host  user user  passwd passwd  db dbname  cursor   db cursor    cursor execute  ALTER DATABASE   s  CHARACTER SET  utf8  COLLATE  utf8 unicode ci     dbname   sql    SELECT DISTINCT table name  FROM information schema columns WHERE table schema     s     dbname cursor execute sql   results   cursor fetchall   for row in results    sql    ALTER TABLE   s  convert to character set DEFAULT COLLATE DEFAULT     row 0     cursor execute sql  db close

User · Answer

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 Slatkevicius  encode  unicode escape   user save    gt  gt  gt  SUCCEED  print user last name  gt  gt  gt  Slatkevi u010dius print user last name decode  unicode escape    gt  gt  gt  Slatkevicius   Is this the only method to save strings like that into a MySQL table and decode it before rendering to templates for display

User · Answer

Improvement to  madprops answer - solution as a django management command   import MySQLdb from django conf import settings  from django core management base import BaseCommand   class Command BaseCommand        def handle self   args    options           host   settings DATABASES  default    HOST           password   settings DATABASES  default    PASSWORD           user   settings DATABASES  default    USER           dbname   settings DATABASES  default    NAME            db   MySQLdb connect host host  user user  passwd password  db dbname          cursor   db cursor            cursor execute  ALTER DATABASE   s  CHARACTER SET  utf8  COLLATE  utf8 unicode ci     dbname           sql    SELECT DISTINCT table name  FROM information schema columns WHERE table schema     s     dbname         cursor execute sql           results   cursor fetchall           for row in results              print f Changing table   row 0                     sql    ALTER TABLE   s  convert to character set DEFAULT COLLATE DEFAULT     row 0               cursor execute sql          db close      Hope this helps anybody but me

User · Answer

If it s a new project  I d just drop the database  and create a new one with a proper charset   CREATE DATABASE  lt dbname gt  CHARACTER SET utf8

User · Answer

I had the same problem and resolved it by changing the character set of the column  Even though your database has a default character set of utf-8 I think it s possible for database columns to have a different character set in MySQL  Here s the SQL QUERY I used       ALTER TABLE database table MODIFY COLUMN col VARCHAR 255      CHARACTER SET utf8 COLLATE utf8 general ci NOT NULL

User · Answer

You can change the collation of your text field to UTF8 general ci and the problem will be solved   Notice  this cannot be done in Django

User · Answer

Simply alter your table  no need to any thing  just run this query on database  ALTER TABLE table nameCONVERT TO CHARACTER SET utf8  it will definately work

[python] MySQL "incorrect string value" error when save unicode string in Django

Examples related to python

Examples related to mysql

Examples related to django

Examples related to unicode

Examples related to utf-8