Error in encoding and RMySql

I used RMySQL::dbWriteTable function in R to write a table (Spanish words) to MySQL on Windows, but I got the character '?' concerning some signals [ñ, í, á, ó, ú, ü, etc.].

I tried some solutions that I found on the internet, but those methods didn't work. Also, I used the Encoding function in R but was the same result in the three ways ( "UTF-8", "latin1" or "unknown")
That was my final code:


connection <- dbConnect(MySQL(), user = "data_xxxxx",
    host = "localhost",
    password = "xxxxxxxx",
    dbname = "data_xxxxx")
dbSendQuery(connection, "SET NAMES utf8mb4;")
dbSendQuery(connection, "SET CHARACTER SET utf8mb4;")
dbSendQuery(connection, "SET character_set_connection=utf8mb4;")

x = tibble(items = c('conrazón ñato', "íóúáñ'"))

dbWriteTable(connection, 'prueba', x, overwrite = TRUE)
prueba = dbReadTable(connection, 'prueba')



Server connection collation: utf8mb4_spanish_ci

Try setting the encoding for the connection

connection <- dbConnect(MySQL(), user = "data_xxxxx",
    host = "localhost",
    password = "xxxxxxxx",
    dbname = "data_xxxxx",
    encoding = "latin1")

This works for me with PostgreSQL and Windows but maybe it will also work with MySQL

1 Like

Thanks for your help, but it didn't work :pensive:

RMySQL is considered a legacy driver, maybe if you try with the newer RMariaDB

1 Like

:open_mouth: :open_mouth: :open_mouth:
Works perfect!!!!!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.