The simple interpretation is that you have run out of space and it takes sqldf
30 minutes to work through the error process and then return the error. A
reason for sql is the more compact representation of a sparse matrix. Changing
it into a data frame may result in a data structure with many empty cells that
consumes a great deal more memory.
-----Original Message-----
From: R-help <r-help-boun...@r-project.org> On Behalf Of Ian Worthington via
R-help
Sent: Monday, June 27, 2022 5:06 PM
Cc: R-help <r-help@r-project.org>
Subject: Re: [R] sqldf returns Error: database or disk is full
[External Email]
Well, iiuc the df is copied to the db early on in the process. This is constant
with the temporarily file growing rapidly in size at the start of the process
and then not challenging for 30 minutes until the error message is produced.
I have no idea what sqldf is doing under the hood.
Sent from Yahoo Mail on Android
On Mon, 27 Jun 2022 at 23:00, Ebert,Timothy Aaron<teb...@ufl.edu> wrote: I
would try answering these questions:
1) What is the expected size?
2) Is the current size smaller or larger?
3) Every program running on your computer takes up memory. Given all the other
programs currently running, do you have space?
Maybe someone can help by suggesting a way to get R to confess how much memory
it has available given current system resources.
Tim
-----Original Message-----
From: R-help <r-help-boun...@r-project.org> On Behalf Of Bert Gunter
Sent: Monday, June 27, 2022 1:44 PM
To: Ian Worthington <ianworthing...@yahoo.com>
Cc: R-help <r-help@r-project.org>
Subject: Re: [R] sqldf returns Error: database or disk is full
[External Email]
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@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@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@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@r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://urldefense.proofpoint.com/v2/url?u=https-3A__stat.ethz.ch_mail
> man_listinfo_r-2Dhelp&d=DwIFaQ&c=sJ6xIWYx-zLMB3EPkvcnVg&r=9PEhQh2kVeAs
> Rzsn7AkP-g&m=D7saEcIOaLR9v9eTeELZP8i6BNDnyIxFvdH9QZEgmCSLEBPYRmriAbv8I
> ar9qeI1&s=pDM85Abhn-vCKrLWt4rilCYft2YZGrY4JDkkH-HE8CA&e=
> PLEASE do read the posting guide
> https://urldefense.proofpoint.com/v2/url?u=http-3A__www.R-2Dproject.or
> g_posting-2Dguide.html&d=DwIFaQ&c=sJ6xIWYx-zLMB3EPkvcnVg&r=9PEhQh2kVeA
> sRzsn7AkP-g&m=D7saEcIOaLR9v9eTeELZP8i6BNDnyIxFvdH9QZEgmCSLEBPYRmriAbv8
> Iar9qeI1&s=b_bVPMySqKEIwMTpmGyQu5j1M3GR8cOwP0KsRw6wjPs&e=
> and provide commented, minimal, self-contained, reproducible code.
>
[[alternative HTML version deleted]]
______________________________________________
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://urldefense.proofpoint.com/v2/url?u=https-3A__stat.ethz.ch_mailman_listinfo_r-2Dhelp&d=DwIFaQ&c=sJ6xIWYx-zLMB3EPkvcnVg&r=9PEhQh2kVeAsRzsn7AkP-g&m=D7saEcIOaLR9v9eTeELZP8i6BNDnyIxFvdH9QZEgmCSLEBPYRmriAbv8Iar9qeI1&s=pDM85Abhn-vCKrLWt4rilCYft2YZGrY4JDkkH-HE8CA&e=
PLEASE do read the posting guide
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.R-2Dproject.org_posting-2Dguide.html&d=DwIFaQ&c=sJ6xIWYx-zLMB3EPkvcnVg&r=9PEhQh2kVeAsRzsn7AkP-g&m=D7saEcIOaLR9v9eTeELZP8i6BNDnyIxFvdH9QZEgmCSLEBPYRmriAbv8Iar9qeI1&s=b_bVPMySqKEIwMTpmGyQu5j1M3GR8cOwP0KsRw6wjPs&e=
and provide commented, minimal, self-contained, reproducible code.
[[alternative HTML version deleted]]
______________________________________________
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://urldefense.proofpoint.com/v2/url?u=https-3A__stat.ethz.ch_mailman_listinfo_r-2Dhelp&d=DwIFaQ&c=sJ6xIWYx-zLMB3EPkvcnVg&r=9PEhQh2kVeAsRzsn7AkP-g&m=0fZNMT-DeF0LZ9aHAla6puo_a73X7hSRErtTnnWMGQKV1cr9q2APnbBGnebI1HtO&s=ztcWjz02KcTdzsGR2-UnitA6yO2n41dS1EaKsMx6fL8&e=
PLEASE do read the posting guide
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.R-2Dproject.org_posting-2Dguide.html&d=DwIFaQ&c=sJ6xIWYx-zLMB3EPkvcnVg&r=9PEhQh2kVeAsRzsn7AkP-g&m=0fZNMT-DeF0LZ9aHAla6puo_a73X7hSRErtTnnWMGQKV1cr9q2APnbBGnebI1HtO&s=Y2P4_7OR1SpkP4Zus_eHnAXXWUpJT-QC5eNr5izpa94&e=
and provide commented, minimal, self-contained, reproducible code.
______________________________________________
R-help@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.
