Mysql – Connecting to MySQL from R

mysqlrrmysql

I am trying to connect to MySQL from R. I have installed "8.0.11 MySQL Community Server – GPL" on my machine. In R studio, I have installed RMySQL Library.

When I give the command:

con = dbConnect(RMySQL::MySQL(),user="root", password = "password", dbname="test")

I keep getting the error:

Error in .local(drv, …) : Failed to connect to database: Error:
Unknown database 'test'

I am not sure why it keep giving this error. Any suggestions?

Best Solution

Here is a code I use for access to MySQL from R

# 1. Library
library(RMySQL)

# 2. Settings
db_user <- 'your_name'
db_password <- 'your_password'
db_name <- 'database_name'
db_table <- 'your_data_table'
db_host <- '127.0.0.1' # for local access
db_port <- 3306

# 3. Read data from db
mydb <-  dbConnect(MySQL(), user = db_user, password = db_password,
                 dbname = db_name, host = db_host, port = db_port)
s <- paste0("select * from ", db_table)
rs <- dbSendQuery(mydb, s)
df <-  fetch(rs, n = -1)
on.exit(dbDisconnect(mydb))

Please, check how it works on your side.

PS. Looks like you miss 'db_table' parameter.

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Mysql – brew install thesql on macOS

I think one can end up in this position with older versions of mysql already installed. I had the same problem and none of the above solutions worked for me. I fixed it thus:

Used brew's remove & cleanup commands, unloaded the launchctl script, then deleted the mysql directory in /usr/local/var, deleted my existing /etc/my.cnf (leave that one up to you, should it apply) and launchctl plist

Updated the string for the plist. Note also your alternate security script directory will be based on which version of MySQL you are installing.

Step-by-step:

brew remove mysql

brew cleanup

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

sudo rm -rf /usr/local/var/mysql

I then started from scratch:

installed mysql with brew install mysql

ran the commands brew suggested: (see note: below)

unset TMPDIR

mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp

Start mysql with mysql.server start command, to be able to log on it

Used the alternate security script:

/usr/local/Cellar/mysql/5.5.10/bin/mysql_secure_installation

Followed the launchctl section from the brew package script output such as,

#start
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

#stop
launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

Note: the --force bit on brew cleanup will also cleanup outdated kegs, think it's a new-ish homebrew feature.

Note the second: a commenter says step 2 is not required. I don't want to test it, so YMMV!

Best Solution

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Mysql – brew install thesql on macOS

Related Question