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