1

Emojis are replaced by '?' when saved to the db and I cannot find where it does come from.

I get "aa" transformed to "a?a", either via django admin or directly through my code

Any idea to fix this problem?

My configuration:

settings.py:

DATABASES = {
    'default': {
        ...
        'OPTIONS': {
            'init_command': "SET NAMES utf8mb4; SET CHARACTER SET utf8mb4; SET character_set_connection=utf8mb4; SET collation_connection = 'utf8_unicode_ci';",
            'charset': 'utf8mb4',
        },
    }
}

my mysql configuration:

[mysqld]
character-set-server  = utf8mb4
collation-server      = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

charset in db:

MariaDB [dbn]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

the table containing the field:

SHOW TABLE STATUS where name="xxx" => "utf8mb4_unicode_ci" for that table

the field receiving the emoji string:

MariaDB [dbn]> SELECT  column_name, character_set_name, collation_name FROM information_schema.columns where TABLE_NAME='xxx';
+-----------------+--------------------+--------------------+
| column_name     | character_set_name | collation_name     |
+-----------------+--------------------+--------------------+
| my_text_field   | utf8mb4            | utf8mb4_unicode_ci |

Thanks for reading

EDIT :

 dpkg -l | grep mysql
ii  default-libmysqlclient-dev:amd64 1.0.5                             amd64        MySQL     database development files (metapackage)
ii  default-mysql-client             1.0.5                             all          MySQL database client binaries (metapackage)
ii  default-mysql-server             1.0.5                             all          MySQL database server binaries and system database setup (metapackage)
ii  mysql-common                     5.8+1.0.5                         all          MySQL database common files, e.g. /etc/mysql/my.cnf

db_shell:

Server version: 10.3.27-MariaDB-0+deb10u1 Debian 10

python 3.9 django 3.1.7

ii  libmariadb-dev                   1:10.3.27-0+deb10u1               amd64        MariaDB database development files
ii  libmariadb-dev-compat:amd64      1:10.3.27-0+deb10u1               amd64        MariaDB Connector/C, compatibility symlinks
ii  libmariadb3:amd64                1:10.3.27-0+deb10u1               amd64        MariaDB database client library
ii  mariadb-client-10.3              1:10.3.27-0+deb10u1               amd64        MariaDB database client binaries
ii  mariadb-client-core-10.3         1:10.3.27-0+deb10u1               amd64        MariaDB database core client binaries
ii  mariadb-common                   1:10.3.27-0+deb10u1               all          MariaDB common metapackage
ii  mariadb-server-10.3              1:10.3.27-0+deb10u1               amd64        MariaDB database server binaries
ii  mariadb-server-core-10.3         1:10.3.27-0+deb10u1               amd64        MariaDB database core server files
Antho
  • 35
  • 9
  • Try this solution https://stackoverflow.com/questions/15943938/django-charset-and-encoding – Ranjan MP May 27 '21 at 18:08
  • Are you talking about 'charset': 'utf8mb4'? If so i've already tried it yet awfully – Antho May 27 '21 at 18:31
  • See "Mojibake" in https://stackoverflow.com/questions/38363566/trouble-with-utf-8-characters-what-i-see-is-not-what-i-stored Also, please provide `SHOW CREATE TABLE`. – Rick James Oct 29 '21 at 02:57

1 Answers1

0

I think collation_connection should be utf8mb4_unicode_ci not general, try:

SET collation_connection = 'utf8mb4_unicode_ci';
TioneB
  • 468
  • 3
  • 12