Why can't DBD::SQLite insert into a database through my Perl CGI script?

Question

I am running a SQLite database within a Perl CGI script which is being accessed by DBD::SQLite. This is being run as a straight CGI on Apache.

The DBI connection works fine and selects are able to be run. However, when I attempt to do an insert I get a die with the following error:

DBD::SQLite::st execute failed: unable to open database file(1) at dbdimp.c line 402 at index.cgi line 66

I have tried changing the database file permission to 666 to try to fix this however I am still receiving the error.

Any advice?

Telegu

Can you set the directory and file permission temporarily to 777 and recheck it? – Cryptograph

Ah ha! Changing the directory permissions to 777 fixed this. Do you know why this is? – Telegu

You probably forgot to set the right directory permission too. – Cryptograph

Answer 1

It looks like the directory needs write permission, the reason is:

SQLite needs to be able to create a journal file in the same directory as the DB, before any modifications can take place. The journal is used to support transaction rollback.

From: seem to need write permission on db's parent directory

Telegu

Answer 2

SQLite momentarily locks the entire file when it is doing inserts and updates (there is no record-level locking as such). Are you sure you're freeing the locks?

The SQLite literature recommends that you start a transaction, collect all of your inserts and updates du jour in that transaction, and then commit. This avoids numerous successive file locks, and improves performance.

Buffon

Answer 3

Since SQLite locks the entire database file, you may want to use a timeout-based retry mechanism. I was working on pretty much the same problem when I asked this related question.

I ended up writing something similar to Mark Fowler's Attempt that retries if the exception thrown by the sub matches a regular expression, in my case:

qr(already in a transaction|database is locked)i

Lafontaine

Answer 4

The path to the directory where the db file resides should have both executable and writable bits set, in order to access it from the script.

Furthermore, if you don't want the db file to be directly accessed (even without the use of special server files), it should have access permissions such as 600 and if the containing directory should not be directly browsed (again, even without the use of special server files) it should have access permissions such as 700.

I use this setup and it works fine both locally and on the server where I host my site.

Of course, the permission of the containing directory cannot be 700 if there exists any other file inside it that should be accessible via html, css or javascript. It should be 755 instead.

Venenose

Answer 5

If you don't want to set write permissions on the whole directory as explained in Todd Hunter's answer, you could instead set the journal_mode PRAGMA to "MEMORY".

In that case, beware that:

The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt.

So whether this is a good solution depends on your database and how it is used.

Others also mention the temp_store pragma. I didn't need to set that, but it may depend on how the database is used.

So in your Perl CGI script, you could try this:

$dbh->do("PRAGMA journal_mode = MEMORY");
$dbh->do("PRAGMA temp_store   = MEMORY");

Wive