Connect to MySQL database with RMySQL

Question

I am making the move from RSQLite to RMySQL and I am confused by the user and password fields. FWIW, I'm running Windows 7, R 2.12.2, MySQL 5.5 (all 64 bit), and RMySQL 0.7-5.

I installed RMySQL as prescribed in this previous SO question, and as far as I know it works (i.e., I can load the package with library(RMySQL)). But when I try to run the tutorial from the R data import guide, I get a "failed to connect to database..." error. This is the code from the tutorial from the guide:

library(RMySQL) # will load DBI as well
## open a connection to a MySQL database
con <- dbConnect(dbDriver("MySQL"), user = "root", password = "root", dbname = "pookas")
## list the tables in the database
dbListTables(con)
## load a data frame into the database, deleting any existing copy
data(USArrests)
dbWriteTable(con, "arrests", USArrests, overwrite = TRUE)
dbListTables(con)
## get the whole table
dbReadTable(con, "arrests")
## Select from the loaded table
dbGetQuery(con, paste("select row_names, Murder from arrests",
                      "where Rape > 30 order by Murder"))
dbRemoveTable(con, "arrests")
dbDisconnect(con)

On the second line I get the following error:

> con <- dbConnect(dbDriver("MySQL"), user = "richard", password = "root", dbname = "pookas")
Error in mysqlNewConnection(drv, ...) : 
  RS-DBI driver: (Failed to connect to database: Error: Access denied for user 'richard'@'localhost' (using password: NO)
)

I have tried with and without user and password and with admin as user. I have also tried using a dbname that I made before with the command line and with one that doesn't exist.

Any tips? Is there a good reference here? Thanks!

Racine

Answer 1

That is most likely a setup issue on the server side. Make sure that networked access is enabled.

Also, a local test with the command-line client is not equivalent as that typically uses sockets. The mysql server logs may be helpful.

Bespoke

I went through all the settings and everything looked good. I tried single quotes ' instead of double quotes " and it worked! I always thought the two were interchangeable. Thanks for the pointers! – Racine

Answer 2

First try to connect to MySQL server using MySQL Workbench or command line mysql using the same parameter. If it connects then R should also be able to connect.

Typically this issue comes when MySQL server doesn't allow connections from remote machines.

Intrepid

Any idea why I WOULD be able to connect to a remote database using MySQL Workbench but not able to connect with R? If I disable the firewall on the remote server I can connect with R, however with firewall up it doesn't allow the connection. I can't understand why with the firewall on it's allowing a connection with MySQL but not R. – Vshaped

Answer 3

As people have told you, you can try to connect to the host with other application as mysql workbench. How odd! When I have tried in RStudio to connect to my db with your code without indicate the host in the command I haven't been able to connect.

I have needed to indicate the host ( host = 'localhost' ) in the command.

Miltie