Django app in Azure - OperationalError at /edit_profile/ (1366, "Incorrect string value: '\\xC5\\x9B' for column 'first_name' at row 1")

Question

I have Django app hosted on Azure that is connected to MySQL database (Azure Database for MySQL). I wanted to edit my profile so I put ść (for testing purposes) in First name and I got the following error:

OperationalError at /edit_profile/
(1366, "Incorrect string value: '\\xC5\\x9B\\xC4\\x87' for column 'first_name' at row 1")
Request Method: POST
Request URL:    http://127.0.0.1:8000/edit_profile/
Django Version: 3.2
Exception Type: OperationalError
Exception Value:    
(1366, "Incorrect string value: '\\xC5\\x9B\\xC4\\x87' for column 'first_name' at row 1")

Traceback Switch to copy-and-paste view
C:\Users\myname\Anaconda3\lib\site-packages\django\db\backends\utils.py, line 84, in _execute
                return self.cursor.execute(sql, params) …
▶ Local vars
C:\Users\myname\Anaconda3\lib\site-packages\django\db\backends\mysql\base.py, line 73, in execute
            return self.cursor.execute(query, args) …
▶ Local vars
C:\Users\myname\Anaconda3\lib\site-packages\MySQLdb\cursors.py, line 206, in execute
        res = self._query(query) …
▶ Local vars
C:\Users\myname\Anaconda3\lib\site-packages\MySQLdb\cursors.py, line 319, in _query
        db.query(q) …
▶ Local vars
C:\Users\myname\Anaconda3\lib\site-packages\MySQLdb\connections.py, line 259, in query
        _mysql.connection.query(self, query) …
▶ Local vars
The above exception ((1366, "Incorrect string value: '\\xC5\\x9B\\xC4\\x87' for column 'first_name' at row 1")) was the direct cause of the following exception:
C:\Users\myname\Anaconda3\lib\site-packages\django\core\handlers\exception.py, line 47, in inner

My server parameners on Azure are:

character_set_server = utf8mb4 (utf8 is not working too)

collation_server = utf8_general_ci

From what I know Django uses utf-8 by default so my question is what can I do to let users use Polish, French, German etc. letters in their usernames, first names and last names?

EDIT:

The solution to this problem was much simpler than I thought. I checked enconding in all tables and apparently the default encoding was set up to latin1_swedish_ci instead of uft8mb4. I specified it in my database options:

    'OPTIONS': {
    'charset': 'utf8mb4',
    'init_command': 'SET character_set_connection=utf8mb4;'
                    'SET collation_connection=utf8mb4_unicode_ci;'
                    "SET NAMES 'utf8mb4';"
                    "SET CHARACTER SET utf8mb4;"},

and changed enconding in all existiong tables.

If you are copying traceback from the browser use ```Switch to copy-and-paste view```, otherwise copy it directly from the terminal. — Sumithran, Dec 16 '21 at 08:40
@GiacomoCatenazzi - Would you care to elaborate? I don't think it is either "double escaping" or "double encoding". — Rick James, Dec 20 '21 at 23:45

score 1 · Accepted Answer · answered Dec 20 '21 at 23:42

Hex C59B C487 is the UTF-8 encoding for ść.

What are the connection settings? They should be something like:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        ...
        'OPTIONS': {
                    'charset': 'utf8mb4',
                    'use_unicode': True, },
    },
}
my.cnf:

[mysqld]
character-set-server=utf8mb4
default-collation=utf8mb4_unicode_ci

[client]
default-character-set=utf8mb4

utf8mb4 (MySQL's term) is the same as UTF-8 (the outside world's term). It includes the encoding for 'all' languages in the world. That includes Polish, French, German, Chinese, Cherokee, Klingon, etc.

SHOW CREATE TABLE should also say that utf8mb4 is being used for the columns that need to contain ść, etc.

Many charset issues are discussed here, but I don't think this problem is included. Still, if you run into other problem, check out that Q&A.

If you are using a "terminal", be sure that it, too, is working with UTF-8. For Windows' "cmd", see chcp 65001.

Django app in Azure - OperationalError at /edit_profile/ (1366, "Incorrect string value: '\\xC5\\x9B' for column 'first_name' at row 1")

1 Answers1