How to connect R to MySQL? Failed to connect to database: Error: Plugin caching_sha2_password could not be loaded
Solution 1
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
Solution 2
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)
Phil
Updated on June 11, 2022Comments
-
Phil about 2 years
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()
orsrc_mysql
inRStudio
, 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)
-
danblack almost 3 yearsWith 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.
-
Jonathan Charlton almost 3 yearsReally, 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.