[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]

Richard Herron <richard.c.herron using ...> writes:

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

The 1000 rows seemed fishy, so with some Googling I found a setting that limits 
workbench queries to n rows (this tipped me off http://bugs.mysql.com/bug.php?
id=49310). I haven't had this problem before and I'm not sure what I did to set 
n = 1000 since I clearly don't know enough MySQL to find that setting on my own 
:). This setting must pass through to the DBI interface, which significantly 
slowed my queries.