2

This is how I am trying to connect MySQL to R.

db <- dbConnect(MySQL(), user='username', password='pwd',dbname=dbx, host = 'local', port = 3306)

But I'm getting this error:

Error in .local(drv, ...) : Failed to connect to database: Error: Plugin caching_sha2_password could not be loaded: The specified module could not be found.

What to do?

Roy Scheffers
  • 3,832
  • 11
  • 31
  • 36
Searcher
  • 21
  • 2
  • Are you using DBI package to connect with MySQL? – msr_003 Oct 15 '18 at 08:55
  • I am having a similar problem. I have tried with the DBI package and cannot get the problem fixed. I am also following detailed instructions to get connected between R and MySQL with no success on this specific point. – Phil Jan 08 '19 at 19:49

1 Answers1

-2

You can create a function to retrieve the query.

library(RMySQL)
sqlQuery <- function (query) {

        # creating DB connection object with RMysql package
        DB <- dbConnect(MySQL(), user="user", password="password",
                        dbname="databaseName", host="host")

        # close db connection after function call exits
        on.exit(dbDisconnect(DB))

        # send Query to obtain result set
        rs <- dbSendQuery(DB, query)

        # get elements from result sets and convert to dataframe
        result <- fetch(rs, -1)

        # return the dataframe
        return(result)
}

And then just:

new_dataframe <- sqlQuery("SELECT * from table")

Hope that helps

Filipe Rigueiro
  • 57
  • 11
  • 2
    there is nothing about this that will solve OPs `Plugin caching_sha2_password could not be loaded` issue. – JD Long Dec 17 '18 at 17:00