18

I need to access to a MySQL remote server, and I want to do this with the R software.

I set up the connection parameter but I can't access. I use this code:

drv <- dbDriver("MySQL")
library(RMySQL)
# open the connection using user, passsword, etc., as
con <- dbConnect(MySQL(), user="user", password="psw",
dbname="NameDB", host="webhosting", port=3306)

And I receive this error:

Error in mysqlNewConnection(drv, ...) : 
RS-DBI driver: (Failed to connect to database: Error: Connection using old (pre-4.1.1)
authentication protocol refused (client option 'secure_auth' enabled)

I tried to connect to the database with the MySQL workbench, and it's working only if I select the option: "use the old authentication protocol". Hence I know that it is what I need to activate in my connection, also using R. However I don't know how to do this! I tried to disable "secure_auth" but dbConnect does not have that parameter.

How can I change the authorization? Thanks in advance!

P.s.: I can't upgrade the server password to make it compatible with post-4.1.1 protocol.

Gavin Simpson
  • 170,508
  • 25
  • 396
  • 453
user3122861
  • 181
  • 3
  • Don't you need the server's ip to connect? This is what i've been using and works but the server is in the local lan. So i don't know if it would help you. ```con <- dbConnect("MySQL", user = "username", dbname = "DatabaseName", host = "192.168.1.4", password = "yourpassword")``` – marbel Dec 20 '13 at 19:31

2 Answers2

0

You may want to try to connect to MySQL with RJDBC instead - it will connect with the JDBC driver under the covers, which apparently will allow old-style authentication.

Community
  • 1
  • 1
atomic77
  • 178
  • 1
  • 3
0

Depending on the size of the DB, you can Clone the remote DB to a local one using MySQL Workbench. Then it's just a local connection, and you can disable/control authentication on it.