disk I/O error in SQLite

Question

What are the possible things that would trigger the "disk I/O error"? I've been having this problem and I couldn't find a solution. I have a SQLite3 database, and I'm trying to insert data from a file that contains SQL inserts.

Sample data in the file:

insert into files (filesize, filedate, md5, fullpath, origin) values (5795096,1370159412, "e846355215bbb9bf5f30102a49304ef1", "SDs/16G-1/DSC00144.JPG", "SDs"); 
insert into files (filesize, filedate, md5, fullpath, origin) values (5435597,1370159422, "1a7bcf3a4aaee3e8fdb304ab995ff80f", "SDs/16G-1/DSC00145.JPG", "SDs");
insert into files (filesize, filedate, md5, fullpath, origin) values (5121224,1370159432, "16d28e83599c731657a6cd7ff97a4903", "SDs/16G-1/DSC00146.JPG", "SDs");

I tried inserting that in the db file with the following command:

$ sqlite3 allfiles.db < insert.sql

See below the error that I get:

Error: near line 27: disk I/O error
Error: near line 28: disk I/O error
Error: near line 34: disk I/O error
Error: near line 39: disk I/O error
Error: near line 47: disk I/O error
Error: near line 129: disk I/O error

The input lines that don't generate error are successfully included, but I don't understand why some lines have errors, and they are not inserted into the DB. There's nothing special in the lines with error, and if I run the command again I get errors in different lines, which means it's random (not related to the data itself). I tried adding pragma syncrhonous = off; and pragma temp_store = memory;, to no success. I'm running that on a lubuntu, which runs in a VirtualBox virtual machine. The host machine is a windows 7. The pwd of the files is a shared folder, i.e., it's a folder in the host machine. If I run it in a "local folder" in the guest machine, the error doesn't happen, although for some reason it's much slower... In any case, I'd like to know about the I/O error.

Answer 1

That sounds like a problem with the shared drive arrangement, rather then one specific to sqlite. It would be worth stating which virtualisation method you are using as people may know if there are common problems with it.

The slower run from a resourse within the VM may be due to I/O overhead from the virt method, or extra IO contention because the input data was on a different physical drive to the VM. It may also be that the guest OS is memory starved, which will increase IO contention for large inputs as they can't be held in cache as easily on the host with it's larger memory. Again more detail would be needed for a surer answer.

Of course both problems could imply an intermittent IO error, so check you OS logs and the drives' SMART readings.

(this is likely to get more traction on serverfault, as it is likely not database specific)

Answer 2

I had this error and found that the cause was the partition holding the database file having no disk space free.