Hi! We're using SQLiter and we're facing thousands...

# touchlab-tools

[Lucas Marçal]

Hi!
We're using SQLiter and we're facing thousands of crashes while trying to access the database.
Adding to this thread more details about the crash

[Lucas Marçal]

Stack Trace

sqliter-crash-backtrace

[Lucas Marçal]

FYI @kpgalligan

[kpgalligan]

Any more info? It's failing while trying to open the file. Presumably the file is corrupt somehow. If it didn't exist, I would expect it to be created.
By "thousands of crashes" is that crashed on thousands of devices? Was there a recent release update?

[kpgalligan]

Looks like it's failing from sqlite3_open_v2. An exception should be thrown from here: https://github.com/touchlab/SQLiter/blob/d4ed6c284259186b8e460faae193aa180bc1267f/[…]CommonMain/kotlin/co/touchlab/sqliter/interop/SqliteDatabase.kt. There should be a string message with an actual error message hopefully saying what's wrong.

[kpgalligan]

Updates?

[Etienne Martin]

Hey Kevin, I work with Lucas, I will be looking into this issue as well. I'll report back soon.

[Etienne Martin]

We seem to be using SQLiter to power the driver for iOS in our KMP based app. I'm curious how we can surface this error message if we are using SQLDelight. I'm not sure when this dbOpen is being called and how we could possibly capture this errors. I'm wondering if there are known causes for this error to happen?

[kpgalligan]

The cause should be in the message of the exception. You have the stacktrace, but not the "message" string. How are you getting the stack trace? If you can add the exception message to your info, that will (usually) explain what's happening. The line numbers in the stack trace don't line up exactly with the code, but that can happen with inlines, etc (also, what versions are you running of sqldelight/sqliter/kotlin?). Here's where it's throwing an exception:
https://github.com/touchlab/SQLiter/blob/d4ed6c284259186b8e460faae193aa180bc1267f/[…]CommonMain/kotlin/co/touchlab/sqliter/interop/SqliteDatabase.kt

sqlite can produce error messages about what went wrong with sqlite3_errmsg(dbPtr.value)?.toKString(). Without that string, we're guessing. However, at that point in the code, there's not an infinite list of possible problems.

This is happening when sqlite is trying open the db. That's before any issues with migration, versioning, etc. Way before SqlDelight, etc. It should create the db when it doesn't exist. So, without more detail, one of two options seem likely:
• The path where the db is stored isn't "standard" and isn't accessible
• The db file is "corrupt"
The "bad path" option would mean you're explicitly telling sqldelight, by way of sqliter config, that the db file is in a non-default folder. I'm guessing that's not what you're doing (I don't know anybody doing this. I've never done this).
The "corrupt db" option points to a handful of possible causes. SQLiter doesn't really do anything with the db directly outside of sqlite calls, and sqlite is one of the most stable pieces of software available. It's very difficult to programmatically corrupt your db without directly accessing the file and doing something to it outside of sqlite. You can have issues if two different processes are accessing the db at the same time, depending on how they're doing it, but that's going to cause lost data, not a corrupt db. There are other possible causes of a corrupt db file caused by what the program is doing. You can read about that here.
So, in general use, sqliter isn't going anywhere near something that might cause a corrupt db (although nothing is impossible in these situations).
There can be hardware issues, but you wouldn't see "thousands" of those. I don't have stats, but I'd guess you'd need to be Facebook/Google, etc level of users before you see hardware issues bad enough to impact the local db file but not break much else.
That leaves two obvious options:
• Something other than sqliter is touching the db
• You're using an encrypted db
In the former, this is usually when an app "seeds" a db from a resource, local or remote. It copies a full db file to the device's disk, then opens it. This is almost always an incomplete copy due to logic errors, interrupted downloads, or a race condition (db is being copied, app tries to access it early). If the app is somehow seeding the db, as with most file copy procs, create a temp file, then rename it. Renaming is atomic-ish and hard to mess up. Saving the file to it's "final" name can run into several issues.
If you're using an encrypted db (SQLCipher, etc), then it's probably that. The Xcode config for SQLCipher is delicate. It's very easy to change config and suddenly your app is using non-encrypted sqlite instead of SQLCipher. All existing installs with an encrypted db would fail at this point (all new installs would have unencrypted databases).

[kpgalligan]

In summary, it would be much easier to comment if you can get the message string.

[Etienne Martin]

Sounds good, I'll look to see if we can get more information with that message. I've also noticed we are quite behind on versions. We are currently working with version 1.5.6 of SQLDelight which is nearly a year old. We are going to upgrade to the latest and also see if that makes a difference.

[kpgalligan]

Maybe? It shouldn't crash like that without a reason, but we'll see what you find.

[Etienne Martin]

For context as well, we haven't been able to reproduce the crash ourselves. We are also seeking more information on how/when it happens. So forgive us if this take a bit of time.

[Etienne Martin]

Small update here. We finally managed to ship out some changes to our builds to get more insight on this issue. Turns out on iOS we had background jobs that would run. iOS was abruptly killing the process at the end of the the grace period causing pending I/O to the database to fail and corrupt the file. We'll be diving into this deeper to find alternative approaches to using SQLDelight in background jobs to avoid these types of issues.

[kpgalligan]

> iOS was abruptly killing the process at the end of the the grace period causing pending I/O to the database to fail and corrupt the file
Any chance you're using SQLCipher?

[kpgalligan]

It's also odd that I/O failing would "corrupt" the file in that way. sqlite should deal with power failures, etc. You'd certainly lose data, but the db file should be able to repair itself (according to the sqlite docs, anyway).

[Etienne Martin]

Any chance you're using SQLCipher?

Not sure what that is, so I can't say for sure. We aren't using it directly, is this something embedded in the library?

[Etienne Martin]

It's also odd that I/O failing would "corrupt" the file in that way. sqlite should deal with power failures, etc. You'd certainly lose data, but the db file should be able to repair itself (according to the sqlite docs, anyway).

This is still an assumption right now. We gathered a few of the errors and it seems like the SQLDelight errors are all linked to reading/writing/deleting from the database. That's what the exceptions are telling us.

I'm only assuming that we are corrupting the database. I'll have to dig deeper in the logs to see what the error messages say when attempting to open the app after a background crash.

[kpgalligan]

Corrupt dbs are rare. Usually a hardware issue (bad storage) or modifying the db files directly and not through the SQLite library.
I guess if the background task attempted to access the db but didn't have access at that time, that error might make sense.

[Etienne Martin]

We wrote some code to capture the error while attempting to run a query that caused the crash. This is what we logged:

Failed to execute SQL query with error: co.touchlab.sqliter.interop.SQLiteExceptionErrorCode: authorization denied

Any insight on what this means?

[Etienne Martin]

We know this happens in our background process, and around the time iOS terminates the background job.

[kpgalligan]

authorization denied

Looks like a file access issue. There's no other "authorization" that I can think of.

[Etienne Martin]

So far from what we've gathered it was simply iOS killing the process while we had SQL queries execution. Basically pulling the rug under our feet while we attempted access to the SQLite DB. We managed to mitigate this by properly terminating our processes when iOS tells us to stop doing work.