CODEWITHSUNDEEP
mysqlrmariadb
cjolley
cjolley

Reputation: 451

RMariaDB: caching_sha2_password error when connecting to MySQL

I'm new to using database connections with R (under Ubuntu 16.04), and am running into some trouble. Following the documentation I've seen online, I tried something like this:

con <- DBI::dbConnect(RMySQL::MySQL(),  
             dbname='IFsHistSeries',   
             host='127.0.0.1')

This got me an error message that read:

Failed to connect to database: Error: Can't initialize character set unknown (path: compiled_in)

From what I was able to find using the google, it seems that RMariaDB is more up-to-date than RMySQL, so I gave that a shot instead:

con <- DBI::dbConnect(RMariaDB::MariaDB(),
             dbname='IFsHistSeries',
             host='127.0.0.1')

This resulted in an equally-cryptic error message:

Failed to connect: Plugin caching_sha2_password could not be loaded:

So then I tried something really foolish -- installing MariaDB using the instructions at https://downloads.mariadb.org/mariadb/repositories/. There were some dependency problems that managed to completely break MySQL so that now nothing works at all anymore. I'm planning to completely uninstall MySQL and MariaDB and start over from scratch. What I want to know for next time is:

Any ideas at all would help a lot. Thanks!

--craig

Upvotes: 1

Views: 2452

Answers (2)

Yuchang Ke
Yuchang Ke

Reputation: 1

You can write mysql_native_password as default-authentication-plugin to my.cnf file.

Upvotes: 0

cjolley
cjolley

Reputation: 451

OK... this seemed to work. First, I removed MySQL and MariaDB and installed 8.0.12 MySQL using the download from https://dev.mysql.com/downloads/mysql/ and followed instructions at https://dev.mysql.com/doc/refman/8.0/en/binary-installation.html.

I uninstalled all of my drivers and just reinstalled unixODBC sudo apt-get remove unixodbc unixodbc-dev tdsodbc odbc-postgresql libmyodbc libsqliteodbc sudo apt-get install unixodbc unixodbc-dev

Things went awry before when I installed libmariadbclient-dev. Instead, I ran

sudo apt-get install libmariadb-client-lgpl-dev

This installed the MariaDB client without breaking MySQL. I really don't know what the difference between the two is.

To get rid of the error with the caching_sha2_password plugin, I needed to change the default authentication mode when starting the MySQL server:

sudo ./mysqld_safe --user=mysql --default-authentication-plugin=mysql_native_password &

At this point, I could run

con <- dbConnect(RMariaDB::MariaDB(),
                 host='127.0.0.1',
                 user='root',
                 password='password')

Without any trouble.

Upvotes: 1

Related Questions