SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=0, totalRows=4

[grzesiek2010]

Problem description

https://console.firebase.google.com/u/1/project/api-project-322300403941/crashlytics/app/android:org.odk.collect.android/issues/1d44e46d70a9c63deff958a80ea8ef4d?time=last-seven-days&versions=v2023.1.0%20(4611)&types=crash&sessionEventKey=64225AB003410001654A7B0AB7CDB18D_1793875140906089215

Caused by android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=0, totalRows=4
       at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(SQLiteConnection.java)
       at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:904)
       at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:851)
       at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
       at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:149)
       at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:137)
       at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:220)
       at org.odk.collect.android.database.forms.DatabaseFormsRepository.getFormsFromCursor(DatabaseFormsRepository.java:249)
       at org.odk.collect.android.database.forms.DatabaseFormsRepository.queryForForms(DatabaseFormsRepository.java:209)

[grzesiek2010]

According to the stacktrace the issue takes place when we query for only 4 forms. I've played with changing the CursorWindowSize and with 4 downloaded forms I'm able to reproduce the issue if I set the size to less than 1kb where the default size should be ~2mb. If it's more than 1kb everything works fine. Such a small size would be ridiculous and I don't know if it's real. I think we should first check and log the real size to know what's going on.
It would be something like this: #5529

[grzesiek2010]

What's also interesting it's not a repetitive crash. It looks like it happens once when a user for example opens the list of forms but then everything is fine. Maybe the CursorWindowSize is too small somehow and then it's increased?

[seadowg]

Let's see what info we get in from the changes in #5529

[seadowg]

We've added updated logging with #5541

[seadowg]

It looks like we're seeing the default window size (2MB) get logged before the crash occurs looking at recent reports. @grzesiek2010 do you want to double-check this? I liked your theory that the crash happens once because of a very small default and then gets upped to 2MB, but these reports would suggest that isn't the case.

[grzesiek2010]

We don't have any logs after merging #5541 because it wasn't a part of v2023.1.2 so we still don't know if my theory was correct or not. we have to wait till releasing v2023.2

[seadowg]

We don't have any logs after merging #5541 because it wasn't a part of v2023.1.2 so we still don't know if my theory was correct or not. we have to wait till releasing v2023.2

Ah right that makes sense. My bad!

[grzesiek2010]

I was right that this problem has something to do with db upgrades, the new logs confirm that: https://console.firebase.google.com/u/1/project/api-project-322300403941/crashlytics/app/android:org.odk.collect.android/issues/1d44e46d70a9c63deff958a80ea8ef4d?time=last-seven-days&versions=v2023.2.3%20(4681)&types=crash&sessionEventKey=64C3EBDA029700015BB465AE9D3F60BA_1839351971234625326
I'm not sure what we can do next. Maybe try to read the database in the main menu so that upgrades are triggered earlier and exceptions are caught earlier too? This assumes the exceptions are thrown just for the first operation on the database after upgrading. I'm not sure if that's true but we can give it a try.
@seadowg any thoughts?

[seadowg]

It looks like the app recovers from this after the crash right? I think if that's the case, we can pause investigating for the moment now we have more of a sense of when/why it's happening. The crash numbers aren't very large (only ~30 for the current release).

[grzesiek2010]

The number of crashes is going to be lower and lower since it's been a while since we upgraded our databases the last time. However, it will grow next time we need to do that and then it might be a problem.
I agree we can remove this issue from the current milestone.
@getodk/testers we have one Huawei device but it's with an old Android version as I remember right?

[srujner]

The number of crashes is going to be lower and lower since it's been a while since we upgraded our databases the last time. However, it will grow next time we need to do that and then it might be a problem. I agree we can remove this issue from the current milestone. @getodk/testers we have one Huawei device but it's with an old Android version as I remember right?

Yep, only Huawei we've got is with Android 5.1

[grzesiek2010]

Ok so let's leave it for now. We can try to reproduce once we have a Huawei device with a newer Android version.