SQLite Android Database Cursor window allocation of 2048 kb failed

Question

I have a routine that runs different queries against an SQLite database many times per second. After a while I would get the error

"android.database.CursorWindowAllocationException: - Cursor window allocation of 2048 kb failed. # Open Cursors = " appear in LogCat.

I had the app log memory usage, and indeed when usage reaches a certain limit the I get this error, implying it runs out. My intuition tells me that the database engine is creating a NEW buffer (CursorWindow) every time I run a query, and even though .close() the cursors, neither the garbage collector nor SQLiteDatabase.releaseMemory() are quick enough at freeing memory. I think the solution may lie in "forcing" the database to always write into the same buffer, and not create new ones, but I have been unable to find a way to do this. I have tried instantiating my own CursorWindow, and tried setting SQLiteCursor to it, but to no avail.

¿Any ideas?

EDIT: re example code request from @GrahamBorland:

public static CursorWindow cursorWindow = new CursorWindow("cursorWindow"); 
public static SQLiteCursor sqlCursor;
public static void getItemsVisibleArea(GeoPoint mapCenter, int latSpan, int lonSpan) {
query = "SELECT * FROM Items"; //would be more complex in real code
sqlCursor = (SQLiteCursor)db.rawQuery(query, null);
sqlCursor.setWindow(cursorWindow);
}

Ideally I would like to be able to .setWindow() before giving a new query, and have the data put into the same CursorWindow everytime I get new data.

Solution 1

Most often the cause for this error are non closed cursors. Make sure you close all cursors after using them (even in the case of an error).

Cursor cursor = null;
try {
    cursor = db.query(...
    // do some work with the cursor here.
} finally {
    // this gets called even if there is an exception somewhere above
    if(cursor != null)
        cursor.close();
}

To make your App crash when you are not closing a cursor you can enable Strict Mode with detectLeakedSqlLiteObjects in your Applications onCreate:

StrictMode.VmPolicy policy = new StrictMode.VmPolicy.Builder()
   .detectLeakedClosableObjects()
   .detectLeakedSqlLiteObjects()
   .penaltyDeath()
   .penaltyLog()
   .build();
StrictMode.setVmPolicy(policy);

Obviously you would only enable this for debug builds.

Solution 2

If you're having to dig through a significant amount of SQL code you may be able to speed up your debugging by putting the following code snippet in your MainActivity to enable StrictMode. If leaked database objects are detected then your app will now crash with log info highlighting exactly where your leak is. This helped me locate a rogue cursor in a matter of minutes.

@Override
protected void onCreate(Bundle savedInstanceState) {
   if (BuildConfig.DEBUG) {     
         StrictMode.setVmPolicy(new StrictMode.VmPolicy.Builder()
         .detectLeakedSqlLiteObjects()
         .detectLeakedClosableObjects()
         .penaltyLog()
         .penaltyDeath()
         .build());
    }
    super.onCreate(savedInstanceState);
    ...
    ...

Solution 3

I have just experienced this issue - and the the suggested answer of not closing the cursor while valid, was not how I fixed it. My issue was closing the database when SQLite was trying to repopulate it's cursor. I would open the database, query the database to get a cursor to a data set, close the database and iterate over the cursor. I noticed whenever I hit a certain record in that cursor, my app would crash with this same error in OP.

I assume that for the cursor to access certain records, it needs to re-query the database and if it is closed, it will throw this error. I fixed it by not closing the database until I had completed all the work I needed.

Solution 4

There is indeed a maximum size Android SQLite cursor windows can take and that is 2MB, anything more than this size would result into the above error. Mostly, this error is either caused by a large image byte array stored as blob in sql database or too long strings. Here is how i fixed it.

Create a java class eg. FixCursorWindow and put below code in it.

    public static void fix() {
        try {
            Field field = CursorWindow.class.getDeclaredField("sCursorWindowSize");
            field.setAccessible(true);
            field.set(null, 102400 * 1024); //the 102400 is the new size added
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Now go to your application class (create one if you don't have already) and make a call to the FixCursorWindow like this

public class App extends Application {

public void onCreate()
{
    super.onCreate();
    CursorWindowFixer.fix();

}

}

Finally, ensure you include your application class in your manifest on the application tag like this

    android:name=".App">

That's all, it should work perfectly now.

Solution 5

If you're running Android P, you can create your own cursor window like this:

if(cursor instanceof SQLiteCursor && Build.VERSION.SDK_INT >= Build.VERSION_CODES.P) {
    ((SQLiteCursor) cursor).setWindow(new CursorWindow(null, 1024*1024*10));
}

This allows you to modify the cursor window size for a specific cursor without resorting to reflections.

View more solutions

Comments

[Mohsin Naeem]

I don't know what is the problem..:) but I use to make SQLiteOpenHelper class singleton. So I never found any issues like this.

[alex]

No i don't use SQLiteOpenHelper, I create a static DataAccess class that contains an SQLiteDatabase. This works fine and i doubt the problem is there. The problem has more to do with the SQLite libraries creating a NEW container to place the results of every new query, rather than using the same container over and over again. And although I can close the cursor, the rate at which the GC cleans up is slower than the rate at which new containers are created, thus producing the memory hog.

[Graham Borland]

Edit it into your question, don't paste huge code snippets as comments!

[aij]

You can actually simply that example to avoid the null and null check.

[slott]

Just like open file pointers - ALWAYS handle the close in the finally section to ensure your code exists cleanly.

[Brian White]

That's great! I've made this standard with if (BuildConfig.DEBUG) {...} inside a 'static {...}' block of my main class.

[alfdev]

Great! But in release will work without the StrictMode?

[demaksee]

According to StrictMode javadoc: "Future versions of Android may catch more (or fewer) operations, so you should never leave StrictMode enabled in applications distributed on Google Play."

[jingyuan iu]

Breakthrough CursorWindow limit of only 2 megabytes

[Alex Kucherenko]

You'll probably get problems on Android Pie due to using reflection api