Getting Random android.database.sqlite.SQLiteBlobTooBigException

Question

I am using Room in my app as a Single Source of Truth, so everything that comes from the backend I save in my room database, which then returns a Flowable that fires an event every time the data changes. This is my PlacesDAO:

    @Dao
abstract class PlacesDao {


    @Query("select * from places where placeId = :id")
    abstract fun getPlace(id: String): Flowable<PlaceVO>

    @Query("select * from places where placeId in (:placesIds) order by distance, placeName ASC")
    abstract fun getPlaces(placesIds: List<String>): Flowable<List<PlaceVO>>


    @Query("select * from places join list_results where Places.placeId = list_results.id order by distance, placeName ASC")
    abstract fun getFilteredPlaces(): Flowable<List<PlaceVO>>



    @Query("select * from places join user_places where Places.placeId = user_places.placeId AND user_places.userId = :userId order by distance, placeName ASC ")
    abstract fun getAllByUser(userId: String) : Flowable<List<PlaceVO>>



    @Insert(onConflict = OnConflictStrategy.REPLACE)
    abstract fun realSavePlaces(places:List<PlaceVO>)



    fun savePlaces(places: List<PlaceVO>){
        Timber.w("PAGELIST - Saving places again!!")
        realSavePlaces(places)
    }

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    abstract fun savePlace(place: PlaceVO)



    @Query("DELETE from places")
    abstract fun deleteAll()

    @Query("select * from places")
    abstract fun getAll(): Single<List<PlaceVO>>




    @Query("select * from places where (experienceId IS NOT NULL) AND (experienceId != '') order by placeName")
    abstract fun getMyPlaces(): Flowable<List<PlaceVO>>

    @Query("update places set distance = :distance and distanceSet = 1 where placeId = :id")
    abstract fun updateDistance(id: String, distance: Float)
}

Now in my app theres a few actions that would trigger changing the data in this table, which then causes my UI to receive all items contained in the table (around 3000-5000).

It does niot always happen (which makes it hard to reproduce) but every now and then I will get the following crash:

Caused by android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=1223, totalRows=114
       at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(SQLiteConnection.java)
       at android.database.sqlite.SQLiteConnection.executeForCursorWindow + 895(SQLiteConnection.java:895)
       at android.database.sqlite.SQLiteSession.executeForCursorWindow + 836(SQLiteSession.java:836)
       at android.database.sqlite.SQLiteQuery.fillWindow + 62(SQLiteQuery.java:62)
       at android.database.sqlite.SQLiteCursor.fillWindow + 157(SQLiteCursor.java:157)
       at android.database.sqlite.SQLiteCursor.onMove + 128(SQLiteCursor.java:128)
       at android.database.AbstractCursor.moveToPosition + 237(AbstractCursor.java:237)
       at android.database.AbstractCursor.moveToNext + 269(AbstractCursor.java:269)
       at com.myapp.android.model.db.dao.PlacesDao_Impl$6.call + 814(PlacesDao_Impl.java:814)
       at com.myapp.android.model.db.dao.PlacesDao_Impl$6.call + 771(PlacesDao_Impl.java:771)
       at io.reactivex.internal.operators.maybe.MaybeFromCallable.subscribeActual + 46(MaybeFromCallable.java:46)
       at io.reactivex.Maybe.subscribe + 4262(Maybe.java:4262)
       at io.reactivex.internal.operators.flowable.FlowableFlatMapMaybe$FlatMapMaybeSubscriber.onNext + 132(FlowableFlatMapMaybe.java:132)
       at io.reactivex.internal.operators.flowable.FlowableObserveOn$ObserveOnSubscriber.runAsync + 407(FlowableObserveOn.java:407)
       at io.reactivex.internal.operators.flowable.FlowableObserveOn$BaseObserveOnSubscriber.run + 176(FlowableObserveOn.java:176)
       at io.reactivex.internal.schedulers.ExecutorScheduler$ExecutorWorker$BooleanRunnable.run + 260(ExecutorScheduler.java:260)
       at io.reactivex.internal.schedulers.ExecutorScheduler$ExecutorWorker.run + 225(ExecutorScheduler.java:225)
       at java.util.concurrent.ThreadPoolExecutor.runWorker + 1167(ThreadPoolExecutor.java:1167)
       at java.util.concurrent.ThreadPoolExecutor$Worker.run + 641(ThreadPoolExecutor.java:641)
       at java.lang.Thread.run + 764(Thread.java:764)

I am only storing text info, as proved by this class:

@Entity(tableName = "places")
data class PlaceVO(
        @PrimaryKey
        var placeId: String,

        var googleId: String,

        var placeName: String,

        var phoneNumber: String,

        @Embedded
        var primaryCategory: Category?,

        var primaryCategoryTags: List<CategoryTag> = emptyList(),

        var secondaryCategories: List<Category>? = emptyList(),

        var images: List<Image>,

        var website: String,

        var formattedAddress: String? = "",

        var vicinity: String = "",

        var vicinityShort: String = "",

        var city: String? = "",

        var neighbourhood: String?,

        var longitude: Double,

        var latitude: Double,

        var openingHours: List<String>,

        var combinedHighlights: List<HighlightCountWrapper>,
        @Embedded
        var ownExperience: OwnExperience?,

        var otherExperiences: List<Experience>,

        var distance: Float?,

        var distanceSet: Boolean = false,

        var comment: String
) : MarkerPlace {

}

Experience class:

@Entity
data class Experience(
        @Json(name="id")
        val experienceId: String,
        @Embedded
        val owner: User,
        val description: String?,
        val highlights: List<Highlight>?,
        val images: List<Image> = emptyList(),
        val createdDate: Date,
        val updatedDate: Date,
        var privacyLevel: AddExperience.Privacy? = null)

Some TypeConverters:

@TypeConverter
    fun toHighlightWrapperList(value: String): List<HighlightCountWrapper> {
        val type = Types.newParameterizedType(List::class.java, HighlightCountWrapper::class.java)
        return moshi.adapter<List<HighlightCountWrapper>>(type).fromJson(value) ?: emptyList()
    }

    @TypeConverter
    fun fromHighlightWrapperList(list: List<HighlightCountWrapper>): String {
        val type = Types.newParameterizedType(List::class.java, HighlightCountWrapper::class.java)
        var adapter: JsonAdapter<List<HighlightCountWrapper>> = moshi.adapter<List<HighlightCountWrapper>>(type)
        return adapter.toJson(list)
    }

 @TypeConverter
    fun toExperienceList(value: String): List<Experience> {
        val type = Types.newParameterizedType(List::class.java, Experience::class.java)
        return moshi.adapter<List<Experience>>(type).fromJson(value) ?: emptyList()
    }

    @TypeConverter
    fun fromExperienceList(list: List<Experience>): String {
        val type = Types.newParameterizedType(List::class.java, Experience::class.java)
        var adapter: JsonAdapter<List<Experience>> = moshi.adapter<List<Experience>>(type)
        return adapter.toJson(list)
    }

@TypeConverter
    fun toImageList(value: String): List<Image> {
        val type = Types.newParameterizedType(List::class.java, Image::class.java)
        return moshi.adapter<List<Image>>(type).fromJson(value) ?: emptyList()
    }

    @TypeConverter
    fun fromImageList(list: List<Image>): String {
        val type = Types.newParameterizedType(List::class.java, Image::class.java)
        var adapter: JsonAdapter<List<Image>> = moshi.adapter<List<Image>>(type)
        return adapter.toJson(list)
    }

so how can it be that my rows are too big for SQLite? Especially when sometimes exactly the same data will be returned without a problem?

"so how can it be that my rows are too big for SQLite?" -- the size limit is 1MB, IIRC. You have a lot of `List` properties, including two scary-looking `List`. "Especially when sometimes exactly the same data will be returned without a problem?" -- it might depend on what else is being returned in the query. — CommonsWare, Aug 30 '19 at 12:41
Would modeling them as separate entities instead of stringifying them into json make the issue better? — M Rajoy, Aug 30 '19 at 15:03

score 0 · Answer 1 · answered Dec 15 '19 at 23:34

I found how to use length() and substr() to request only 1MB (the max for CursorWindow is 2MB), maybe it will help. (in your case you could simply divide the requests into chunks of 100 rows and then close the cursor, then repeat) It seems that you are storing images, in which case it would be better to store them in internal storage, and only store the file path in the database.

Especially when sometimes exactly the same data will be returned without a problem?

If you mean exactly the same rows (like the rows from 1000 to 2000), but from different devices, it might be the case that they have different "max-sizes" for the CursorWindow, in my case it seems to be 2MB.

can you update with sample code – Ramesh Bhati Nov 23 '21 at 06:14 — Ramesh Bhati, Nov 23 '21 at 06:14

Getting Random android.database.sqlite.SQLiteBlobTooBigException

1 Answers1

Linked