I am trying to connect to a MySQL database through RMySQL but get the following error

"Error in .local(drv, ...) : 
  Failed to connect to database: Error: Unknown database 'XXX'"

Has anyone had a similar issue and was able to resolve it?

Running

  • macOS High Sierra, Version 10.13.6
  • MySQL workbench 8.0
  • RStudio Version 1.1.453

I constructed the SQL driver as follows:

install.packages("RMySQL")
install.packages("dbConnect")
library(DBI)
library(dbConnect)

con <- dbConnect(RMySQL::MySQL(),
                 dbname = "xxx",
                 host = "xxx",
                 port = xxx, 
                 user = "xxx",
                 password = "xxx")

I've been following Filip Schouwenaars' datacamp course Importing Data in R (https://www.datacamp.com/courses/importing-data-in-r-part-1) and was hoping to establish a connection to the SQL database and create an MySQLConnection object to then run SQL queries from inside R.

The problem is that I get stuck at the very beginning because of the failure to connect to database. In MySQLworkbench, the script opens and looks great. I'm a complete newbie at this, and am wondering whether this may have something to do with the location of the database file itself? Should I be saving it in a specific folder?

PS: I've read through all RMySQL threads on here and could not find a solution; if I missed something, please let me know. This is my first ask on this forum, and I'm both super grateful for the community here but also worried that I missed something, somewhere. THANK YOU for your help.

1 Answers

0
Community On

Solution: I did in fact not have a 'database' (or schema, as they are now called in MySQL), but merely an .sql file. Once I created a database from the file, it worked like a charm!

For other newbies out there, especially in the humanities, this was a very helpful tutorial on how to set up MySQL with R: https://programminghistorian.org/en/lessons/getting-started-with-mysql-using-r