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

[Bert Gunter]

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