Can't get results from sqlite database with opencpu
I have a relatively simple function to fetch some data from a sqlite database:
library(RSQLite)
db.select <- function(table="mydata", vars, rows=c()) {
vars <- paste(unlist(vars), collapse=", ")
q <- paste("SELECT ", vars, " FROM ", table, sep="")
if (length(rows) > 0) {
rows <- paste(as.character(rows), collapse=", ")
q <- paste(q, " WHERE row in (", rows, ")", sep="")
}
con <- DBI::dbConnect(RSQLite::SQLite(), "/abs/path/to.db")
res <- DBI::dbSendQuery(con, q)
data <- DBI::dbFetch(res)
DBI::dbClearResult(res)
DBI::dbDisconnect(con)
data
}
When I run this code in R (or RStudio), this works great for me:
> db.select(vars = c("gc_content"), rows=c(1:5))
gc_content
1 44.30
2 41.22
3 48.51
4 60.83
5 45.21
However, I was unable to get data at all using this function via opencpu:
$ curl http://localhost/ocpu/user/bertjan/library/RParcoords/R/db.select/json -H "Content-Type: application/json" -d '{"vars":["gc_content"]}'
RS-DBI driver: (could not connect to dbname:
unable to open database file
)
In call:
sqliteNewConnection(drv, ...)
I double checked that the file exists, checks its permissions, sets read and write permissions for everyone, but no luck at all. Any input would be appreciated.
Edit 1: Tried the changes suggested by @Jeroen in the apparmor setup to no avail. Which helped a bit by adding the following line to the custom file:
/full/path/to/db rw,
However, it only helped me that I got the error:
RS-DBI driver: (error in instruction: database is locked)
In the call: sqliteExecStatement (conn, statement, ...)
Which is strange, since I don't get this error when I execute exactly the same code (with the same database file) in RStudio.
Edit 2: As suggested by Hadley Wickham ( https://twitter.com/hadleywickham/status/526739851974955008 ), I simplified the function using dbGetQuery:
db.select <- function(table="mydata", vars, rows=c()) {
vars <- paste(unlist(vars), collapse=", ")
q <- paste("SELECT ", vars, " FROM ", table, sep="")
if (length(rows) > 0) {
rows <- paste(as.character(rows), collapse=", ")
q <- paste(q, " WHERE row in (", rows, ")", sep="")
}
con <- DBI::dbConnect(RSQLite::SQLite(), "/home/bertjan/cstr.db")
data <- DBI::dbGetQuery(con, q)
DBI::dbDisconnect(con)
data
}
However, to no avail since I get the same behavior (works in R studio, gives a locked database message when issuing the curl command).
Edit 3: Actually still seems to be an AppArmor problem. I get:
Oct 27 15:50:52 Stef kernel: [899068.612784] type=1400 audit(1414421452.965:232): apparmor="DENIED" operation="file_lock" profile="opencpu-exec" name="/path/to/db" pid=9708 comm="apache2" requested_mask="k" denied_mask="k" fsuid=33 ouid=1000
Solution: Finally found it. In the end it seemed like it was a pure AppArmor problem. I had to change:
/full/path/to/db rw,
to
/full/path/to/db rwk,
(Note k) to enable file locking.
source to share
This is probably a security limitation. See this post on the mailing list . Also see Section 3.5 PDF manual . To debug take a look:
tail -f /var/log/kern.log
while you are trying to use your application. i think by adding
#include <abstractions/mysql>
In the profile /etc/apparmor.d/opencpu/custom
you need to solve the problem.
source to share