Unable to access database using RMySQL.

Question

I installed the smartform database and queried it successfully from the command prompt as per the instructions in the manual without any problem.However,when I try to access it using RStudio and the RMySQL package I am unable to establish a connection.The arguments to dbConnect are fairly self explanatory so I don't know where I am going wrong.I have been using 'localhost' or '127.0.0.1' for the host argument,neither seem to work.The error generated reads, "Can't initialize character set unknown (path: compiled_in)" . I have used R for many years and have never had any issues in the past but I am new to MySQL. Any guidance would be appreciated.

Answer 1

A tricky one...... I must admit to not having come across this problem before, but then I rarely use Windows for development work and host my MySQL server on Linux.

However, I have managed to track down some leads on this.

It looks like there could either be a compilation problem, or a setting in the my.cnf file might be incorrect.

Some details about the exact same error, and details about commenting out custom configuration in my.cnf file: https://support.plesk.com/hc/en-us/articles/213940125-Error-executing-Plesk-utilities-Can-t-initialize-character-set-unknown-path-compiled-in-

Stackoverflow page discussing a similar error: https://stackoverflow.com/questions/38813689/cant-initialize-character-set-utf8mb4-with-windows-mysql-python

MySQL Manual Page discussing a similar error. Looks like this solution might require re-compiling MySQL: https://dev.mysql.com/doc/refman/5.7/en/cannot-initialize-character-set.html

I hope some of those links provide some clues and you can resolve this. If not, perhaps we can look at a different R library to connect to MySQL.

There's the RMariaDB package: https://github.com/r-dbi/RMariaDB

Or you could try dplyr/dbplyr combination: http://db.rstudio.com/dplyr

Comments

Thank you very much for your reply.Unfortunately,I still haven't solved the problem using RMySQL or the other packages.Other users of RMySQL seem to be having the same problem having updated to the latest version of MySQL 8.0.See https://stackoverflow.com/questions/50053961/error-r-cannot-connect-to-mysql.I am working around the problem using Workbench to access the database and importing query results into RStudio as csv files.