Android database - Cannot perform this operation because the connection pool has been closed

Question

I have strange problem with android database and cursors. Time to time (very rarely) happens, that I got crash report from customers. It's hard to find out why it crashes, as I have ~ 150 000 active users and maybe 1 report per week or so, so it's really some minor bug. Here is exception:

STACK_TRACE=java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.
at android.database.sqlite.SQLiteConnectionPool.throwIfClosedLocked(SQLiteConnectionPool.java:962)
at android.database.sqlite.SQLiteConnectionPool.waitForConnection(SQLiteConnectionPool.java:599)
at android.database.sqlite.SQLiteConnectionPool.acquireConnection(SQLiteConnectionPool.java:348)
at android.database.sqlite.SQLiteSession.acquireConnection(SQLiteSession.java:894)
at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:834)
at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:144)
at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:133)
at sk.mildev84.agendareminder.a.c.a(SourceFile:169)

Before every cursor "iterating and exploring" I use this code to make sure everything is ok:

db = instance.getWritableDatabase();
cursor = db.rawQuery(selectQuery, null);

if (isCursorEmptyOrNotPrepared(cursor)) {
    ...
    }

private synchronized boolean isCursorEmptyOrNotPrepared(Cursor cursor) {
        if (cursor == null)
            return true;

        if (cursor.isClosed())
            return true;

        if (cursor.getCount() == 0) // HERE IT CRASHES
            return true;

        return false;
    }

And it falls at line:

if (cursor.getCount() == 0)

Anyone know why? I think, I am checking all possible exceptions and conditions...Why is my app crashing here?

PS: All database methods are synchronized and I am correctly opening and closing database/cursors in all cases, I checked it many times.

Answer 1

Problem
If you try another operation after closing the database, it will give you that exception.Because db.close(); releases a reference to the object, closing the object if the last reference was released.

Solution
Keep a single SQLiteOpenHelper instance(Singleton) in a static context. Do lazy initialization, and synchronize that method. Such as

public class DatabaseHelper
{
    private static DatabaseHelper instance;

    public static synchronized DatabaseHelper getInstance(Context context)
    {
        if (instance == null)
            instance = new DatabaseHelper(context);

        return instance;
    }
//Other stuff... 
}

And you don't have to close it? When the app shuts down, it’ll let go of the file reference, if its even holding on to it.
i.e. You should not close the DB since it will be used again in the next call. So Just remove

db.close();

For more info See at Single SQLite connection