RMySQL on OSX

I ran into an issue installing RMySQL on OSX. I am using a R from brew and MySQL from the Oracle download page. Trying to install RMySQL would throw up this error

	** installing vignettes
	** testing if installed package can be loaded
	Error : .onLoad failed in loadNamespace() for 'RMySQL', details:
	call: dyn.load(file, DLLpath = DLLpath, ...)
	error: unable to load shared object '/usr/local/Cellar/r/3.0.1/R.framework/Versions/3.0/Resources/library/RMySQL/libs/RMySQL.so':
	dlopen(/usr/local/Cellar/r/3.0.1/R.framework/Versions/3.0/Resources/library/RMySQL/libs/RMySQL.so, 6): Library not loaded: libmysqlclient.18.dylib
	Referenced from: /usr/local/Cellar/r/3.0.1/R.framework/Versions/3.0/Resources/library/RMySQL/libs/RMySQL.so
	Reason: image not found
	Error: loading failed
	Execution halted
	ERROR: loading failed
	* removing ‘/usr/local/Cellar/r/3.0.1/R.framework/Versions/3.0/Resources/library/RMySQL’
	sh-3.2# R CMD INSTALL /private/var/folders/n0/4_22_21n66x537xg2kdnc618m0j889/T/RtmpehnXuM/downloaded_packages/RMySQL_0.9-3.tar.gz -h
	Loading required package: stats
	Usage: R CMD INSTALL [options] pkgs

view raw gistfile1.txt hosted with ❤ by GitHub

Turns out that the right solution is really easy. Set your MySQL home R env var, make sure that libmysqlclient can be found by R, configure the RMySQL package with the proper inc & lib dirs for MySQL

	# Find your R home
	$ echo "R.home()" | Rscript /dev/stdin
	Loading required package: stats
	Loading required package: methods
	[1] "/usr/local/Cellar/r/3.0.1/R.framework/Resources"
	# Make sure that you have the MySQL home var in your Renviron
	$ fgrep MYSQL_HOME /usr/local/Cellar/r/3.0.1/R.framework/Resources/etc/Renviron
	MYSQL_HOME="/usr/local/mysql-5.6.12-osx10.7-x86_64/"
	#### This step trips up everyone ####
	# Make sure that the libmysqlclient dynamic lib is in your R lib path by
	# 1. Get the dll path
	$ echo "Sys.getenv('DYLD_FALLBACK_LIBRARY_PATH')" | Rscript /dev/stdin
	Loading required package: stats
	Loading required package: methods
	[1] "/usr/local/Cellar/r/3.0.1/R.framework/Resources/lib"
	# 2. Symlink your libmysqlclient
	$ ln -s /usr/local/mysql-5.6.12-osx10.7-x86_64/lib/libmysqlclient.18.dylib /usr/local/Cellar/r/3.0.1/R.framework/Resources/lib
	# 3. Install the package
	$ export PKG_LIBS="-L/usr/local/mysql-5.6.12-osx10.7-x86_64/lib/ -lmysqlclient"
	$ export PKG_CPPFLAGS="-I/usr/local/mysql-5.6.12-osx10.7-x86_64/include/"
	$ R CMD INSTALL RMySQL.tar.gz
	...
	...
	** installing vignettes
	** testing if installed package can be loaded
	* DONE (RMySQL)

view raw RMySQL_works.sh hosted with ❤ by GitHub

Tests!

	> library("RMySQL")
	Attaching package: ‘RMySQL’
	The following object is masked from ‘package:RSQLite’:
	dbBuildTableDefinition, isIdCurrent, safe.write
	> library("RMySQL")
	> m <- dbDriver("MySQL")
	> con <- dbConnect(m, user="root", host="localhost")
	> rs <- dbSendQuery(con, "show databases")
	> fetch(rs, n=100)
	Database
	1 information_schema
	2 xxxxxxxxxx
	3 mysql
	4 performance_schema
	5 test
	> rs <- dbSendQuery(con, "select * from information_schema.STATISTICS limit 5;")
	> fetch(rs, n=100)
	TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT
	1 def mysql columns_priv 0 mysql PRIMARY 1 Host A NA NA <NA> BTREE
	2 def mysql columns_priv 0 mysql PRIMARY 2 Db A NA NA <NA> BTREE
	3 def mysql columns_priv 0 mysql PRIMARY 3 User A NA NA <NA> BTREE
	4 def mysql columns_priv 0 mysql PRIMARY 4 Table_name A NA NA <NA> BTREE
	5 def mysql columns_priv 0 mysql PRIMARY 5 Column_name A 0 NA <NA> BTREE
	INDEX_COMMENT

view raw tests.r hosted with ❤ by GitHub