RMySQL issues connecting to sql: also trouble thru terminal

[sklinger]

but I am really trying to use R and this is what is happening


conn <- dbConnect(MySQL() , user = 'root' , pwd = 'NYY@NK33s' , dbname = 'BDB_2011_03_23')


Error: unexpected symbol in:
"conn <- dbConnect(MySQL() , user = 'root' , pwd = 'NYY@NK33s' , dbname = 'BDB_2011_03_23' 
  host"

thanks for any help here

[technocrat]

library(DBI)
library(knitr)
library(kableExtra)
library(RMySQL)
drv <- dbDriver("MySQL")
con <- dbConnect(drv, username="root", password="[IF ANY]", dbname ="YOUR_DB", host="localhost")

res <- dbGetQuery(con, "SELECT field1, field2 FROM table1")

[sklinger]

thanks.
I am also going to uninstall and reinstall the mysql and mysql workbench. I am in that process now. not sure if it will help, but the update to new r made things smooth, and I have nothing in MySQL now. you guys are great helps

[sklinger]

technocrat
unfortunately, i am getting errors. for some reason default is setting not to root but to user in workbench for mysql, i am identified as user . in there i need to switch to root each time. so I did try your instructions with both user and root. this is my error i don't understand. on separate note, thru terminal I connect w mysqlsh --uri root@localhost. I am real confused here, with the user and root issue. i install everything w defualts, i din't change from root. frustration.

but it should be root for rmysql is my long point here

con <- dbConnect(drv, username="root", password="NYY@NK33s", dbname ="RBaseball", host="localhost")
Error in .local(drv, ...) : 
  Failed to connect to database: Error: Plugin caching_sha2_password could not be loaded: dlopen(lib/mariadb/plugin/caching_sha2_password.so, 2): image not found
> con <- dbConnect(drv, username="user", password="NYY@NK33s", dbname ="RBaseball", host="localhost")
Error in .local(drv, ...) : 
  Failed to connect to database: Error: Plugin caching_sha2_password could not be loaded: dlopen(lib/mariadb/plugin/caching_sha2_password.so, 2): image not found

[technocrat]

What you're seeing is not a user role (root/non-root) error, but a missing .so file problem in your MariaDB install.

caching_sha2_password.so, 2): image not found

https://goo.gl/SzEsSW gives an idea of the issue on MySQL after some googling -- the battle of authentication protocols. I used to waste hours trying to trap and dissect this kind of bug when I had it, but nowadays I'm more likely to install the latest version (https://goo.gl/RqUYEZ) and see where that gets me.

There might be a way to flush the password cache, but that could be just another rabbit hole. If you're up to it, re-install and report back. Ping me @technocrat if I miss your update.

Good luck.

[sklinger]

i seem to have bugs with a=just about everything. thats exaggeration of course, but way too many times. I see you're suggestion is mariaDB instead of mysql? I am tempted. but I for last near 2 months now I have been reading "analyzing baseball data with R' and I have a dated edition, I try and stick to it. once things get installed I have been having a ton of fun. I feel foolish when these bugs show up.

right now I want to try and connect RmariaDB to mysql, because I just made first database thru workbench and got it on server.

> con <- dbConnect(RMariaDB::MariaDB(), group = "BDB_2013_03_23")
Error in connection_create(host, username, password, dbname, as.integer(port),  : 
  Failed to connect: Plugin caching_sha2_password could not be loaded: dlopen(lib/mariadb/plugin/caching_sha2_password.so, 2): image not found
> 

the BDB_2013_03_23 is the database I created and trying to reach
I got this from github site from download.  I also used the original code directly from them
I am tired and may be repeating .  I will have to try again tomorrow
I appreciate your help tonight

[technocrat]

Sorry for the difficulties.

MariaDB is a MySQL workalike from a user standpoint. I assumed somewhere from your error messages that you're on MariaDB, rather than MySQL.

The best way to find out is to type in the terminal

mysql -u root -p

And see if you set back a prompt identifying itself as mysql or MariaDB, you'll know which.

But you don't have to specify which flavor you're using if you use

library(DBI)
library(RMySQL)
drv <- dbDriver("MySQL")
con <- dbConnect(drv, username="root", password="", dbname ="dlf", host="localhost")

Change password from blank if you have one and change dlf to the name of the database. It won't matter whether the db engine is MySQL or MariaDB.