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')

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)