Error: R cannot connect to MySQL

Question

I updated MySQL to latest version 8.0.11 yesterday and tried to use RMySQL to connect to MySQL, but it was not working and I could not find any solution.

My code:

library(RMySQL) 
con <- dbConnect(MySQL(), user="hello", password="hi", dbname = "webscrape", host="xx.xxx.xxx.xxx", port=xxxx)

Error:

Error in .local(drv, ...) : 
  Failed to connect to database: Error: Can't initialize character set unknown (path: compiled_in)

Answer 1

I solved this problem like this:

User Windows

1.Error in .local(drv, ...) : Failed to connect to database: Error: Can't initialize character set unknown (path: compiled_in)

After adding these lines:

file path: %PROGRAMDATA%\MySQL\MySQL Server X.X\my.ini

    [mysqld]
    collation-server = utf8_unicode_ci
    init-connect='SET NAMES utf8'
    character-set-server = utf8
    skip-character-set-client-handshake

    [client]
    default-character-set   = utf8

    [mysql]
    default-character-set   = utf8

2- Authentication plugin 'caching_sha2_password' cannot be loaded

    ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'youpassword';

References:

MySQL my.ini location

Change MySQL default character set to UTF-8 in my.cnf?

Authentication plugin 'caching_sha2_password' cannot be loaded

Answer 2

Had the same problem. Tried changing character sets on the schema. Also tried reinstalling MySQL with the legacy authentication method option and still no luck with RMySQL.

Tried RMariaDB package and it worked. I would switch to RMariaDB rather than downgrading MySQL.

https://cran.r-project.org/web/packages/RMariaDB/