disk I/O error with SQLite

Question

I have a (tiny) dynamic website that is (roughly) a Perl CGI script using a SQLite database. Package DBI is the abstraction layer used in Perl.

About one week ago, I started to see this error message:

disk I/O error(10) at dbdimp.c line 271

Since this is a hosted site running Apache, I cannot see if the hard disk is (nearly) full. Access to command "df" is disabled.... but I used the (UNIX) shell command "yes > blah" to test the disk can still create new files. My database is very tiny -- less than 50 kilobytes.

I checked file and directory permissions: Directory and all parents are a+r,a+x (all + read/executable). The directory containing my SQLite database file is also a+w (all + write). The database file itself has a+w,a+r (all + read/write).

I wrote a simple Perl program to test I can run the failing select query: It runs fine.

I ran query "VACUUM" on the database. I tried my tests again -- no improvement.

I dumped the SQLite database to raw SQL (using SQLite shell command ".dump") and rebuilt. I tried my tests again -- no improvement.

Any suggestions? I am so confused... Normally, the above list can catch most programming/setup errors.

Answer 1

Another cause for this:

• Database file is writeable
• Database journal file (ending in -journal) is not writable

When the database file isn't writable, you get a "readonly database" error. When it's writable, but the journal file is not, you get "I/O error" instead.

Answer 2

Unfortunately, sqlite3.h isn't very descriptive about what the specific issue is. Error code 10 is defined here:

#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */

You may have an issue with /tmp being full at certain points or sqlite not having access to memory to write its page cache. This is unlikely though if your db is 50kb as sqlite should be able to hold your page cache in memory.

You could try making a copy of the db in the hopes that sqlite can read the copied database and update your code to reflect that:

$sqlite3 your.db
sqlite> begin immediate;
<press CTRL+Z>
$cp your.db copyofyour.db
$exit
sqlite> rollback;

You should also check the logs to see if this is happening with every request or intermittently. You may want to see if you have access to other commands to monitor server health (top, free). Being able to reproduce the issue seems to be your first task at hand. If you can't reproduce it with consistently, it's likely a memory related issue.