RMySQL, fetch errors - RS-DBI driver warning: (error while extracting rows)

Question

I use RMySQL to extract some rows from a data table (the table is large, so I can not publish it here, but basically it contains a bunch of numbers .. only 10 columns and about 12,000 rows). When running fetch(con, n=-1) , the following error appears: RS-DBI driver warning: (error while fetching rows) , and the server returns only 1713 rows.

If I get rid of some of the selected columns, then this seems to work fine. Does anyone know what this may be related to? I don’t even know where to start debugging. Could this be server side? My R session has more than enough memory (20 concerts).

Answer 1

Is each column a number or a list of numbers? That is, how many bytes in each column?

I used to run into this problem, and when I hit it, it was because I tried to pull out too much data too quickly. I found that in these cases, sometimes several calls can be made with smaller n values. On the other hand, the rows in the databases I came across were huge

Answer 2

Better soul instead of n = -1 try to put a very large number, for example n = 1000000. The error did not come, I used this. In my case, the number of rows that I selected was 1.13 million.

Answer 3

I have the same problems:

• Select all rows once:

  df = dbFetch(res, n = -1)

  => it returns only part of the result set and stops retrieving more rows.

• Using a loop to extract fragments:

    while (! dbHasCompleted (res)) {
       chunk = dbFetch (res, n = 1000)
       print (nrow (chunk))
       df = rbind (df, chunk)
   }
  

  => it returned some pieces for a while, and then runs into an endless loop of zero size (print " [1] 0 " forever), even if the result set did not complete the selection of all rows: dbHasCompleted(res) == FALSE .

Then I used this strategy:

Run the query with " select count(1) from table where ... " to find out the size of the result set. Added 1 to the number of row counters [ row_count = as.integer(dbFetch(res, n = 1)) + 1 ] and use this parameter "count + 1" as parameter n to immediately get all the rows in the next query. Everything seems to be in order so far ... but then I knew about this form:

my_df = dbGetQuery(con, my_query)

Much better method, no errors yet.