Help with R and MySQL

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Help with R and MySQL

Aaron Barzilai
Hello,

This forum has been very helpful to me in the past, and I've run out of ideas on how to solve my problem.

I had been using R and MySQL (and Perl) together for quite some time successfully on my Windows XP machine. However, I recently had some problems with MySQL (the ibdata file had become 35GB on my hard drive, turns out it's a known bug with InnoDB), and ultimately the way I fixed my problem with MySQL was to upgrade it.  It's working fine now, I can use MySQL however I'd like.  I'm sticking to MyISAM tables for now, though.

However, I had set up my system so I did a linear regression in R. Originally, this was done in R 2.5.0, I would load in the tables from MySQL to R and then conduct the regression in R.  However, after solving my MySQL problem, I ran into a strange error in R (and DBI/RMySQL).  R connected to the database just fine, and I could even show the tables in the database and load two of them into R.  However, the tables I loaded successfully were only a single column.  Every time I tried to load in a recordset that was multiple columns, I got a relatively nondescript Windows error("R for Windows terminal front-end has encountered a problem and needs to close.  We are sorry for the inconvenience.").  To verify that it wasn't a memory issue,  I even tried "rs <- dbSendQuery(con, "select 'a', 'b'")".  This statement causes the error as well.

I tried upgrading the packages, and upgrading R from 2.5.0 to 2.8.1.  However, I still get the same errors.  Has anyone run into this problem before?  Any suggestions on how to solve it?

Thanks in advance,
Aaron


     
        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Help with R and MySQL

Jeffrey Horner
Aaron Barzilai wrote:
> Hello,
>
> This forum has been very helpful to me in the past, and I've run out of ideas on how to solve my problem.
>
> I had been using R and MySQL (and Perl) together for quite some time successfully on my Windows XP machine. However, I recently had some problems with MySQL (the ibdata file had become 35GB on my hard drive, turns out it's a known bug with InnoDB), and ultimately the way I fixed my problem with MySQL was to upgrade it.  It's working fine now, I can use MySQL however I'd like.  I'm sticking to MyISAM tables for now, though.
>
> However, I had set up my system so I did a linear regression in R. Originally, this was done in R 2.5.0, I would load in the tables from MySQL to R and then conduct the regression in R.  However, after solving my MySQL problem, I ran into a strange error in R (and DBI/RMySQL).  R connected to the database just fine, and I could even show the tables in the database and load two of them into R.  However, the tables I loaded successfully were only a single column.  Every time I tried to load in a recordset that was multiple columns, I got a relatively nondescript Windows error("R for Windows terminal front-end has encountered a problem and needs to close.  We are sorry for the inconvenience.").  To verify that it wasn't a memory issue,  I even tried "rs <- dbSendQuery(con, "select 'a', 'b'")".  This statement causes the error as well.
>
> I tried upgrading the packages, and upgrading R from 2.5.0 to 2.8.1.  However, I still get the same errors.  Has anyone run into this problem before?  Any suggestions on how to solve it?


Hi Aaron,

Be sure to read the details of the RMySQL web page:

http://biostat.mc.vanderbilt.edu/RMySQL

You need to make sure and match the version of your MySQL client library
(not the running MySQL server) with the RMySQL binary that you choose
from the web page above.

Best,

Jeff
--
http://biostat.mc.vanderbilt.edu/JeffreyHorner

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Help with R and MySQL

Aaron Barzilai
Thanks Jeff, that was exactly the problem.  When I unzipped the version at the page below for my version of MySQL (5.1), it worked fine.  The version I downloaded through install.packages() must have been for 5.0.

Thanks so much for the help and quick response,
Aaron





________________________________
From: Jeffrey Horner <[hidden email]>

Cc: [hidden email]
Sent: Monday, February 23, 2009 10:10:02 AM
Subject: Re: [R] Help with R and MySQL

Aaron Barzilai wrote:
> Hello,
>
> This forum has been very helpful to me in the past, and I've run out of ideas on how to solve my problem.
>
> I had been using R and MySQL (and Perl) together for quite some time successfully on my Windows XP machine. However, I recently had some problems with MySQL (the ibdata file had become 35GB on my hard drive, turns out it's a known bug with InnoDB), and ultimately the way I fixed my problem with MySQL was to upgrade it.  It's working fine now, I can use MySQL however I'd like.  I'm sticking to MyISAM tables for now, though.
>
> However, I had set up my system so I did a linear regression in R. Originally, this was done in R 2.5.0, I would load in the tables from MySQL to R and then conduct the regression in R.  However, after solving my MySQL problem, I ran into a strange error in R (and DBI/RMySQL).  R connected to the database just fine, and I could even show the tables in the database and load two of them into R.  However, the tables I loaded successfully were only a single column.  Every time I tried to load in a recordset that was multiple columns, I got a relatively nondescript Windows error("R for Windows terminal front-end has encountered a problem and needs to close.  We are sorry for the inconvenience.").  To verify that it wasn't a memory issue,  I even tried "rs <- dbSendQuery(con, "select 'a', 'b'")".  This statement causes the error as well.
>
> I tried upgrading the packages, and upgrading R from 2.5.0 to 2.8.1.  However, I still get the same errors.  Has anyone run into this problem before?  Any suggestions on how to solve it?


Hi Aaron,

Be sure to read the details of the RMySQL web page:

http://biostat.mc.vanderbilt.edu/RMySQL

You need to make sure and match the version of your MySQL client library (not the running MySQL server) with the RMySQL binary that you choose from the web page above.

Best,

Jeff
-- http://biostat.mc.vanderbilt.edu/JeffreyHorner



     
        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.