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

by

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.

More Related Contents:

  1. Python & MySql: Unicode and Encoding
  2. How to filter (or replace) unicode characters that would take more than 3 bytes in UTF-8?
  3. Unicode (UTF-8) reading and writing to files in Python
  4. UnicodeDecodeError: ‘ascii’ codec can’t decode byte 0xef in position 1
  5. Python and BeautifulSoup encoding issues [duplicate]
  6. How to convert a string to utf-8 in Python
  7. Writing UTF-8 String to MySQL with Python
  8. Setting Django up to use MySQL
  9. UnicodeEncodeError: ‘latin-1’ codec can’t encode character
  10. General Unicode/UTF-8 support for csv files in Python 2.6
  11. How to replace Django’s primary key with a different integer that is unique for that table
  12. Install mysql-python (Windows)
  13. Why doesn’t Python recognize my utf-8 encoded source file?
  14. How to use MySQLdb with Python and Django in OSX 10.6?
  15. Django : Table doesn’t exist
  16. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock
  17. Error loading MySQLdb Module ‘Did you install mysqlclient or MySQL-python?’
  18. How to make Django slugify work properly with Unicode strings?
  19. Python and character normalization
  20. Python ascii utf unicode
  21. How to run Django’s test database only in memory?
  22. How can I check if a Python unicode string contains non-Western letters?
  23. InterfaceError (0, ”)
  24. How to make an Inner Join in django?
  25. Convert “\x” escaped string into readable string in python
  26. Simple Subquery with OuterRef
  27. Unable to encode/decode pprint output
  28. Django + MySQL on Mac OS 10.6.2 Snow Leopard
  29. Single Django model, multiple tables?
  30. What is PyMySQL and how does it differ from MySQLdb? Can it affect Django deployment?

Leave a Comment