shiny + MonetDBLite

[davidbrae]

I am having issues using MonetDBLite databases within shiny apps. Specifically after connecting, disconnecting and reconnecting to a database I receive this error

Error in monetdb_embedded_startup(embedded, !getOption("monetdb.debug.embedded", :
Failed to initialize embedded MonetDB !FATAL: BBPinit: invalid format for BBP.dir
The following code reproduces the error after reconnecting and using the shiny App a few times (it varies)

library(DBI)
library(MonetDBLite)
library(shiny)
library(devtools)
# devtools::install_github("hannesmuehleisen/MonetDBLite")
# create a database stored locally

setwd("C:/Users/drae/Desktop/tempdatabase")
dbdir <- "C:/Users/drae/Desktop/tempdatabase"
con <- dbConnect(MonetDBLite::MonetDBLite(), dbdir)

dbWriteTable(con, "mtcars", mtcars)

server <- function(input, output, session) {

```
output$tab <- renderTable({
	dbGetQuery(con,'SELECT * from mtcars limit 5')
})
```

}

ui <- fluidPage(
	tableOutput('tab')
)
# run application, prints first five rows

runApp(list(ui=ui, server=server))
# exit out of the shiny application in the console
# connection to the database still works

dbListTables(con)
# close the connection

dbDisconnect(con, shutdown=TRUE)

q(save="no")
# ---------------------------------------------------------------------------------------------------------
# reconnect
# after repeating the following lines of code a few times, the connection fails to re-estatablish
# and the following error is produced. Repeated attempts to reconnect produce the same result
# Error in monetdb_embedded_startup(embedded, !getOption("monetdb.debug.embedded",  :
# Failed to initialize embedded MonetDB !FATAL: BBPinit: invalid format for BBP.dir
# ---------------------------------------------------------------------------------------------------------

library(DBI)
library(MonetDBLite)
library(shiny)
library(devtools)

setwd("C:/Users/drae/Desktop/tempdatabase")
dbdir <- "C:/Users/drae/Desktop/tempdatabase"
con <- dbConnect(MonetDBLite::MonetDBLite(),dbdir)

server <- function(input, output, session) {

```
output$tab <- renderTable({
	dbGetQuery(con,'SELECT * from mtcars limit 5')
})
```

}

ui <- fluidPage(
	tableOutput('tab')
)
# run application, prints first five rows

runApp(list(ui=ui, server=server))

dbDisconnect(con, shutdown=TRUE)
q(save="no")

sessionInfo()
R version 3.3.1 (2016-06-21)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

locale:
[1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252 LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C LC_TIME=English_United States.1252

attached base packages:
[1] stats graphics grDevices utils datasets methods base

other attached packages:
[1] devtools_1.12.0 shiny_0.13.2 MonetDBLite_0.3.2 DBI_0.4-1

loaded via a namespace (and not attached):
[1] R6_2.1.2 htmltools_0.3.5 withr_1.0.2 memoise_1.0.0 Rcpp_0.12.5 codetools_0.2-14 digest_0.6.9
[8] xtable_1.8-2 httpuv_1.3.3 mime_0.5

[ajdamico]

hi, here's a slightly modified example with a different error message

library(DBI)
library(shiny)

dbdir <- "C:/My Directory/ML"
con <- dbConnect(MonetDBLite::MonetDBLite(),embedded=dbdir)

dbWriteTable(con, "mtcars", mtcars)

server <- function(input, output, session) {

output$tab <- renderTable({
    dbGetQuery(con,'SELECT * from mtcars limit 5')
})

}

ui <- fluidPage(
tableOutput('tab')
)

runApp(list(ui=ui, server=server))
# hit escape

# NOTE: lines below work fine without the `shutdown=TRUE`
dbDisconnect(con, shutdown=TRUE)


con <- dbConnect(MonetDBLite::MonetDBLite(),embedded=dbdir)
# Error in monetdb_embedded_startup(embedded, !getOption("monetdb.debug.embedded",  : 
  # Failed to initialize embedded MonetDB !FATAL: logger_load: BBPrename to sql_freed failed

con
# <MonetDBEmbeddedConnection>
  # DISCONNECTED

con <- dbConnect(MonetDBLite::MonetDBLite())
# Error in monetdb_embedded_startup(embedded, !getOption("monetdb.debug.embedded",  : 
  # MonetDBLite already initialized

[hannes]

i'd be suspicious if shiny messes with the files in the tmp dir. try starting monetdblite in another directory. does the issue persist?

[ajdamico]

updated my comment above to try that. the issue persists

[lianos]

Isn't using MonetDBLite a non-starter in the shiny setting, anyway? I was quite keen to have given it a try, but the final disclaimer in the readme makes this a non-starter for anything but locally deployed applications, no?

MonetDBLite does not allow multiple R sessions to connect to a single database concurrently. As soon as a single R session loads an embedded server, that server is locked down and inaccessible to other R consoles

If we deploy this app to a shiny server, then once a second person uses the app concurrently, the game is up, right?

[davidbrae]

Perhaps, but for my applications there is no risk of multiple users using an app concurrently, it is pretty small scale stuff.

[hannes]

That is not true, there is only a single R process at a time that can access MonetDBLite. But serving an app to multiple users at the same time should be fine.

[lianos]

@hannesmuehleisen I'm having a hard time reconciling how this can be, but perhaps I'm not fully appreciating how shiny server (pro) handles multiple users hitting the same application -- I thought it was by having separate R processes for each user.

In any event, I have an application I'm working on where replacing my SQLite backend with a MonetDBLite backend would be killer, so I'll try that and report back.

[hannes]

don't know how shiny server pro does things... please do report back

[lianos]

According to this "Scaling and Perfomance" overview, it seems that it can decide to do one of two things ("Scenario A" or "Scenario B").

It would seem that if a shiny app utilizes a MonetDBLite data source, and chooses to go down "Scenario B", we're going to run into problems, right?

If so, I wonder if we enforce that the MonetDBLite data source is used for read-only operations, can you imagine a world where the 1 R process / DB restriction can be worked around? In this hypothetical world, how difficult do you imagine the software engineering effort would be to help make that happen?

[hannes]

Sorry, the read-only scenario is unrealistic given MonetDB's implementation. If you have a multi-user installation, you can use the standalone MonetDB server. For normal Shiny uses, MonetDBLite should work fine. @davidbrae, can you check if the BBPinit issue persists?

[ajdamico]

hi, the issue in my minimal reproducible example has not been solved -- using today's dev version. thanks

[ajdamico]

the issue in @davidbrae 's minimal reproducible example has also not been solved using today's dev version