Unknown charset: utf8mb4

All we need is an easy explanation of the problem, so here it is.

Context: I send queries to my MariaDB server(Ver 15.1 Distrib 10.3.34-MariaDB) through a Python script and mysql.connector module.
(I don’t know which one it’s among those 3 in my list:
mysql-connector 2.2.9
mysql-connector-python 8.0.29
mysql-connector-python-rf 2.2.2
)

I had a first issue where this query for example :

INSERT INTO messages(message_id, message, user_id, date_sent, channel_sent, stream_sent) VALUES('1c5e6b29-cd11-4a8b-bd20-583f65b4a130', '🙃', 121797523, FROM_UNIXTIME(1658512097), 24147592, 1539530627)

Would return this error :

1366 (22007): Incorrect string value: '\xF0\x9F\x99\x83' for column `table_name`.`messages`.`message` at row 1

After countless hours of looking, I find a post saying this function : conn.set_charset_collation(charset=charset, collation=collation)

should fix it. So right after connecting to my database in my Python script, and getting my conn(the one you use to get your cursor), I do

conn.set_charset_collation(charset="utf8mb4", collation="utf8mb4_bin")

Now I get a new error message : unknown encoding: utf8mb4

I’ve also found another post saying to edit /etc/mysql/my.cnf, so I did and now it looks like the following(but it didn’t fix the issue) :

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
max_connections = 500
character-set-client-handshake                  = 0
init_connect                                    = 'SET character_set_system = utf8mb4'
init_connect                                    = 'SET character_set_connection = utf8mb4'
init_connect                                    = 'SET character_set_database = utf8mb4'
init_connect                                    = 'SET character_set_results = utf8mb4'
init_connect                                    = 'SET collation_database = utf8mb4_bin'
init_connect                                    = 'SET collation_connection = utf8mb4_bin'
init_connect                                    = 'SET NAMES utf8mb4'
character_set_client                            = utf8mb4
collation-server                                = utf8mb4_bin

(I did systemctl restart mysql and systemctl restart mariadb afterward)
I would be VERY THANKFUL for anything that would help fixing this. I usually know I can find a solution after looking for a few hours, but here I feel like I’ll never be able to ever fix it

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I found a solution. I had to, right after creating my cursor, call these functions :

cursor.execute('SET NAMES utf8mb4')
cursor.execute("SET CHARACTER SET utf8mb4")
cursor.execute("SET character_set_connection=utf8mb4")

This is weird because I thought those were already done through what I added in /etc/mysql/my.cnf, but apparently not ? Anyway. I am happy

It says I can only add this as Accepted answer in 2 days, I’ll add it then.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply