[R] How do I solve a disk I/O error with RSQLite?

[chen jia]

Hi there,

I had an error when using RSQLite. My code and the error message are
the following:

> dbGetQuery(sql.dispersion,
+            "create table monthly_data_temp as
+             select a.*, b.industry
+             from   monthly_data as a left join
+                    siccd_industry_ff49 as b
+             on     a.siccd = b.siccd
+             order by permno, date")
Error in sqliteExecStatement(con, statement, bind.data) :
  RS-DBI driver: (RS_SQLite_exec: could not execute1: disk I/O error)
Calls: dbGetQuery ... dbGetQuery -> sqliteQuickSQL ->
sqliteExecStatement -> .Call
Execution halted

I ran the same code by using sqlite3 from the command line and had no error.

$ sqlite3 dispersion.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table monthly_data_temp as
   ...>             select a.*, b.industry
   ...>             from   monthly_data as a left join
   ...>                    siccd_industry_ff49 as b
   ...>             on     a.siccd = b.siccd
   ...>             order by permno, date;
sqlite>

I initialized the connection for SQLite by using

assign("sql.dispersion",
       dbConnect(SQLite(),
                 loadable.extensions=TRUE,
                 synchronous=0,
                 cache_size=200000,

dbname="/home/chenj/Fun/Research/Projects/Dispersion/Data/dispersion.db"),
       envir = .GlobalEnv)

and I also loaded an extension:
dbGetQuery(sql.dispersion,
           "select
load_extension('/home/chenj/Fun/SQLite/Extension/LibraryFile64Bit/libsqlitefunctions.so')")
cat("Connected to sql.dispersion",fill=TRUE)

The directory content for the database file is:
$ ls -l
total 34435604
-rw-r--r-- 1 chenj chenj         216 Jun 22  2012 C:\nppdf32Log\debuglog.txt
-rw-r--r-- 1 chenj chenj       26947 Mar 22  2011 Data_Dispersion.ods
drwxr-xr-x 2 chenj chenj        4096 Dec  2  2011 Data_from_Hou
drwxr-xr-x 2 chenj chenj      139264 Feb  2 23:30 dispersion
-rw-r--r-- 1 chenj chenj 33468631040 Feb 12 10:55 dispersion.db
-rw-r--r-- 1 chenj chenj   785633114 Nov 22  2009 monthly_data.csv
-rwxr-xr-x 1 chenj chenj  1007551488 Nov  2  2007 monthly_data.sas7bdat

If you could help me find out the cause of this error and solve it, it
would be great!

Please let me know if you need more information!

Best,
Jia