Android SQLite database gets corrupted

Question

This link describes my problem exactly: http://old.nabble.com/Android-database-corruption-td28044218.html#a28044218

There are about 300 people using my Android App right now and every once and while I get a crash report to the server with this stack trace:

android.database.sqlite.SQLiteDatabaseCorruptException: database disk image is malformed
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2596)
    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2621)
    at android.app.ActivityThread.access$2200(ActivityThread.java:126)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1932)
    at android.os.Handler.dispatchMessage(Handler.java:99)
    at android.os.Looper.loop(Looper.java:123)
    at android.app.ActivityThread.main(ActivityThread.java:4595)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:521)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:860)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618)
    at dalvik.system.NativeStart.main(Native Method) Caused by: android.database.sqlite.SQLiteDatabaseCorruptException: database disk image is malformed
    at android.database.sqlite.SQLiteQuery.native_fill_window(Native Method)
    at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:75)
    at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:295)
    at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:276)
    at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:171)
    at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:248)

The result is the app crashing and all the data in the DB being lost.

One thing to note is that every time I read or write to the database I get a new SQLiteDatabase and close it as soon as I'm done. I did this in an attempt to prevent these kind of corruption errors.

I also tried synchronizing all DB reads and writes using a single static object and that didn't seem to help.

Is it possible this is just a SQLite bug?

I found a similar bug with the built-in email app here: http://code.google.com/p/android/issues/detail?id=5610.

Here is my code:

public class KeyValueTableAdapter extends BaseTableAdapter {

    private String tableName;
    private String keyColumnName;
    private String valueColumnName;

    public KeyValueTableAdapter(Context context, String tableName, String keyColumnName, String valueColumnName) {
        super(context);
        this.tableName = tableName;
        this.keyColumnName = keyColumnName;
        this.valueColumnName = valueColumnName;
    }

    protected String getStringValue(int key) {
        Cursor cursor = null;
        SQLiteDatabase db = null;
        String value;

        try {
            db = dbOpenHelper.getReadableDatabase();
            cursor = db.query(true, tableName, new String[] { valueColumnName }, keyColumnName + "=" + key, null, null, null, null, null);

            if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
                value = null;
            } else {
                value = cursor.getString(0);
            }
        } finally {
            if (cursor != null) cursor.close();
            if (db != null) db.close();
            dbOpenHelper.close();
        }

        return value;
    }
}


public abstract class BaseTableAdapter {

    protected DbOpenHelper dbOpenHelper;

    public BaseTableAdapter(Context context) {
        this.dbOpenHelper = new DbOpenHelper(context, DatabaseSettings.DATABASE_NAME, null, DatabaseSettings.DATABASE_VERSION);
    }

}

Answer 1

Using multiple instances of SQLiteDatabase could be causing your problem if you have two instances updating the same database file at the same time.

Answer 2

Most likely the database process(es) is getting killed during an I/O. For example by a task killer, or if you're allowing db write operations to continue during a time when the app should shutdown or sleep...

See if you can reproduce the issue by putting your app in a DB write loop and using a task killer on it.

Scenario: 32 bytes being written to the database, the writer task gets killed after only writing 10, result: database left in inconsistent and possibly corrupt state.

Also see: http://stackoverflow.com/questions/2720164/android-process-killer

EDIT: opening and closing the DB for each read/write? stop that! :)

Answer 3

Implement a backup db process each day, and if the DB gets corrupted you just replace the database with a backup. You can use simple file copy paste methods to maintain a backup each day.

Answer 4

"the DB holds session information so it's not very feasible to do a backup. The data changes by the minute"

You should try using SharedPreferences: it stores key-value pairs (in the background, it uses a file). Storing values:

SharedPreferences sp=MyActivity.getSharedPreferences("Name",Context.MODE_PRIVATE);
SharedPreferences.Editor editor=sp.edit();
editor.putString("key",value);
editor.putBoolean("another",true);
editor.commit();

Retrieving data:

sp.getString("key","Not found"); 
// "Not found" is the default value
//if sp doesn't contain the specified key
sp.getBoolean("another",false); 
// false is the default value
//if sp doesn't contain the specified key

See getSharedPreferences and SharedPreferences for a more detailed description.

Answer 5

Since I can't comment -yet- on Brad's post.

I have to agree with the extra overhead.

I have a HTC Magic as my daily phone, and ram is always an issue.

Android phones are at very different ends of the $$$

Some are super cheap and some are super expensive, this basically comes down to ram, and cpu.

People who run task killers do ruin their android phones.

As a developer you should suggest people not to use them, or just deny support to people who use task killers, since android doesn't need these "improvements" (ask steve (cyanogen))

Also the new statement in android is very expensive.

You want to limit the amount of new calls when programming for android.

Programming for android is all about reuse of the precious memory. (HTC Magics/Dreams only have 96MB available to applications, and most of it is already in use)

As for your SQLiteDB...the API says that your SQLiteDB is private to your application.

I don't see why you need to open and close a NEW connection to it each time you want to read or write to it.

I would rather keep the connection open until the user looses focus from it.

However, if you are writing a content provider, that is a different story.