Access to MySQL with R using a pre 4.1.1 authentication protocol

Question

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.

Answer 1

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.