Trouble with connecting R to MySQL database using tcpl R package

Question

I have a database stored in MySQL and need to connect to it in R using the tcpl R package.

I tried connecting using the following:

tcplConf(user='root', password= YOUR_PW, db='prod_internal_invitrodb_v4_1', drvr='MySQL', host='localhost')

This however keeps giving an error: Error: Failed to connect: Access denied for user 'root'@'localhost' (using password: NO)

When I try connecting with dbConnect(), it works:

con <- dbConnect(drv = RMySQL::MySQL(), user='root', password=YOUR_PW, db='prod_internal_invitrodb_v4_1', host='localhost')

I don't think it's an authentication issue. Does anyone have some advice on how to resolve this establish a connection using tcplConf()?

Answer 1

Update: the connection issue with tcpl R package has been resolved. Because I had run and used RMySQL to establish a connection with the MySQL database, it was interfering with the authentication and establishing a connection using tcplConf().

unloadNamespace("RMySQL")

After this, I ran tcplConf() which worked fine.

Answer 2

Ater installing the driver

install.packages("RMySQL")

library(RMySQL)

your connection string must be like this

mysqlconnection = dbConnect(RMySQL::MySQL(),
                            dbname='prod_internal_invitrodb_v4_1',
                            host='localhost',
                            port=3306,
                            user='root',
                            password=YOUR_PW)

Answer 3

Looking at the tcpl documentation, it uses pass parameter to pass the password instead of the password parameter that you used:

Usage

tcplConf(drvr = NULL, user = NULL, pass = NULL, host = NULL, db = NULL, ...)