2

Using RSQLite in RStudio I run into the following error when running large queries

 Error in rsqlite_send_query(conn@ptr, statement) : 
   database or disk is full

as the underlying process works on /dev/xvda1. I've got plenty of space on /mnt/tmp, but can't get sqlite to work on /mnt/tmp.

How can I change the temp folder where sqlite creates etilqs files? points to a solution for Windows. I'm running RStudio on ubuntu.

I've copied the following in my .profile and /etc/environment (neither ~/.bash_profile nor ~/.bash_login are in my home dir.

 export TMPDIR=/mnt/tmp
 export TMP=/mnt/tmp
 export TEMP=/mnt/tmp
 export SQLITE_TMPDIR=/mnt/tmp

I've submitted the same env variable definitions at the command line, but still can't get sqlite to use /mnt/tmp.

Do I really have to recompile sqlite with sqlite3_temp_directory set to /mnt/tmp?

I have logged off between attempts...

Here's the content of /etc/environment. I've tried with and without export, with and without the quotes.

PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games"
TMPDIR="/mnt/tmp"
TMP="/mnt/tmp"
TEMP="/mnt/tmp"
SQLITE_TMPDIR="/mnt/tmp"
user2105469
  • 1,413
  • 3
  • 20
  • 37

2 Answers2

1

Use regular slashes rather than backslashes, e.g., export TMPDIR=/mnt/tmp. ("\mnt\tmp" get expanded to "mnttmp" which probably doesn't exist.)

varro
  • 2,382
  • 2
  • 16
  • 24
  • Good point, I did use the regular slashes, I'll correct the question. – user2105469 Jun 30 '17 at 20:12
  • In that case, my answer doesn't apply. How are you determining that `/mnt/tmp` isn't being used? And what does `pragma temp_store;` yield? – varro Jun 30 '17 at 22:28
  • Oh, to address your doubts about the syntax, you *do* need the `export` to propagate environment variables, but it doesn't matter whether or not you use quotes in this instance (though if you wanted to use a directory with space in its name [virtually unknown under Unix/Linux, but common under Windows], then you *would* have to quote: `export SQLITE_TMPDIR="/mtp/temporary directory"`). – varro Jun 30 '17 at 23:09
  • `pragma temp_store;` yields 0. `df -k` shows the root volume filling up as the query runs , while the /mnt/tmp (mounted volume) storage usage stays the same. Eventually I get the "database or disk is full" error. – user2105469 Jul 01 '17 at 01:32
  • I assume the database itself is on `/dev/xvda1`? Are you running a large transaction? What exactly is on `/dev/xvda1`? – varro Jul 01 '17 at 19:45
0

The trick is to define the temporary environment variables in Renviron.site located in $RHOME/etc

My RHOME path is /usr/lib/R, which can be determined by calling R.home().

This is what I added to Renviron.site:

 TMP=/mnt
 TMPDIR=/mnt
 TEMP=/mnt
 SQLITE_TMPDIR=/mnt

I'm still not 100% clear on how rstudio handles user environment variables, and how useful the Shell tool (within rstudio) is to establish env variable 'inheritance'. Anyway, using Renviron.site put my project back on the rails without having to agonise over recompiling sqlite with a new sqlite3_temp_folder global.

user2105469
  • 1,413
  • 3
  • 20
  • 37