Failed to connect to database: Error: Access denied for user

[danceyoung]

hi
sessionInfo():

> sessionInfo()
R version 3.3.2 (2016-10-31)
Platform: x86_64-apple-darwin13.4.0 (64-bit)
Running under: macOS Sierra 10.12.2

locale:
[1] zh_CN.UTF-8/zh_CN.UTF-8/zh_CN.UTF-8/C/zh_CN.UTF-8/zh_CN.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RMySQL_0.10.9 DBI_0.5-13   

loaded via a namespace (and not attached):
[1] httr_1.2.1      R6_2.2.0        tools_3.3.2     withr_1.0.2    
[5] curl_2.3        memoise_1.0.0   git2r_0.16.0    digest_0.6.10  
[9] devtools_1.12.0

---

I try dbConnect method connect remote mysql,but always raise the error

conn <- dbConnect(RMySQL::MySQL(), host = "x.x.x.x", dbname = "xxx", username = "xxx", password = "xxx", port = 3306)
Error in .local(drv, ...) : 
  Failed to connect to database: Error: Access denied for user 'xxx'@'xxxx' (using password: YES)

---

I have try to searching google 、stack overflow, github rstats-db/rmysql /issues,but not fixed

[imcullan]

Hello, try this:

library(DBI)
library(RMySQL)
library(RJDBC)
mydrv <- dbDriver("MySQL")
conn <- dbConnect(mydrv, dbname="xxx",host="x.x.x.x",port=3306, user="xxx",password="xxx")

Also, may I ask where your host is? Are you sure you have access to the host from your IP?

[JulienSteel]

I'm facing the same issue. Have you found a solution yet?

[monitotier]

someone who solved this problem? i have tried several things with out success :(

[miguelBra]

Hello, I've got the same issue. I've tried @imcullan 's solution but it did not work.

[srikrishna123g]

The following worked for me. I use Ubuntu 19.04 and this is a mysql_native_password issue.

Basically, mysql_native_password is the traditional method to authenticate- it is not very secure (it uses just a hash of the password), but it is compatible with older drivers.

My guess is dbConnect is compatible with mysql_native_password.

Try this in your mysql terminal

mysql> ALTER USER 'user_name'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

Then it should work fine even without entering the host and port number for a local host.

con <- dbConnect(MySQL(), user='user_name', password='new_password', dbname='Database_Name');

[John-Barnes]

skrishna123g 's solution worked perfectly on the first try. Bravo and you're a lifesaver. I use Ubuntu 18.04, RMySQL in the 'Dark and Stormy Night' version, and RStudio 1.2.5033 ("Orange Blossom"), and MySQL 5.7.30. For whatever reason, the Digital Ocean "droplet" (virtual server") tends to come unstuck on this modification, so it's occasionally necessary to go in via command line monitor or Workbench and re-fix the MySQL_native_password as described above; it takes a few seconds but it's a very minor nuisance.

[agusnieto77]

when using dbConnect() with a remote host I get this error:

Error in .local(drv, ...) : 
  Failed to connect to database: Error: Plugin https could not be loaded: 
No se puede encontrar el módulo especificado. 
Library path is 'https.dll'

any idea how to resolve it?

[jeroen]

@agusnieto77 can you please tell us exactly which version of RMySQL and which version of R you are using, how you installed it (though CRAN or anaconda or something else) and what sort of authentication your mysql server is using.

[agusnieto77]

thanks @jeroen . ok. I use:
R version 4.1.2 (2021-11-01)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19043)
Matrix products: default
locale:
[1] LC_COLLATE=Spanish_Spain.1252 LC_CTYPE=Spanish_Spain.1252
[3] LC_MONETARY=Spanish_Spain.1252 LC_NUMERIC=C
[5] LC_TIME=Spanish_Spain.1252
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] RMySQL_0.10.23 DBI_1.1.2 (by CRAN)
loaded via a namespace (and not attached):
[1] compiler_4.1.2 tools_4.1.2

Connect to the MySQL database: con

con <- dbConnect(RMySQL::MySQL(),
dbname = "xxxx",
host = 'https//xxxxxxxxxxxx.xxx',
port = xxxx,
user = "xxxxxx",
password = "xxxxxxxxxxx")

[PilarRius]

For the original question:

1-
username="root"
password= "YOUR_PASSWPRD"

As in no need to put the actual password, only copy "YOUR_PASSWORD"

2-If still doen´t work remove database

3-Check that your IP address is added to the server permissions/security

4-Check that the DB is available and public connection is allowed

5-If using Windows allow connections to the port 3306 on the Firewall Defender(control panel, security, firewall defender, advanced setting, add inbound rule, port, 3306, allow)

[agusnieto77]

Thank you very much, I was able to solve it El jue, 23 jun 2022 a las 5:29, Pilar Rius ***@***.***>) escribió:

…

For the original question: 1- username="root" password= "YOUR_PASSWPRD" As in no need to put the actual password, only copy "YOUR_PASSWORD" 2-If still doen´t work remove database 3-Check that your IP address is added to the server permissions/security 4-Check that the DB is available and public connection is allowed 5-If using Windows allow connections to the port 3306 on the Firewall Defender(control panel, security, firewall defender, advanced setting, add inbound rule, port, 3306, allow) — Reply to this email directly, view it on GitHub <#177 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AKVQLKG6GFRHAXP4P5JMEQDVQQN5FANCNFSM4C2AE3QQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>