Installing Rmysql in Mavericks

Installing RMySQL in mavericks

I have successfully installed RMySQL 0.9-3 from source for Mac:

• OS X Mavericks 10.9.4
• R 3.1.0 and RStudio 0.98.945 installed from CRAN and rstudio.com
• Xcode 5.1.1 and the corresponding command-line tools

There are alternatives to achieve the same goal, but I will try to provide the friendliest way. Here are the major steps:

1. Make sure you have "gcc" available. (looks like you do)

Go to Applications > Utilities, open Terminal, and type the following command to verify the command line tools exist: ($ is the command prompt in Terminal)

$ gcc
clang: error: no input files

If you receive -bash: gcc: command not found, you will need to install the command-line tools (gcc) in the standalone or Xcode way.

2. Install MySQL client via Homebrew.

Homebrew is a package management system that simplifies the installation of software on the Mac OS X operating system.

First, Install Homebrew from Terminal:

$ ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

After that, type "brew" command to check if Homebrew is installed. Then install MySQL via Homebrew:

$ brew install mysql

You should see the Downloading, Pouring, Caveats, and finally the Summary showing that MySQL is installed.

You can verify the installation by connecting to the local MySQL server as root with (default) empty password:

$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.19 Homebrew
... (skipped) ...

mysql> 

Use "exit" to exit the MySQL shell. You may have problems starting or connecting to your local MySQL server, but that's beyond the scope.

3. Install RMySQL from source in RStudio.

So far, all the steps are done in Terminal. Although this step can also be done in Terminal, I will show how to do it in RStudio. From your error message:

Configuration error:
  could not find the MySQL installation include and/or library
  directories.  Manually specify the location of the MySQL
  libraries and the header files and re-run R CMD INSTALL.

INSTRUCTIONS:

1. Define and export the 2 shell variables PKG_CPPFLAGS and
   PKG_LIBS to include the directory for header files (*.h)
   and libraries, for example (using Bourne shell syntax):

      export PKG_CPPFLAGS="-I<MySQL-include-dir>"
      export PKG_LIBS="-L<MySQL-lib-dir> -lmysqlclient"

   Re-run the R INSTALL command:

      R CMD INSTALL RMySQL_<version>.tar.gz

This means that R can't find the header files (include things) to include and the libraries (lib things) to link to.

The instruction tells you to set 2 environment variables PKG_CPPFLAGS and PKG_LIBS to indicate where the include and lib are.

Assume you have installed MySQL with the default paths in Homebrew. Within RStudio, you can set them by: (> is the command prompt in RStudio)

### These are the KEY COMMANDS in this turotial ###
> Sys.setenv(PKG_CPPFLAGS = "-I/usr/local/include/mysql")
> Sys.setenv(PKG_LIBS = "-L/usr/local/lib -lmysqlclient")

Finally, you should be able to install RMySQL from source correctly! Either from CRAN repo or the local file.

> install.packages("RMySQL", type = "source")

or

> install.packages("/path/to/package/RMySQL_0.9-3.tar.gz", repos = NULL, type = "source")

Both will give you the success message:

** libs
clang -I/Library/Frameworks/R.framework/Resources/include -DNDEBUG -I/usr/local/include/mysql/ -I/usr/local/include -I/usr/local/include/freetype2 -I/opt/X11/include    -fPIC  -Wall -mtune=core2 -g -O2  -c RS-DBI.c -o RS-DBI.o
clang -I/Library/Frameworks/R.framework/Resources/include -DNDEBUG -I/usr/local/include/mysql/ -I/usr/local/include -I/usr/local/include/freetype2 -I/opt/X11/include    -fPIC  -Wall -mtune=core2 -g -O2  -c RS-MySQL.c -o RS-MySQL.o
clang -dynamiclib -Wl,-headerpad_max_install_names -undefined dynamic_lookup -single_module -multiply_defined suppress -L/usr/local/lib -o RMySQL.so RS-DBI.o RS-MySQL.o -L/usr/local/lib/ -lmysqlclient -lz -F/Library/Frameworks/R.framework/.. -framework R -Wl,-framework -Wl,CoreFoundation
installing to /Library/Frameworks/R.framework/Versions/3.1/Resources/library/RMySQL/libs
** R
** inst
** preparing package for lazy loading
Creating a generic function for ‘format’ from package ‘base’ in package ‘RMySQL’
Creating a generic function for ‘print’ from package ‘base’ in package ‘RMySQL’
** help
*** installing help indices
** building package indices
** testing if installed package can be loaded
* DONE (RMySQL)

As usual, load the RMySQL package by:

> library(RMySQL)
Loading required package: DBI
> 

---

Note: The install.packages() in R actually runs R CMD INSTALL xxx in the Terminal (Unix shell) environment. So if you prefer the Terminal way, you can also set PKG_CPPFLAGS and PKG_LIBS by the export command in Terminal, and run R CMD INSTALL RMySQL_xxx.tar.gz to install from the source package you have manually downloaded.

So the following way will also work in Terminal for Step 3:

$ export PKG_CPPFLAGS="-I/usr/local/include/mysql"
$ export PKG_LIBS="-L/usr/local/lib -lmysqlclient"
$ R CMD INSTALL RMySQL_0.9-3.tar.gz

Trying to get RmySQL to work but not understanding bash's export or filesystem conventions

I am going to assume you're trying to get RmySQL to run on R 3.1.0 on Mavericks? Rather than worry about exporting variables etc, here is a simple clean solution for you that should avoid the headaches.

The RMySQL install link Pascal provided above really is your solution. You're probably just stumbling on syntax, or getting things to work from the terminal.

Even if you're a "noob", you should be able to get this working. I'll try to offer a "dummy's guide" walk through here, as I bet there are many others who have this problem too, even after trying to read the RMySQL installation readme.

I would bet with very high confidence the problem is just that you aren't specifying correctly the locations of the library and header folders for compiling. Read the errors carefully when you try to compile... the errors will probably tell you a file/header is missing, or some .so file (shared object) is missing.

One simple way compile RMySQL from source on R 3.1.0, mavericks is as follows (this does not require you to set any environmental variables, no editing of the Renviron file, etc):

1. Does MySQL work by itself? i.e. Can you open/run it no problems? If not, fix that first.

2. Find the precise location of your mysql installation. For me, on Mavericks, I see mysql installed at /usr/local/mysql-5.6.17-osx10.7-x86_64 (your version number may be different). There is also another folder /usr/local/mysql which is an alias to /usr/local/mysql-5.6.17-osx10.7-x86_64 (/usr/local/mysql finds the current version of mysql you are using, if multiple mysql file folders exist, I think). In this directory, I see two sub directories (among many) called "include" and "lib". Take a look; "include" will contain header files (include as in #include , etc, in simple C++ programs). The "lib" folder contains compiled source code of the mysql library.

3. An easy way to compile and install RMySQL which doesn't exactly follow the suggested way to do it in the installation guide is this. Note that this is doing the same thing as in the installation guide, just a little easier as it's one command line from the terminal, once you know where your mysql install folder is. Go to the terminal, and type the following exactly, with one space between each chunk (with your mysql folder name adjusted appropriately for the version number):

PKG_CPPFLAGS="-I/usr/local/mysql/include/" PKG_LIBS="-L/usr/local/mysql/lib/ -lmysqlclient" R CMD INSTALL RMySQL_0.9-3.tar.gz

OR (the same thing, just more typing)

PKG_CPPFLAGS="-I/usr/local/mysql-5.6.17-osx10.7-x86_64/include/" PKG_LIBS="-L/usr/local/mysql-5.6.17-osx10.7-x86_64/lib/ -lmysqlclient" R CMD INSTALL RMySQL_0.9-3.tar.gz

Note for dummies: Make sure when you run this command, that you are doing it from the terminal in the directory that contains the RMySQL_0.9-3.tar.gz file (or whatever the name of your folder is that contains the RMySQL source code)

and RMySQL compiles!

Don't be afraid about trying to compile source code -- it's not just for 'compiled language programmers' or 'computer science graduates'. Most of the time when compiling fails it's just because files are "missing" (there is no corruption on the source code) -- the user hasn't properly specified the locations of the header and libraries (shared objects). Now pull your big boy/girl panties up and just do it .... it's easy.

Notes for people clueless about compiling source code for packages in R:

a) pay special attention to the spacing in the above, otherwise it may not work. Do not have any spaces between the = and the variable/file names (e.g. don't try and have in the above PKG_CPPFLAGS ="-I/usr/local/mysql/include/" as it won't work)

b) When compiling, you want to specify the locations of the header files and the library files and this is what the "-I/ .... " and "-L/ ...." are doing. The -I directory specifies the location of the header files, and the -L the location of the library files. The library files also require the -l[name of library] extension (the -l is short for -lib in the library object names).

c) Note that in the directory /usr/local/mysql-5.6.17-osx10.7-x86_64/lib/ I do not see a file called "lmysqlclient", or even "libmysqlclient", but I do see files named (among others) "libmysqlclient.a" and "libmysqlclient.18.dylib". So don't worry about your MySQL installation not being correct if you don't see a file just called "libmysqlclient" in the lib folder.

package ‘RMySQL’ is available as a source package but not as a binary

If you don't mind installing another MySQL server via Homebrew, I have posted the complete solution in another thread: Installing RMySQL in mavericks

---

But in your case, I do find those directories in XAMPP that contain the same necessary MySQL header and library files as those installed via Homebrew.

So you can set the environment variables and build in the Terminal way:

% export PKG_CPPFALGS="-I/Applications/XAMPP/xamppfiles/include"
% export PKG_LIBS="-L/Applications/XAMPP/xamppfiles/lib -lmysqlclient"
## RMySQL_x.x-x.tar is the source package you have manually downloaded
% R CMD INSTALL RMySQL_x.x-x.tar

or in the RStudio way:

> Sys.setenv(PKG_CPPFLAGS = "-I/Applications/XAMPP/xamppfiles/include")
> Sys.setenv(PKG_LIBS = "-L/Applications/XAMPP/xamppfiles/lib -lmysqlclient")
## It will automatically download the source package in the remote repo
> install.packages("RMySQL", type = "source")

How to Connect R with MySQL or how to install RMySQL package?

It is not a direct answer but still you may find it helpful:

1. Use a more up to date version of R (currently at 2.15)

2. On Windows platforms I'd rather use RODBC + Windows MySQL driver, unless you are in a environment with heterogenous platforms (i.e. Linux and Windows) where code is heavily shared among team members. And even then choosing between using RMySQL and RODBC in the same script depending on the platform it runs is a simple if() {...} else {...}

Notice that I am not saying there are no success stories with what you are trying to do, but IMHO you'll be up and running sooner with the above.

Error installing RMySQL on OS X

Take a look at this memo from Simon Urbanek on the R-sig-Mac list in February:

https://stat.ethz.ch/pipermail/r-sig-mac/2013-February/009967.html

"Ah, hold on - you are on Mountain Lion and you may have installed R with 32-bit default since 10.8 was omitted from the list of supported 64-bit systems at the time - try installing latest R 2.15.3 RC from
http://r.research.att.com/"

You should also make sure that the RMySQL version is appropriate for version 2.15 since CRAN would now be serving up a version that expects R 3.0.0 to be running. Actually that should not be the problem since RMySQL_0.9-3 is in the 2.15 binaries trunk at the UCB CRAN mirror: http://cran.cnr.berkeley.edu/bin/macosx/leopard/contrib/2.15/RMySQL_0.9-3.tgz

brew install mysql 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:

1. installed mysql with brew install mysql

2. 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
   

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

4. Used the alternate security script:

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

5. 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!

---

Related Topics