SQLiteBlobTooBigException: Row too big to fit into CursorWindow while writing to DB

Question

I see the above error when I try to add to sqllite DB on android.I am using cloudant library. Here's the stack trace:

2019-07-17 18:04:48.292 5522-5753/org.iprd.identity E/SQLiteQuery: exception: Row too big to fit into CursorWindow requiredPos=0, totalRows=1; query: SELECT docs.docid, docs.doc_id, revid, sequence, current, deleted, parent, json FROM revs, docs WHERE docs.docid=? AND revs.doc_id=docs.doc_id AND revid=? ORDER BY revs.sequence LIMIT 1
2019-07-17 18:04:48.302 5522-5522/org.iprd.identity E/DatabaseImpl: Failed to create document
    java.util.concurrent.ExecutionException: android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=0, totalRows=1
        at java.util.concurrent.FutureTask.report(FutureTask.java:123)
        at java.util.concurrent.FutureTask.get(FutureTask.java:193)
        at com.cloudant.sync.internal.documentstore.DatabaseImpl.get(DatabaseImpl.java:1084)
        at com.cloudant.sync.internal.documentstore.DatabaseImpl.create(DatabaseImpl.java:925)

I never got this issue previously, however, I recently made a change and started using a 3rd party tool to give me some images. With this new tool, the image sizes seem to be much bigger than before. I am storing these images in my db as a string by doing a Base64 encoding of the image. However, with this new tool, while storing the image, I get the above exception.

I have a unit test which I tried using one of the generated strings from this image, but that also throws an error that the string size is too large and does not even compile.

What is the best approach to solve this? -

1. What I was thinking of doing is to store the image in the device itself and just store the path in the DB.

2. Is there some compression we can do to the image to reduce its file size?

3. Is it possible to tune some db setting to ensure that it can handle larger image sizes?

Thanks in advance!

Answer 1

What I was thinking of doing is to store the image in the device itself and just store the path in the DB.

Not going to answer, as you are the best to answer that question.

Is there some compression we can do to the image to reduce its file size?

Possibly but that isn't necessarily going to eliminate the problem. As even if you compressed the images to say 2Mb (half the 4Mb limit of a Cursor Window ) then the likeliehood is that only 1 row would fit into a cursor window at a time. The result would likely be very poor performance and the bottlehead isn't really SQLite but handling the Cursor.

• not recommended

Another solution, but still costly, would be to store the image in parts/chunks, an example of doing so (noting that no tuning was undertaken to establish the 256kb chunk size) can be found here. Noting that the response time, is considered very poor.

• not recommended but if poor performance is not an issue then could be a solution

The solution that doesn't require the overheads of additional compression/decompsression is to store images or images over a set size in the file system (e.g. external storage) and store the path or a part of the path.

• The latter option, storing small images in the database and larger images as files, could take advantage of 35% Faster Than The Filesystem).

• an example of a mixed path/blob solution in in the answer here.

  • Note this example could be used as the basis for both just storing paths or for a mixed solution.

• recommended

Is it possible to tune some db setting to ensure that it can handle larger image sizes?

Yes.

You could consider:-

• optimising your queries. See, as a start, EXPLAIN QUERY PLAN and The SQLite Query Optimizer Overview
• increasing the page size (noting the limitations as when),
• increasing the cache size,
• risk turning synchronous to off and
• risk using exclusive locking.
• compiling your own tuned version of SQLite applying compile time options to suit.
• further reading the above links as there may be additional applicable tuning settings.