How do I get SQL database into R from local host?

Question

I just created my first SQL database using MAMP. (It is simple- just a list of pets.) I would like to load it into R. Here is what I wrote:

install.packages("dbConnect")
library(dbConnect)
mypets=dbConnect(MySQL(),user="root",
             host="localhost1234/DatabaseGrace")

This error is returned:

Error in mysqlNewConnection(drv, ...) : 
  RS-DBI driver: (Failed to connect to database: Error: Unknown MySQL server host 'localhost1234/DatabaseGrace' (2))

Any idea what this means or how I can solve it?

Answer 1

Thank you @duffymo and @Lorenz.

Summary:

• The database is stored on my computer, so host did need to be localhost as you suggested.

Here is what ended up working.

install.packages("RMySQL")

install.packages("dbConnect")
library(dbConnect)
dbGrace=dbConnect(MySQL(),user="root",
                 host="localhost",
                 dbname="DatabaseGrace",
                 password="root",
                 unix.sock="/Applications/MAMP/tmp/mysql/mysql.sock")

Thanks, all!

Answer 2

I think the host name should be "localhost", without the 1234.

If you intended that to mean the port, I'd check to make sure that MySQL is indeed listening on that port. The default value is 3306; the admin would have to make a conscious decision to change that.

The usual notation is "localhost:3306" in Java. If you must include the port number, try separating it from the host name with a colon.

Can you connect to MySQL using the admin shell?

Have you GRANTed permission to localhost to connect to database named DatabaseGrace? If not, MySQL won't allow you to connect.

http://dev.mysql.com/doc/refman/5.1/en/grant.html

localhost suggests to me that the database is running on your local machine. Is that true? If not, localhost isn't the right host name. It might also mean that you have a firewall between your machine and the database which prevents you from connecting.

I get a lot of mileage out of cutting & pasting any errors I get into Google to see if anyone else has ever experienced my problem. Here's the first hit.