Character encoding in R MySQL on Linux machine

Question

I'm trying to fetch data which includes some German word with umlaut characters. following the bellow structure everything is fine in windows machine :

Sys.setlocale('LC_ALL','C')

library(RMySQL)
  conn <- dbConnect(MySQL(), user = "user", dbname = "database", 
                    host = "host", password = "pass")
sql.query <- paste0("some query")

df <- dbSendQuery(conn, sql.query)
  names <- fetch(df, -1)
  dbDisconnect(conn)

As an example I have :

names[1230]
[1] "Strübbel"

What should I change in order to get the same result in Linux Ubuntu ? the query will run without problem, but the result is :

names[1230]
[1] "Str\374bbel"

I have checked This solution, but when I put the 'set character set "utf8"' inside of query I'm getting the following error :

df <- dbSendQuery(conn, sql.query, 'set character set "utf8"')
names <- fetch(df, -1)
Error in .local(conn, statement, ...) : 
  unused argument ("set character set \"utf8\"")

I should mention the encoding for the result is unknown :

Encoding(names[1230])
[1] "unknown"

and doing the :

Encoding(names[1230]) <- "UTF-8"

names[1230]
[1] "Str<fc>bbel"

does not solve the problem !

score 1 · Accepted Answer · answered Jul 29 '19 at 08:03

Instead of :

Sys.setlocale('LC_ALL','C')

You have to use :

Sys.setlocale('LC_ALL','en_US.UTF-8')

and in the sql query :

library(RMySQL)
  conn <- dbConnect(MySQL(), user = "user", dbname = "database", 
                    host = "host", password = "pass")
sql.query <- paste0("some query")

dbSendQuery(conn,'set character set "utf8"')
df <- dbSendQuery(conn, sql.query)
  names <- fetch(df, -1)
  dbDisconnect(conn)

score -2 · Answer 2 · answered Jul 19 '19 at 13:35

Not sure if this solution will help you but you could try such approach:

con <- dbConnect(MySQL(), user = "user", dbname = "database", 
                    host = "host", password = "pass", encoding = "ISO-8859-1")

If this encoding doesn't work then try "brute force" with different variants

Character encoding in R MySQL on Linux machine

2 Answers2