Android SQLite memory leak.

Question

I know this is a long post. Pls don't mind.

Leak found
E/Database( 4549): java.lang.IllegalStateException: mPrograms size 1
E/Database( 4549):  at android.database.sqlite.SQLiteDatabase.finalize(SQLiteDatabase.java:1668)
E/Database( 4549):  at dalvik.system.NativeStart.run(Native Method)
E/Database( 4549): Caused by: java.lang.IllegalStateException: /data/data/com.rjblackbox.droid.fvt/databases/fvt.db SQLiteDatabase created and never closed
E/Database( 4549):  at android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:1694)
E/Database( 4549):  at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:738)
E/Database( 4549):  at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:760)
E/Database( 4549):  at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:753)
E/Database( 4549):  at android.app.ApplicationContext.openOrCreateDatabase(ApplicationContext.java:473)
E/Database( 4549):  at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:193)
E/Database( 4549):  at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:98)
E/Database( 4549):  at com.rjblackbox.droid.fvt.FVTDataHelper.<init>(FVTDataHelper.java:37)
E/Database( 4549):  at com.rjblackbox.droid.fvt.FVTNotificationService.getNextEntry(FVTNotificationService.java:91)
E/Database( 4549):  at com.rjblackbox.droid.fvt.FVTNotificationService.access$2(FVTNotificationService.java:90)
E/Database( 4549):  at com.rjblackbox.droid.fvt.FVTNotificationService$1.run(FVTNotificationService.java:53)
E/Database( 4549):  at android.os.Handler.handleCallback(Handler.java:587)
E/Database( 4549):  at android.os.Handler.dispatchMessage(Handler.java:92)
E/Database( 4549):  at android.os.Looper.loop(Looper.java:123)
E/Database( 4549):  at android.app.ActivityThread.main(ActivityThread.java:4363)
E/Database( 4549):  at java.lang.reflect.Method.invokeNative(Native Method)
E/Database( 4549):  at java.lang.reflect.Method.invoke(Method.java:521)
E/Database( 4549):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:860)
E/Database( 4549):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618)
E/Database( 4549):  at dalvik.system.NativeStart.main(Native Method)

This is the stack trace of my memory leak. Below is the source code for my data Helper. In addition to an Activity, I also have a Service accessing the data. This must be a piece of cake for experienced developers.

public class FVTDataHelper {
    private static final String DB_NAME = "fvt.db";
    private static final int DB_VERSION = 1;

    private static final String TABLE_TIMERS = "timers";
    private static final String[] TABLE_TIMERS_COLUMNS = {"id", "name", "added", "expires"};
    private static final String TIMER_INSERT = "INSERT INTO " + 
    TABLE_TIMERS + "('name', 'added', 'expires') VALUES (?, ?, ?)";

    private SQLiteDatabase db;
    private Cursor c;
    private SQLiteStatement timerInsert;

    //Constructor
    public FVTDataHelper(Context ctx) {
        OpenHelper oh = new OpenHelper(ctx);
        db = oh.getWritableDatabase();
        timerInsert = db.compileStatement(TIMER_INSERT);
    }

    public void addTimerEntry(TimerEntry entry) {
        String name = entry.getName();
        long added = entry.getAdded();
        long expires = entry.getExpires();

        timerInsert.bindString(1, name);
        timerInsert.bindString(2, Long.toString(added));
        timerInsert.bindString(3, Long.toString(expires));
        timerInsert.executeInsert();
    }

    public List<TimerEntry> getTimerEntries() {
        ArrayList<TimerEntry> entries = new ArrayList<TimerEntry>();
        c = db.query(TABLE_TIMERS, TABLE_TIMERS_COLUMNS, null, null, null, null, "expires asc");

        if(c.moveToFirst()) {
            int id;
            String name;
            long added;
            long expires;

            do {
                id = c.getInt(0);
                name = c.getString(1);
                added = c.getLong(2);
                expires = c.getLong(3);

                if((System.currentTimeMillis() - added) >= 0) {
                    entries.add(new TimerEntry(id, name, added, expires));
                }
            } while(c.moveToNext());
        }
        c.close();
        return entries;
    }

    public void deleteTimerEntry(int id) {
        db.delete(TABLE_TIMERS, "id=" + id, null);
    }

    //Helper class for creating and updating database 
    private static class OpenHelper extends SQLiteOpenHelper {
        private Context ctx;

        public OpenHelper(Context ctx) {
            super(ctx, DB_NAME, null, DB_VERSION);
            this.ctx = ctx;
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            String sqlDump = getSQLDump();

            String[] statements = sqlDump.split("\n");
            for(String statement : statements) {
                if(DEBUG) Log.d(TAG, statement);
                db.execSQL(statement);
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            onCreate(db);
        }

        //Helper: Returns SQL statements from the dump
        private String getSQLDump() {
            StringBuilder sb = new StringBuilder();
            try {
                InputStream is = ctx.getAssets().open("dump.sql");
                int c;
                while((c = is.read()) != -1) {
                    sb.append((char) c);
                }
                is.close();
            } catch (IOException e) {
                Log.d(TAG, e.getMessage());
            }
            return sb.length() > 0 ? sb.toString() : ";";
        }
    }
}

Answer 1

You need to close your database before you reopen it, the stack trace message is quite clear about what the problem is.

One solution is to make use of the android.app.Application class and store the open database instance in there. If you do do this, be sure to open the database using the Application context rather than the Activity context otherwise you can leak the context when your activity is destroyed.

The other solution is to close the database in onDestroy/onStop, etc and reopen it in onCreate/onStart, etc.