[R] sqldf returns Error: database or disk is full

[Ian Worthington]

Indeed, that's where the dB is stored, but where is the result of the query stored pre aggregation, and does that have any bearing upon the running out of space error?

Sent from Yahoo Mail on Android 
 
  On Mon, 27 Jun 2022 at 19:45, Bert Gunter<bgunter.4567 using gmail.com> wrote:   You should probably heed the warning messages you received before doing anything further.
Bert
On Mon, Jun 27, 2022, 2:55 PM Ian Worthington via R-help <r-help using r-project.org> wrote:

I'm using sqldf() to execute a query using a named dbname.  It appears to create an external db but after 30 minutes or so returns:

Error: database or disk is full
The disk is not full, so have I hit some internal db size limit?  The external db created is about 610MB and doesn't grow, so maybe it's running out  of room for the result set, where ever that is stored?

Code being executed is:
  sqlCmds <- c(#"create index ixsip on ipTrace100(source)",
               "create index ixdip on ipTrace100(destination)",
               #"create index ixspo on ipTrace100(SrcPo)",
               "create index ixdpo on ipTrace100(DstPo)",
               #"create index ixno  on ipTrace100(`No.`)",
               # "explain query plan 
               "select tr1.`No.` InNo,      tr1.Time InTime, tr1.source InSource, /*tr1.destination InDest,*/ tr1.SrcPo InSrcPo, tr1.DstPo InDstPo,
                       min(tr2.`No.`) OutNo, tr2.Time OutTime /*, tr2.source OutSource,*/ /*tr2.destination OutDest,*/ /*tr2.SrcPo OutSrcPo*/ /*, tr2.DstPo OutDstPo */
               from main.ipTrace100 tr1
               join main.ipTrace100 tr2
               on tr1.destination = '10.27.187.1' and
                  tr1.source = tr2.destination and
                  tr1.SrcPo = tr2.DstPo and
                  tr1.`No.` < tr2.`No.`
               group by tr1.`No.`"
               )
  (tempfile <- tempfile())
  system.time( sqldf(sqlCmds, dbname=tempfile, verbose=TRUE) )
and console log:

| 
|  |

>   system.time( sqldf(sqlCmds, dbname=tempfile, verbose=TRUE) )
sqldf: library(RSQLite)
sqldf: m <- dbDriver("SQLite")
sqldf: connection <- dbConnect(m, dbname = "C:\Users\PIWN01~1\AppData\Local\Temp\RtmpSycE1E\file78e464e72860")
sqldf: initExtension(connection)
sqldf: dbWriteTable(connection, 'ipTrace100', ipTrace100, row.names = FALSE)
sqldf: dbGetQuery(connection, 'create index ixdip on ipTrace100(destination)')
Warning in result_fetch(res using ptr, n = n) :
  SQL statements must be issued with dbExecute() or dbSendStatement() instead of dbGetQuery() or dbSendQuery().
sqldf: dbGetQuery(connection, 'create index ixdpo on ipTrace100(DstPo)')
Warning in result_fetch(res using ptr, n = n) :
  SQL statements must be issued with dbExecute() or dbSendStatement() instead of dbGetQuery() or dbSendQuery().
sqldf: dbGetQuery(connection, 'select tr1.`No.` InNo,      tr1.Time InTime, tr1.source InSource, /*tr1.destination InDest,*/ tr1.SrcPo InSrcPo, tr1.DstPo InDstPo,
                       min(tr2.`No.`) OutNo, tr2.Time OutTime /*, tr2.source OutSource,*/ /*tr2.destination OutDest,*/ /*tr2.SrcPo OutSrcPo*/ /*, tr2.DstPo OutDstPo */
               from main.ipTrace100 tr1
               join main.ipTrace100 tr2
               on tr1.destination = '10.27.187.1' and
                  tr1.source = tr2.destination and
                  tr1.SrcPo = tr2.DstPo and
                  tr1.`No.` < tr2.`No.`
               group by tr1.`No.`')
Error: database or disk is full
sqldf: dbDisconnect(connection)
sqldf: file.remove(dbname)
Timing stopped at: 413.1 1081 1780 |


|  |




Best wishes / Mejores deseos /  Meilleurs vœux

Ian ...
        [[alternative HTML version deleted]]

______________________________________________
R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

  

	[[alternative HTML version deleted]]