SQLite.NET - I/O Errors in when the app is in the background.

rrodriguezrrodriguez USMember
edited February 2014 in Xamarin.iOS

Hello there,

The app I'm working on uses background location.

I found that every time the app is backgrounded any write to the db returns an I/O Error. The app needs to update some data as a result of a location change; that's how I found this error.
The SQLite exception itself is not very descriptive.

While googling about this issue I found this post in SO where the author claims that by disabling Write-Ahead Logging (WAL) the app works as expected.

I went ahead and set the PRAGMA journal_mode = OFF as suggested on his post, however this didn't have any effect on my app.
If, instead, I set PRAGMA journal_mode = WAL the application works as expected and no "I/O Error" is thrown regardless if the app is in the foreground or in the background.

According to the General Core Data Enhancements

The Core Data SQLite persistent store will default to using WAL journal_mode on all applications linked on or after 10.9.

However if I query for the journal_mode as soon as the db is created

var cmd = connection.CreateCommand ("PRAGMA journal_mode");
Console.WriteLine ("PRAGMA: {0}", cmd.ExecuteScalar<string>());

... it is set to DELETE instead of WAL

Questions:

  1. Why the default journal_mode is DELETE instead of WAL, as found in the Apple Doc?
  2. Why when journal_mode = DELETE, I can't write to the db, and if I query a value, it returns null?
  3. Since setting the journal_mode = WAL seems to do the trick, what are the possible side effects to the rest of the app?

Thanks,
Raciel

PS: BTW, I'm only able to replicate this issue in an IPhone 5 running iOS 7.0.x. My other device is an old IPod running 6.1.x and I'm not able to replicate the problem regardless the journal_mode I set. I could confirm that the journal_mode for this Ipod with iOS 6.1.x is DELETE too.

Posts

  • FrankAKruegerFrankAKrueger ✭✭ USInsider, University, Developer Group Leader ✭✭
    1. The reason they are different is that you read the docs for "Core Data". sqlite is not Core Data, it's just sqlite. I did not choose the default, it is whatever Apple compiled it to be.

    2. I have no idea, sorry I'm new to journal modes.

    3. Sorry, again I don't know.

    If Core Data takes WAL as the default, I wonder if SQLite-net should also. I have not tried background writing so this is new to me.

  • UrsinBrunnerUrsinBrunner ✭✭ CHMember ✭✭

    @rrodriguez‌ Do you get this error as soon as you are in background or as I wrote in my SO thread (http://stackoverflow.com/questions/21625501/sqlite-i-o-error-on-ios-7-0-with-monotouch), only after a certain time of background-activity?

    I just read a little deeper into the SQLite-WAL algorithm (http://www.sqlite.org/draft/wal.html) and from my point of view, it could be possible that WAL in combination with the iOS 7 filesystem causes the problem. They talk about two extra files beside the database ("-wal" and "-shm"). And after a certain amount of transactions, this log-files are transfered back into the database (it's called "checkpointing"). I don't know what exactly happens on the filesystem, but this mechanism would explain why the error appears only after a certain amount of time.

  • rrodriguezrrodriguez USMember

    @FrankAKrueger‌ thanks for your input. You are definitely right about me reading in the wrong place (CoreData); I just thought that was going to be Apple's default.

    @UrsinBrunner‌ yes, the issue happens after the 1st or 2nd write after the app is backgrounded. I also noticed that performing a query at that time would not return valid results (I was getting null).

    What is odd is that what is actually working for us is exactly the opposite. In your case you are turning WAL off (by setting journal_mode = OFF ?) and in my case is actually when I turn it ON (by setting journal_mode = WAL) when I see the issue disappear.

    I would continue reading SQLite docs more in detail, but I need to test now that the new journal_mode is not affecting anything else in the app.

    Not sure where to take this issue to, or who else to get involve for more input.

    Thanks!

Sign In or Register to comment.