0

I have problem with character encoding when pulling data from MySQL database that seems to be specific to Windows computer.

The easy solution that's working on the Linux computer:

# establish connection
db = src_mysql(user = "user", password = "pass", dbname = "training_db", host = "127.0.0.1", port = 3306)
# change/force the character encoding
dbGetQuery(db$con, 'SET NAMES utf8')

If I pull the table with this settings on Linux, everything looks fine, if I do it on Windows computer, I get messy variables from Czech characters in the database.

When I inspect the databases in MySQL Workbench on Win pc, everything seems to be allright.

Does anyone stumbled on the same problem and found a solution?

ayasugihada
  • 329
  • 1
  • 2
  • 13

1 Answers1

0

Found a solution in using dbConnect. Thus in this particular example:

db = dbConnect(drv = RMariaDB::MariaDB(),
username = "user",
password = "pass",
dbname = "training_db",
host = "127.0.0.1", 
port = 3306
)

Interestingly, I remember trying to use dbConnect on Linux, but after some issues gravitating to aforementioned src_mysql. Anyway, dbConnect is recommended even in the oficial guidelines nowadays dplyr_guidelines .

ayasugihada
  • 329
  • 1
  • 2
  • 13