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) :

default-character-set = utf8mb4

default-character-set = utf8mb4

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


1 Answer 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.

  • 1
    init_connect is ignored when you connect ast root.
    – Rick James
    Jul 24, 2022 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.