Some kind of disk I/O error occurred–SQLite

I got the extremely helpful “Some kind of disk I/O error occurred” message using the System.Data.SQLite .net assembly today. This was a big SQLite show stopper so I thought it was about time I figured out what was causing this.  The error was occurring when I tried to post an update to the SQLite database in the local App_Data folder of an asp.net application. As soon as the application attempted to post the update I got the dreaded “Some kind of..” error and a SQLite journal file was created in the database folder. After this, the database became unavailable. It became available again once the journal file was deleted, but I was then back in the same situation once another update was attempted. The curious thing was that this behaviour didn’t occur in the development environment, only when uploaded to the live server. My first thought was that the application for some reason didn’t have rights to write to the App_Data folder. But the journal file was writing to the location with no troubles. Finally I hit upon a forum post on the System.Data.SQLite forum here. This pointed in the direction of using the “PRAGMA” statement to set the journal_mode value to something other than “DELETE”. The PRAGMA statement is executed exactly like a query, so all that needs to be done is to execute the statement using a SQLiteCommand object in the same way you would execute any other SQL statement.

   1:  using (SQLiteConnection conn = new SQLiteConnection(sqliteConnectionString))    

   2:          {     

   3:              conn.Open();     

   4:              SQLiteCommand cmd = new SQLiteCommand();     

   5:              cmd.Connection = conn;  

   6:              string pragma = "PRAGMA journal_mode = OFF";     

   7:              cmd.CommandText = pragma;     

   8:              cmd.ExecuteNonQuery();

   9:   

  10:   

  11:          }