14

I recently installed MySQL on my computer and am trying to connect RStudio to MySQL. I followed instructions in a book as well as instructions here. However, whenever I use dbConnect() or src_mysql in RStudio, I get this error message:

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

For example, I might login to MySQL using the command prompt in Windows

mysql -u username -p

and create a database as follows

CREATE DATABASE myDatabase;

and then in RStudio:

library(RMySQL) db <- dbConnect(MySQL(), dbname = "myDatabase", user = "username", password = "password", host = "localhost")

and my response is always that error message listed above.

And if you need it: sessionInfo()

R version 3.5.2 (2018-12-20) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows >= 8 x64 (build 9200)
Improve this question

2 Answers 2

Reset to default
13

The R mysql library depends on libmysqlclient/libmariadbclient. The missing caching_sha2_password seems to be an indicator that an old mysqlclient version or a libmariadbclient isn't installed. Only very recently did caching_sha2_password get added to mariadb (3.0.8)

An alternative, like this answer, is to change the user in mysql to use a different authentication mechanism:

You set the user back to mysql_native_password:

ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'

To make this the default for all newly created users change the my.cnf/my.ini setting default_authentication_plugin=mysql_native_password

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

With Ubuntu Bug 1913676 fixed now the Linux side should be solved for all (except Ubuntu-18.04 which is too old). Windows R connecting to MySQL-8.0 is still a problem.
Really, this Windows R connecting to MySQL-8.0 isn't just still a problem, it's a REAL problem. Thank you for referencing those comments danblack.
3

Step 1: OPEN THE mySql 8.0 command client

Step 2: To list all users in database, type the command, select host,user from mysql.user;

Step 3: Now reset the current user password as,
set password for 'root'@'localhost'='yourpassword';

Step 3: Last Step

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword'; Query OK, 0 rows affected (0.12 sec)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.