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)

Antispasmodic

I am getting the same error in the latest version of R and MySQL. This was not coming in older versions I was using earlier. – Amboina

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

Pentad

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/

Horacehoracio

Answer 3

I had this problem as well and I noticed in MySQL 5.8, which was released recently, the default character set of the server and client connections have now been set to utf8mb4 by default. With MySQL 5.7 the default character set was set to utf8.

I tried playing around with the character sets of the server and and client, reverting the values to utf8 but I just couldn't get it to work so I reverted my installed MySql to 5.7. RMySQL worked then after.

It feels like RMySQL cannot support utf8mb4 at the moment and needs to be updated.

Vespers

Answer 4

Related to this problem, one solution that would prevent you from changing your R preferred library and/or downgrading MySQL can be found at this post.

You just need to change your my.cnf settings.

Aweather

Answer 5

I have the same Pb :

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

I try the solution of Hugo (modify the my.ini), David Guerin (using RMariaDB) but both are not working. After some research and experience this solution is working for me

Modify my.ini (this is the path to find C:\ProgramData\MySQL\MySQL Server 8.0) :

By opposit to the answer of Hugo, you need to put utf8mb4 (utf8 is not the good one)

[client]

default-character-set = utf8 (the old line "utf" to "utfmb4")
default-character-set = utf8mb4 (the new line with modification)

[mysql]

default-character-set = utf8 (the old line "utf" to "utfmb4")
default-character-set = utf8mb4 (the new line with modification)

[mysqld]

character-set-client-handshake = FALSE  (new line to add)
character-set-server = utf8mb4  (the old line "utf" to "utfmb4")        
collation-server = utf8mb4_unicode_ci   (new line to add)
skip-character-set-client-handshake (Line to remove)

In mysql, you need to change your password :

ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY 'mot-passe';

Turpentine