Cannot get results from sqlite database with opencpu

Question

I have a relatively simple function to extract some data from an 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 runs this code in R (or RStudio) this works perfectly fine 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 haven't succeed at all, to retrieve any data, using this function, through 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 have double checked that the file exists, check its rights, set read/write permissions for all, but no luck at all. Any input would be appreciated.

Edit 1: Tried the changes suggested by @Jeroen to the apparmor configuration to no avail. What did help a bit though was adding the following line to the custom file:

/full/path/to/db rw,

However, it only helped sofar that I now get the error:

RS-DBI driver: (error in statement: database is locked)

In call: sqliteExecStatement(conn, statement, ...)

Which is strange as I don't get this error when I execute exactly the same code (with the same database file) in RStudio.

Edit 2: As proposed 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
}

Still, to no avail as I get the exact same behavior (works in R studio, gives a database locked message when issuing the curl command).

Edit 3: Actually it still appears to be an AppArmor issue. 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. It seemed in the end to be a pure AppArmor problem. I had to change:

/full/path/to/db rw,

to

/full/path/to/db rwk,

(Note the k) in order to allow for file locking.

Jeroen Ooms · Answer 1 · 2014-10-27T22:50:16.297

1

It is probably a security restriction. See this post on the mailing list. Also see section 3.5 of the pdf manual. To debug, have a look at:

tail -f /var/log/kern.log

while you try to use your application. . I think adding

#include <abstractions/mysql>

To the /etc/apparmor.d/opencpu/custom profile should resolve the problem.

edited Oct 27 '14 at 22:50

answered Oct 24 '14 at 16:25

Jeroen Ooms

31,998
35
134
207

Some small remarks: the file you meant is probably: /etc/apparmor.d/opencpu/custom. The include line must state "#include " otherwise apparmor will complain that it cannot find the file. – Bertjan Broeksema Oct 27 '14 at 08:36
Thanks I updated it. Shouldn't answer SO questions on my phone :) Did this fix your problem? – Jeroen Ooms Oct 27 '14 at 22:50
No, but it pointed me in the right direction. As stated in the last edit, sqlite is a one-file database, so the mysql profile is not going to help much. What is required is read, write *and* lock access to the the database file. – Bertjan Broeksema Oct 28 '14 at 12:41
Ah sorry about that. So which line did you end up using? Maybe we can add it to the general profile. – Jeroen Ooms Oct 28 '14 at 18:59
See the solution in my question. Sqlite is a single file in-process database. So it is really package/application specific. As such, I don't think it can be added in general. Unless you 'force' sqlite db files to be in a specific folder of a package (eg data). Though i don't believe that to be a viable nor useful default. – Bertjan Broeksema Oct 28 '14 at 19:09

Cannot get results from sqlite database with opencpu

1 Answers1