Inconsistent "unable to open database file" error from a CGI program using SQLite

Question

I've been running a task program for a psychology experiment on my virtual private server with lighttpd 1.4.28 and Ubuntu 12.04. The experiment is a CGI program written in Perl using a Perl module I wrote, presenting a web interface over Mechanical Turk. Data is stored in a SQLite database using WAL mode, with DBD::SQLite providing the underlying Perl interface.

The experiment seems to work fine for most workers completing it on Mechanical Turk. Occasionally, however, users get the error DBD::SQLite::db prepare failed: unable to open database file. It happens at seemingly arbitrary points midway through a task that involves clicking through some 100 HTML pages generated by the CGI program, each of which hits the database. It doesn't seem to go away when users refresh the page (I think). I have no idea how to replicate it on my own, but I know for a fact it hit at least 2 of 15 users. Looking at top while this was happening, CPU and memory didn't seem to have been stressed. Disk space isn't a problem, either (I'm only using 1.4 gigs of a 3-gig partition). The database is about 280 kb currently. PRAGMA integrity_check; returned ok.

Any ideas how to debug this? Google tells me that "unable to open database file" is usually a permissions error or something like that, but all that's set up properly; the task usually works.

Answer 1

This error can happen when SQLite fails to create a temporary file. (In that situation, "database file" in the error message is a bit misleading.)

For example, this happens on Windows if SQLite needs to write a temporary table/index to disk and none of the env vars TMP, TEMP, USERPROFILE is set. In that case SQLite attempts to create a temporary file in the Windows directory, which is typically not allowed. That can be fixed by passing TEMP to the script (in Apache config, PassEnv "TEMP"), or inside the script, pointing $ENV{TEMP} to a suitable directory.

Temp file creation might fail for other reasons. See functions "unixGetTempname" and "winGetTempname" in the SQLite sources for the exact rules SQLite uses for finding the temp directory on your platform.