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.

Answer 2

I resolved it as follows: When I search for images from my remote bank, I check their size and if they are larger than 500 kb, I do a resizing process.

produto.setImagem_img(imagemTratada(rs.getBytes("imagem_img")));

The method that does the magic:

private byte[] imagemTratada(byte[] imagem_img){

    while (imagem_img.length > 500000){
        Bitmap bitmap = BitmapFactory.decodeByteArray(imagem_img, 0, imagem_img.length);
        Bitmap resized = Bitmap.createScaledBitmap(bitmap, (int)(bitmap.getWidth()*0.8), (int)(bitmap.getHeight()*0.8), true);
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        resized.compress(Bitmap.CompressFormat.PNG, 100, stream);
        imagem_img = stream.toByteArray();
    }
    return imagem_img;

}

Answer 3

Yes - this is a genuine issue. But I implemented a fix for this. We basically biometric images in our document. what I do now: store the individual biometric entities (Ex: fingerprint) in separate documents. Have a master document that has the ID of this doc.

Ex: we capture thumb and index finger for a person. We store 2 docs to store them - lets call them thumb.json and index.json. These have doc ID as thumb_100 and index_100 respectively. Now, we have a master biometric doc that looks something like:

"thumb": "thumb_100" "index": "index_100"

This helps us get the thumb and index finger print from the master document. More importantly, it ensures that an individual row size does not exceed the limit or cause an exception as seen earlier.

Answer 4

I found how to use length() and substr() to request only 1MB (the max for CursorWindow is 2MB), maybe it will help. (in case users have this issue and you need to avoid the exception) Also, you could use context.getContentResolver().query() with a selection that only includes the relevant columns, that way you won't load data related to the other columns (and save some space). Otherwise, I would recommend the first option you suggested, which is to store the file in internal storage and store the filepath in the database.

Answer 5

If you want to save image as a byte array in SQLite or Room, you should compress the byte array.

like this

Bitmap bmp = intent.getExtras().get("data");
ByteArrayOutputStream stream = new ByteArrayOutputStream();
bmp.compress(Bitmap.CompressFormat.PNG, 100, stream);
byte[] byteArray = stream.toByteArray();

Answer 6

Store image in device under your application private storage.And store that image path in database.