Connecting R environment to Mysql databases on Unix system

Question

[marcelelaux]

Hello Guys

I have a really (long time) important question here, and I will really appreciate any help

I'd like to perform several statistical tasks using R environment under unix system. Actually up to now I have used R Studio (windows), for ecology studies, but now I have huge tables of metagenomic data to analyze.

I created my Mysql database (unix) of metagenomic data, with reads and uniprot's and Knumbers and taxid's and so so so on. This means that I have dozens of tables with thousands (even millions) entries. Now I have to perform some tasks unavailable on Mysql functions, like variance analysis, PCA, cluster, regression, even stepwise model choices.

I have tried to install the RMySQL and dbConnect packages on R Studio, but I got the message:

Warning in install.packages :
  package 'dbConnect' is not available (for R version 3.1.0)

I searched for this error and none of the available suggestions and comments and possible solutions worked to me.

Besides of this, what would be really efficient would be do this connection between R and Mysql under command line unix system.

My R version on unix is

R version 3.1.0 (2014-04-10) -- "Spring Dance"
Copyright (C) 2014 The R Foundation for Statistical Computing
Platform: x86_64-redhat-linux-gnu (64-bit)

My MySQL version is:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1081 #----> I think that this id will be important, right?
Server version: 5.1.73 Source distribution

I don't know what to do, I searched for this in many blogs, forums, I asked my colleagues and advisors, no one around me could help me, for now.

You can just send to me links and any kind of material (books, tutorials), I can try to do this by myself, but I need, at least, a objective reading about this (or someone that did this).... I have dozens of R books and tutorials and none of them help to do this.

Thank You!

Merry Christmas!

[Pierre Lindenbaum]

"dbConnect: Provides a graphical user interface to connect with databases that use MySQL" <- do you really need this package ? RMySQL should be enough.

[marcelelaux]

Yes, sure, you are right, I tried dbConnect too because RMySQL doesn't work, but what I need is the later.

Thanx

Answer 1

[Ram]

Quick possible solution: Can you try running

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

unless you tried that already? If you did and it did not succeed, the error you face would help get to the next step.

[marcelelaux]

I tried this command and a "+" appear, so I tried this one:

install.packages("RMySQL", lib="/my/own/R-packages/")

then selected a CRAN mirror, a lot of checks were performed and I got this 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>" ----> **How I get this information of MySQL directory?**
      export PKG_LIBS="-L<MySQL-lib-dir> -lmysqlclient"

   Re-run the R INSTALL command:

      R CMD INSTALL RMySQL_<version>.tar.gz

2. Alternatively, you may pass the configure arguments
      --with-mysql-dir=<base-dir> (distribution directory)
   or
      --with-mysql-inc=<base-inc> (where MySQL header files reside)
      --with-mysql-lib=<base-lib> (where MySQL libraries reside)
   in the call to R INSTALL --configure-args='...'
</pre>
<pre>
   R CMD INSTALL --configure-args='--with-mysql-dir=DIR' RMySQL_<version>.tar.gz
</pre>
<pre>
ERROR: configuration failed for package 'RMySQL'
* removing '/root/R/x86_64-redhat-linux-gnu-library/3.1/RMySQL'

The downloaded source packages are in
        '/tmp/RtmpnPMTpK/downloaded_packages'
Warning message:
In install.packages("RMySQL", lib = "/my/own/R-packages/") :
  installation of package 'RMySQL' had non-zero exit status

PS: I have root access.

Thanx for help

[Ram]

Sorry, there was a mistake in matching quotes. I've updated the command, please try it again.

[marcelelaux]

Yes, now install.packages('RMySQL', type='source') command worked, but with the same error :(

I think that I really need to this this:

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.

But I don't know how to locate this information...

Thanx

[Ram]

Please give me the output of:

$which mysql

from the UNIX command prompt

[marcelelaux]

It just logged to MySQL:

[marlaux@Frevo ~]$ $which mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1084
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement

[Ram]

The $ sign was to denote a shell command, please just run this as-is on the shell:

which mysql

[Ram]

OK, so after this, you ran a which mysql to which you got the output /usr/bin/mysql

From this link, your MYSQL INC and LIB directories are:

• INC: /usr/include/mysql
• LIB: /usr/lib/mysql

Now try running these on the Unix shell:

R CMD INSTALL --configure-args='--with-mysql-inc=/usr/include/mysql --with-mysql-lib=/usr/lib/mysql' RMySQL_0.9-3.tar.gz

[marcelelaux]

I got this:

[root@Frevo marlaux]# R CMD INSTALL --configure-args='--with-mysql-inc=<base-inc> --with-mysql-lib=<base-lib>' RMySQL_0.9-3.tar.gz

*installing to library '/root/R/x86_64-redhat-linux-gnu-library/3.1'
* installing *source* package 'RMySQL' ... 
** package 'RMySQL' successfully unpacked and MD5 sums checked
sh: -c: line 0: syntax error near unexpected token `newline'
sh: -c: line 0:`*R_SHLIB_BUILD_OBJECTS_SYMBOL_TABLES*=false ./configure --with-mysql-inc=<base-inc> --with-mysql-lib=<base-lib>'
ERROR: configuration failed for package 'RMySQL'
* removing '/root/R/x86_64-redhat-linux-gnu-library/3.1/RMySQL'

[Ram]

I forgot to update the command with the actual directories. Please try the refreshed command now.

[marcelelaux]

no way :(

[Ram]

Can you try installing without logging in as root? And you could probably use pastebin or GitHub Gist to share long output/error messages the next time!