1

I have a sqlite db where I create tables and append new results on daily basis. This process was manual by going in RStudio and hit run!

I decided to schedule my scripts using Jenkins and for the last 5 hours I could not run it as I kept getting the following error:

Error in rsqlite_bind_rows(rs@ptr, value) : unable to open database file Calls: source ... tryCatch -> tryCatchList -> rsqlite_bind_rows -> .Call

Initially thought it was down to permissions so as a good unix citizen I chmod'ed my db to 777. Same error!

Then (unfortunately) went down the rabbit hole of updating packages:dplyr,dbplyr,DBI - read all the latest changes in each package, etc. Not fortune...

Until I saw a dbname.sqlite3-journal flashing in Rstudio Files Window. As it turned out this file is created by sqlite for temp tables and indices.

Obviously this file is gone once all transactions are done and disconnected from DB.

and obviously Jenkins does not have permission to modify this file.

a workaround is to give full write to the directory which is what I am doing at the moment and is working.

But I wonder if there is a way of sending this file to /var/tmp instead? Not keen on the 777 strategy.

something like:

con <- DBI::dbConnect(RSQLite::SQLite(), db, create = F, journal='/var/tmp')
Altons
  • 1,422
  • 3
  • 12
  • 23
  • What call gives you the error message? Can you at least establish a connection? If yes, you could try the [`temp_store_directory` pragma](https://sqlite.org/pragma.html#pragma_temp_store_directory). – krlmlr Jul 21 '17 at 23:04
  • Yes I can make the connection - issue arise when using db_insert_into – Altons Jul 21 '17 at 23:06

1 Answers1

1

The temp_store_directory pragma could help. Run this after establishing the connection:

dbExecute(con, "PRAGMA temp_store_directory = '/var/tmp'")

and check if it now works with your Jenkins installation.

krlmlr
  • 25,056
  • 14
  • 120
  • 217