2

I am learning how to use MySQL in R with the package RMySQL:

https://www.tutorialspoint.com/r/r_database.htm

but when I try to connect to the DB provided in the example

mysqlconnection = dbConnect(MySQL(), user = 'root', password = '', dbname = 'sakila', host = 'localhost')

I get:

 Failed to connect to database: Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

My guess is that I am running it simply on my laptop (mac os), so if my guess is right (?) I need some virtual server.

Do you know any R package that does so? Any suggestion appreciated.

Chandana Kumara
  • 2,485
  • 1
  • 22
  • 25
Xavier Prudent
  • 1,570
  • 3
  • 25
  • 54
  • I got a similar problem and fixed it by using RMariaDB instead of RMySQL. Now it works perfectly (RMySQL is deprecated). – Simon C. Jan 11 '21 at 18:34

2 Answers2

3

First check your sql service is running.

service mysqld start

Then try mysql -u root

If not solve issue from above solution try with host = '127.0.0.1' instead of localhost. Because 127.0.0.1 use TCP/IP connector. Unless localhost run with socket connector.

Chandana Kumara
  • 2,485
  • 1
  • 22
  • 25
  • thanks, should the "service" and "mysql" commands be run in the R promp? – Xavier Prudent Nov 13 '16 at 05:28
  • Nop. your os console. – Chandana Kumara Nov 13 '16 at 05:32
  • https://coolestguidesontheplanet.com/start-stop-mysql-from-the-command-line-terminal-osx-linux/ – Chandana Kumara Nov 13 '16 at 06:09
  • I installed mysql, started it and typed: sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist Password: com.oracle.oss.mysql.mysqld: Already loaded, then in the R prompt: mysqlconnection = dbConnect(MySQL(), user = 'root', password = '', dbname = 'sakila', host = '127.0.0.1') Error in .local(drv, ...) : Failed to connect to database: Error: Access denied for user 'root'@'localhost' (using password: NO) – Xavier Prudent Nov 13 '16 at 15:07
  • Pls check here for solution:http://stackoverflow.com/questions/6445917/connect-failed-access-denied-for-user-rootlocalhost-using-password-yes – Chandana Kumara Nov 15 '16 at 05:26
  • `host = '127.0.0.1' instead of localhost` helped me – sunkuet02 Oct 30 '19 at 12:00
  • Host means mys sql connection host. [mysqlconnection = dbConnect(MySQL(), user = 'root', password = '', dbname = 'your_db_name',host = 'localhost'] – Chandana Kumara Nov 01 '19 at 02:51
0

I also had the same issue, but the instructions in https://www.tutorialspoint.com/r/r_database.htm worked well for me. This are my steps

1. Install and load "odbc" and "RMySQL" packages

library(odbc);library(RMySQL)

2. Setup the connection

mysqlconnection = dbConnect(MySQL(), user = 'root', password = '', dbname = 'your_db_name',host = 'localhost')

3. List all your tables in your_db_name

dbListTables(mysqlconnection)

4. Now you can write queries

result = dbSendQuery(mysqlconnection,"select * from your_table_name limit 0,30")
Nilupa Rupasinghe
  • 1
  • 2