RMySQL not working with a cnf file

Question

I am trying to connect to a MySQL server through R and it works perfect with the follwoing line:

con <- dbConnect(MySQL(), user="user", password="password",dbname="dbname", host="localhost", port=3306)

But, I would like to use a cnf file so that my user/apssword credentials donot appear in my code and tried the following:

rmysql.settingsfile<-"mydefault.cnf"
rmysql.db<-"test_db"
drv<-dbDriver("MySQL")
con<-dbConnect(drv,default.file=rmysql.settingsfile,group=rmysql.db) 

And this is how my cnf file looks:

[test_db]
user=user
password=password
database=dbname
host=localhost
port=3306

It is in the same folder as in my R script which is my current working directory. But, I run into the following error:

Error in mysqlNewConnection(drv, ...) : 
  RS-DBI driver: (Failed to connect to database: Error: Access denied for user 'ODBC'@'localhost' (using password: NO)
)

Any suggestions, please?

Thanks so much

Answer 1

I had this problem very recently. RMySQL looks in the root directory for these files so you need to fully qualify the location of the file. i.e.:

rmysql.settingsfile<-"/home/MD-Tech/mydefault.cnf" or rmysql.settingsfile<-"c:\Users\MD-Tech\rfiles\mydefault.cnf"

Answer 2

Ya, getting this setup for the first time can be like pulling cats' teeth! Here is what I did while running R on a Droplet (Ubuntu 16.04, MySQL 5.7.16).

1. First, make sure you can at least login successfully to MySQL through the terminal

   mysql -u kevin -p

2. Next, run R and verify that you can login in directly with dbConnect() using a user name and password

   mydb = dbConnect(drv, user='kevin', password='ilovecats', dbname='catnapdb', host='127.0.0.1', port=3306)

3. Edit your mysql.cnf text file and at the bottom add a new group (exact name of this file and its location will depend on operating system and versions).

   [whiskerpatrol] user = kevin password = ilovecats host = 127.0.0.1 port = 3306 database = catnapdb

Answer 3

Two things could be going on.

The CNF file should be encrypted, password should say password = ****. The MySQL documentation shows how to create a CNF file. Below would work for your code to create the CNF

shell> mysql_config_editor set --login-path=test_db --host=localhost --user=user --password

press enter without typing password, you will be prompted to enter it

The second thing is that user = NULL and password = NULL are missing as referenced in the src_mysql documentation

rmysql.settingsfile <- "~/.mylogin.cnf"
rmysql.db <- "test_db"
drv <- dbDriver("MySQL")
con <- dbConnect(drv, default.file = rmysql.settingsfile, group = rmysql.db, user = NULL, password = NULL)

When you add these and run the code, you should be set.

Answer 4

Fing something working at: https://www.r-bloggers.com/mysql-and-r/

Not in configuration file... but work.


con <- dbConnect(MySQL(),
     user="me", password="nuts2u",
     dbname="my_db", host="localhost")