Row too big to fit into cursorwindow ошибка

I keep hitting this annoying message !

When data finished insert into local database, I saw this message

JNI critical lock held for 30.083ms on Thread[27,tid=23883,Runnable,Thread*=0xce150a00,peer=0x12cc0190,"Sqflite"]

If I select data from the table, I get

W/CursorWindow(23809): Window is full: requested allocation 3095146 bytes, free space 2096696 bytes, window size 2097152 bytes
E/SQLiteQuery(23809): exception: Row too big to fit into CursorWindow requiredPos=0, totalRows=1; query: SELECT * FROM description_table;
I/flutter (23809): DatabaseException(Row too big to fit into CursorWindow requiredPos=0, totalRows=1) sql 'SELECT * FROM defect_description_table;' args []}

I don’t have any blob data, all are String. So why would this happened?

Here the json structure

[{ "id": 1, "name": "Descriptions","data": [{..},{...} ... ]},{....},{....}]

I think the issue is on the data list,because it contains lot of data(it has 10298) ?

What is the solution for this?

My insert method

Future insertData(
      BuildContext context, String urls, String accessToken) async {
    CategoryTableData categoryData = CategoryTableData();
    try {
      var desc = List<Desc>();
      var headers = {
        'authorization': "Bearer" + " " + accessToken,
        "Accept": "application/json"
      };
      var url = xxx;
      var response = await http.get(url, headers: headers);
      var res = json.decode(response.body);
      for (var i in res) {
        if (i['name'] == "Descriptions") {
          desc.add(Desc(
              id: i['id'], name: i['name'], data: i['data']));
        }
      }
      await dao.batch((b) {
        b.insertAll(_dao.descTable, desc);
      });
      categoryData = await _dao.selectAllCategories();
      return categoryData;
    } catch (e) {
      print(e);
      categoryData = await _dao.selectAllCategories();
      return categoryData;
    }
  }

Desc

class Desc extends Table {
  IntColumn get id => integer().nullable()();
  TextColumn get name => text().named("name").nullable()();
  TextColumn get data => text().map(const ListConverter()).nullable()();
}

Expected Behavior

The query with large data should succeed without an error.

Current Behavior

When trying to a do a SELECT on large rows, the query fails with error:

Row too big to fit into CursorWindow

Steps to Reproduce (for bugs)

This is affecting several users of Joplin:

laurent22/joplin#1771
laurent22/joplin#1690

and it happens when trying to read or write to the database large text data (some users mention 4MB).

It seems to have started only recently so it could be due to a React Native upgrade or other change to the Android build.

What I’m wondering is, is there any way to prevent this error, for example some option that can be set to increase the available memory?

The Joplin desktop application uses the same architecture (but of course with a different sqlite package) and this error doesn’t happen, so it seems it’s possible to prevent it. 4MB is a bit large but not huge either so it seems it should work, at least with an option.

Your Environment

• React Native SQLite Storage Version used: 3.3.10
• React Native version used: 0.59.10
• Operating System and version (simulator or device): Android (device)
• Link to your project: https://github.com/laurent22/joplin

I am getting below exception only in android 9, after reinstalling everything looks good,

Exception:

android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=0, totalRows=1...

Code:

Cursor cursor = database.query(......);
    if(cursor == null || cursor.getCount() < 0) { //Here is the error
        Log.d("Error", "count : null");
        return "";
    }

Edited:

java.lang.RuntimeException: An error occurred while executing doInBackground()
at android.os.AsyncTask$3.done(AsyncTask.java:354)
at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:383)
at java.util.concurrent.FutureTask.setException(FutureTask.java:252)
at java.util.concurrent.FutureTask.run(FutureTask.java:271)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
at java.lang.Thread.run(Thread.java:764)

Caused by: android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=0, totalRows=1
at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)
at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:859)
at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:836)
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)

Thanks in advance guys

This worked for me, you have to put it on your Main Activity.

try {
    Field field = CursorWindow.class.getDeclaredField("sCursorWindowSize");
    field.setAccessible(true);
    field.set(null, 100 * 1024 * 1024); //the 100MB is the new size
    } catch (Exception e) {
       e.printStackTrace();
    }

Try to scale bitmap:

Bitmap.createScaledBitmap

Try this new constructor method above API 28. Maybe you should set a limited windowSizeBytes for CursorWindow and try-catch the exception.

Related cts code (https://android.googlesource.com/platform/cts/+/master/tests/tests/database/src/android/database/sqlite/cts/SQLiteCursorTest.java) :

    public void testRowTooBig() {
        mDatabase.execSQL("CREATE TABLE Tst (Txt BLOB NOT NULL);");
        byte[] testArr = new byte[10000];
        Arrays.fill(testArr, (byte) 1);
        for (int i = 0; i < 10; i++) {
            mDatabase.execSQL("INSERT INTO Tst VALUES (?)", new Object[]{testArr});
        }
        // Now reduce window size, so that no rows can fit
        Cursor cursor = mDatabase.rawQuery("SELECT * FROM TST", null);
        CursorWindow cw = new CursorWindow("test", 5000);
        AbstractWindowedCursor ac = (AbstractWindowedCursor) cursor;
        ac.setWindow(cw);
        try {
            ac.moveToNext();
            fail("Exception is expected when row exceeds CursorWindow size");
        } catch (SQLiteBlobTooBigException expected) {
        }
    }

Others:

Since this article was originally published, we’ve added new APIs in
the Android P developer preview to improve the behavior above. The
platform now allows apps to disable the ⅓ of a window heuristic and
configure CursorWindow size.

Ref Link:
https://medium.com/androiddevelopers/large-database-queries-on-android-cb043ae626e8