Exception Message: Some kind of disk I/O error occurred

Question

I am getting this error when I try to run a update query on a SQLite database. This only happens on XP (on Vista works fine). The database is created without any issue, insert also works fine. I also checked and I have permissions and disk space available (as sqlite.org says these are possible causes).

Answer 1

One answer that has worked for me is to use the PRAGMA statement to set the journal_mode value to something other than "DELETE". You do this by issuing a PRAGMA statement such as PRAGMA journal_mode = OFF in the same way you would issue a query statement. I posted an example of this using c# at: http://www.stevemcarthur.co.uk/blog/post/some-kind-of-disk-io-error-occurred-sqlite/

Edit

Probably a better PRAGMA statement to issue is PRAGMA journal_mode = TRUNCATE rather than "OFF" as a couple of the others have suggested.

Answer 2

Per http://www.sqlite.org/pragma.html#pragma_journal_mode (emphasis mine):

The OFF journaling mode disables the rollback journal completely. No rollback journal is ever created and hence there is never a rollback journal to delete. The OFF journaling mode disables the atomic commit and rollback capabilities of SQLite. The ROLLBACK command no longer works; it behaves in an undefined way. Applications must avoid using the ROLLBACK command when the journal mode is OFF. If the application crashes in the middle of a transaction when the OFF journaling mode is set, then the database file will very likely go corrupt.

Sounds like you are on the right track, but one of the other options would be safer.

Answer 3

Do you have .db-journal file next to database file? If so make sure your application can delete files from folder where database is located.

Your symptoms indicate that SQLite can read and write but is unable to delete journal file.

Answer 4

Another possibility would be using the following SQL statement

"PRAGMA journal_mode = TRUNCATE"

It still keeps the journal, so if the power fails during a transaction you're still able to rollback and you'll avoid database corruption. The difference between DELETE and TRUNCATE is that delete creates and deletes constantly the journal file for each statement. Truncate only needs to create it once and just overwrites it. In my case it was a lot faster and I avoided the weird permissions that are coming with the standard journal_mode = DELETE.

Please refer to SQlite3 Pragma_journal_mode explenation