title: How can I change the temp folder where sqlite creates etilqs files on Ubuntu Linux R?
body: <p>I'm running sqldf in R on Ubuntu to select certain IDs from a big table with gigabytes of data and the process is creating temporary etilqs files under /var/tmp according to inotifywait monitoring file changes. However, my /var/tmp is on a small disk and this occasionally causes R to error out. I found a <a href="https://stackoverflow.com/questions/16122824/how-can-i-change-the-temp-folder-where-sqlite-creates-etilqs-files">thread</a> on how to change the temp folder location for sqlite on Windows, but I could not figure out how to make it work under Linux.</p>
  
  <pre><code>library(sqldf)
  customer_extr &lt;- sqldf("select b.*, a.year, a.name from product as b left join customer as a on a.ID = b.ID", dbname = "/home/userName/customer.db")
  </code></pre>
  
  <p>It seems to me that sqlite searches directories for temporary file storage locations (NOT the tempfile() that I can choose where to create the file by selecting tmpdir=) in the following order:</p>
  
  <ol>
  <li>The directory set by PRAGMA temp_store_directory or by the sqlite3_temp_directory global variable</li>
  <li>The SQLITE_TMPDIR environment variable</li>
  <li>The TMPDIR environment variable</li>
  <li>/var/tmp</li>
  <li>/usr/tmp</li>
  <li>/tmp</li>
  <li>The current working directory (".")</li>
  </ol>
  
  <p>I tried a few options but none of them seemed to work:</p>
  
  <ol>
  <li><p>set temp_store_directory:</p>
  
  <pre><code>con &lt;- dbConnect(dbDriver("SQLite"), dbname = "/home/userName/customer.db")
  dbGetQuery(con, "PRAGMA temp_store_directory = '/mnt/tmp'")
  </code></pre>
  
  <p>But this errors out: </p>
  
  <pre><code>Error in rsqlite_send_query(conn@ptr, statement) : basic_string::resize
  </code></pre>
  
  <p>Currently, temp_store_directory is not set after checking</p>
  
  <pre><code>Sys.getenv('temp_store_directory')
  </code></pre></li>
  <li><p>Before running R, I set the environmental variables to the desired temp folder: /mnt/tmp:</p>
  
  <pre><code>export SQLITE_TMPDIR=/mnt/tmp
  export TMPDIR=/mnt/tmp
  </code></pre>
  
  <p>I verified this has been successfully set up by </p>
  
  <pre><code>echo $SQLITE_TMPDIR 
  echo $TMPDIR 
  </code></pre>
  
  <p>under Linux, </p>
  
  <pre><code>Sys.getenv('SQLITE_TMPDIR') 
  Sys.getenv('TMPDIR') 
  </code></pre>
  
  <p>in R.</p>
  
  <p>However, my sqldf step still writes etilqs files to /var/tmp.</p></li>
  <li><p>I tried to run </p>
  
  <pre><code>dbGetQuery(con, "PRAGMA temp_store = 2") 
  </code></pre>
  
  <p>to instruct sqlite to save temporary files in memory. However, it's still writing etilqs files to /var/tmp.</p></li>
  <li><p>I thought about creating a symbolic link for /var/tmp to point to /mnt/tmp but to do that I think I have to delete the folder /mnt/tmp first. This is not ideal since it's a shared Linux server and the disk for /mnt/tmp sometimes gets unmounted. I am not sure if this will cause any trouble for other applications and users.</p></li>
  </ol>
  
  <p>I don't know how to check/change the sqlite3_temp_directory global variable in R.</p>
  
  <p>This is my session info:</p>
  
  <pre><code>&gt; sessionInfo()
  R version 3.3.2 (2016-10-31)
  Platform: x86_64-pc-linux-gnu (64-bit)
  Running under: Ubuntu 14.04.5 LTS
  
  locale:
   [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C
   [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8
   [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8
   [7] LC_PAPER=en_US.UTF-8       LC_NAME=C
   [9] LC_ADDRESS=C               LC_TELEPHONE=C
   [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C
  
  attached base packages:
  [1] stats     graphics  grDevices utils     datasets  methods   base
  
  other attached packages:
  [1] sqldf_0.4-10 RSQLite_1.1  gsubfn_0.6-6 proto_1.0.0
  
  loaded via a namespace (and not attached):
  [1] DBI_0.5-1     memoise_1.0.0 Rcpp_0.12.8   digest_0.6.10 chron_2.3-47
  </code></pre>
  
  <p>I can try upgrading my OS disk to a larger drive but isn't there a way to tell sqlite in R under Linux to write temporary files somewhere else? Any suggestions would be highly appreciated!</p>