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.

Fever

Can you post the code that executes when the error happens? – Difficile

Which version of SQLite are you running? And how big is your database? Is this useful - sqlite.org/cvstrac/tktview?tn=3094,3 – Mikimikihisa

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.

Goodall

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.

Shechem

Nice suggestion! Cool ideas. I will take a note for future issues. Unfortunately, in this case -- it was due to a FreeBSD kernel bug (as explained by support forums for NearlyFreeSpeech.net). – Fever

Could you say a bit more about the bug? Think I might be bumping up against something similar... – Marketable

@ChrisVandevelde: Sorry, NFS.net admins did not provide further details about the FreeBSD <-> SQLite bug. However, my problem has been resolved. – Fever

Answer 3

A possible, and maybe hard to detect, error source may be if file locking fails. You could test if your file system currently supports file locking with

flock testfile touch testfile

NFS file systems for example may exhibit this behavior depending on NFS server configuration.

Jeromyjerreed