This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Connecting R environment to Mysql databases on Unix system

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!

mysql unix dbconnect r rmysql
• 9,312 views • link
updated by Ram Ram • written by marcelelaux • marcelelaux

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

• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

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

Thanx

• 0 views • link
written by marcelelaux • marcelelaux

1 answer

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.

• 0 views • link
written by Ram Ram

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

• 0 views • link
updated by Ram Ram • written by marcelelaux • marcelelaux

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

• 0 views • link
written by Ram Ram

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

• 0 views • link
updated by Ram Ram • written by marcelelaux • marcelelaux

Please give me the output of:

$which mysql

from the UNIX command prompt

• 0 views • link
written by Ram Ram

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
• 0 views • link
updated by Ram Ram • written by marcelelaux • marcelelaux

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

which mysql
• 0 views • link
written by Ram Ram

Hello RamRS, I've sent you the output yesterday, I dont know if I understood the things about the comments, I tried to do what you asked me...

Thanx

• 0 views • link
written by marcelelaux • marcelelaux

Hi, I don't see the output anywhere. How did you send it to me? And no worries, I'll walk you through the usual discussion structure here.

• 0 views • link
written by Ram Ram

The output is /usr/bin/mysql. I'm clicking on "add reply"

• 0 views • link
updated by Ram Ram • written by marcelelaux • marcelelaux

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
• 0 views • link
written by Ram Ram

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'
• 0 views • link
updated by Ram Ram • written by marcelelaux • marcelelaux

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

• 0 views • link
written by Ram 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!

• 0 views • link
written by Ram Ram

Log in to answer this question.

More posts like this