dbSendQuery only returning 500 rows when using RMySQL in R

Question

I am using RMySQL to query a database.

I used

df <- fetch(dbSendQuery(con, "SELECT some_col from some_table"))

and got back this message:

Error in fetch(dbSendQuery(con, "SELECT some_col from some_table")) : 
  error in evaluating the argument 'res' in selecting a method for function 'fetch': Error in .local(conn, statement, ...) : 
  connection with pending rows, close resultSet before continuing

Strangely, df was actually created; 500 obs. of 1 variables, and it did give me back what I expected for these 500.

• The table some_table has 292,000 rows, which shouldn't be a problem at all for R.

• I tried with a variety of other tables in my schema (e.g., some_other_table) and got the same result. So it appears to be something with respect to the fetch function itself.

• I tried using dbGetQuery and dbFetch with no success.

What does this error mean, and how can I get all the rows in my table?

FOLLOWUP

I found out that I can slurp up a whole table by using

dbReadTable(con, 'some_table')

Beckmann

Answer 1

What you're seeing is normal behavior. The RMySQL implementations retrieves only n records, and if n is missing it only returns up to fetch.default.rec as specified in the call to MySQL (500 by default).

To return all of the rows use n = -1.

Also, it's useful to assign a name to your entire query string and then just pass that name to your fetch() statement.

drv <- dbDriver("MySQL")
con <- dbConnect(drv, user = "username", password="password1", 
                 host = "localhost", dbname="database")
res <- dbSendQuery(con, statement = paste(
                      "SELECT some_column1, some_column2",
                      "FROM some_table", 
                      "WHERE some_column2 >= x",
                      "ORDER BY some_column1"))
data1 <- fetch(res, n = -1)   

Geld

Thanks a lot for your advice. I'm having trouble finding good information about the RMySQL package. Seems like it would be immensely popular and widely used -- but even on SO, there are only ~175 questions with this tag. – Beckmann

Strangely, it even seems 'n = -1' is the default setting if you look at the help section or print the source code in the R console. – Aggressor

Answer 2

This link helped so much to use the RMySQL package in a useful way : Accessing MySQL through R

It includes also that you have to pass the parameter n=-1 to the fetch function to retrieve all data and not only the first 500 rows which is set by default in the fetch function.

I hope that helps you like It did for me.

Other useful links:

R interface to the MySQL database

Accessing MySQL through R

Maidservant