[R-sig-DB] Stalled MySQL query with RMySQL in R 2.13.0 on Win 7 (but works with small number of rows)

[Richard Herron]

I have a MySQL query that completes quickly in MySQL workbench (0.28 sec),
but stalls in R 2.13.0 using RMySQL. When the query stalls, I can break and
get the following

> db <- dbConnect(dbDriver("MySQL"), dbname = "finance", user = "root",
password = "root")
> temp <- dbGetQuery(db, query.compustat)
Warning messages:
1: In mysqlFetch(res, n, ...) :
  RS-DBI driver warning: (error while fetching rows)
2: In mysqlQuickSQL(conn, statement, ...) : pending rows

If I divide the query into dbSendQuery() and fetch(), I get similar results

> db <- dbConnect(dbDriver("MySQL"), dbname = "finance", user = "root",
password = "root")
> temp <- dbSendQuery(db, query.compustat)
> temp2 <- fetch(temp, n = -1)
Warning message:
In mysqlFetch(res, n, ...) :
  RS-DBI driver warning: (error while fetching rows)

But, if I limit myself to a much smaller query result, the query completes
just fine (my full query has about 1000 rows, which doesn't strike me as
particularly large). Here I limit myself to five rows

> db <- dbConnect(dbDriver("MySQL"), dbname = "finance", user = "root",
password = "root")
> temp <- dbGetQuery(db, paste(query.compustat, "LIMIT 5", collapse = " "))
> head(temp)
  gvkey fyear  sic      at  dlc ebitda prcc_f   csho     ceq   dltt dcvt
1  1004  1990 5080 379.958 16.5 41.957 14.125 15.891 193.778 68.953    0
2  1004  1990 5080 379.958 16.5 41.957 14.125 15.891 193.778 68.953    0
3  1004  1990 5080 379.958 16.5 41.957 14.125 15.891 193.778 68.953    0
4  1004  1990 5080 379.958 16.5 41.957 14.125 15.891 193.778 68.953    0
5  1004  1990 5080 379.958 16.5 41.957 14.125 15.891 193.778 68.953    0

Any ideas? I am on Win 7, so I could go back to RODBC, but I'd prefer either
RMySQL or RJDBC so that I can begin to start using the university's servers.
I get the same behavior with RJDBC, which makes me think that I'm done
something odd to my MySQL install. Thanks!

	[[alternative HTML version deleted]]