1

This works perfectly fine in R (using RStudio IDE):

library(RMySQL)
con <- dbConnect(RMySQL::MySQL(),
             dbname="remote_db_name",
             user = "root",
             password = "remote_pw",
             host = "remote_host.rds.amazonaws.com"
             )

Obviously, for best practices, I decided to move the connection details to a file(myconfig.cnf)

The contents of myconfig.cnf

# Config file to connect to the remote DB
[production]
 dbname=remote_db_name
 user=root
 password=remote_pw
 host=remote_host.rds.amazonaws.com

And I run this code in R now:

con1 <- dbConnect(RMySQL::MySQL(), group='production',default.file="~full_path/myconfig.cnf")

But on execution, I get the error:

Error in .local(drv, ...) : 
Failed to connect to database: Error: Access denied for user 'root'@'my_public_ip' (using password: YES)

I am clueless now. The code that works when you hardcode it, but not when you provide it in a config file.

Any suggestions on how to fix this and explain what's happening under the hood?

Thanks, Rouse

Rouse
  • 191
  • 1
  • 2
  • 5
  • It should be `database`, not `dbname`. – m0nhawk Jan 20 '15 at 13:49
  • Still the same error. – Rouse Jan 20 '15 at 13:54
  • Strange, it works for me. And what if you try different user? Maybe something messed with `root` permissions? – m0nhawk Jan 20 '15 at 14:09
  • Unfortunately, that's the only user in the DB that I can use. I did a summary(con) and that had User listed as root. But when I use the config file, the user seems to be passed as 'root' '@' 'my_ip' . That seems to be what is causing problems ! For eg: I considered this [link] (http://stackoverflow.com/questions/8348506/grant-remote-access-of-mysql-database-from-any-ip-address) but the code works when hard-coded. That stumps me. – Rouse Jan 20 '15 at 14:47
  • And what does `SELECT user, host FROM mysql.user;` shows? Does `root` user have either `%`, or your IP? – m0nhawk Jan 20 '15 at 14:55

0 Answers0