What can be done about the fact that Android automatically deletes corrupt SQLite files?

Question

When Android opens an SQLite file, and the file is corrupt, Android deletes the file.

As surprising as it may sound, this behavior is implemented clearly in the Android source code, leading to consternation and to this Android issue.

Anyway, as app developers we just have to deal with it. What is the best strategy when opening an SQLite file?

• Corrupt files are actually often recoverable, so we can't afford to take any risk of losing one of those corrupt files.
• Creating a backup before opening is very time-costly, and would make the app startup really slow, so anything smarter would be greatly appreciated.

Answer 1

The issue has been fixed starting from API level 11. Now there exists an interface: DatabaseErrorHandler which you can implement to define your own onCorruption() method. At the opening of your database you can pass this DatabaseErrorHandler as a parameter to the constructor of SQLiteOpenHelper.

e.g.

public class MyDbErrorHandler implements DatabaseErrorHandler {
    @Override
    onCorruption(SQLiteDatabase db) {
        // Back up the db or do some other stuff
    }
}

SQLiteOpenHelper dbHelper = new SQLiteOpenHelper(context, "MyDbName", null, 1,
                                                 new MyDbErrorHandler());

SQLiteDatabase db = dbHelper.getWritableDatabase();

---

For Systems with an API level below 11 and for those who dont want to use this approach there are several alternatives.

1. Android data backup

Android offers a backup service which automatically copys the application data to a remote 'cloud' storage. If a database gets corrupted or the application is reinstalled after factory reset. The application data can be restored from the remote data.

For further information see: http://developer.android.com/guide/topics/data/backup.html

2. JDBC (sqldroid)

One approach could be implementing your own database connector, either native JDBC or with the sqldroid library. It is officially not supported by google and you cannot be sure whether it will be still available in future Android versions.

3. Berkley DB Java Edition

An interesting approach, also with a look to performance handling large data amounts, is the Berkley DB Java Edition.

Here is a tutorial how to use it in Android: http://download.oracle.com/docs/cd/E17277_02/html/HOWTO-Android.html

4. Customizing the android libraries

Another more risky approach is to implement your own database class by copying or extending the SQLiteDatabase.java from the android source and reimplement or override the critical parts which are:

public static SQLiteDatabase openDatabase(String path, CursorFactory factory, int flags) {
    SQLiteDatabase sqliteDatabase = null;
    try {
        // Open the database.
        sqliteDatabase = new SQLiteDatabase(path, factory, flags);
        if (SQLiteDebug.DEBUG_SQL_STATEMENTS) {
            sqliteDatabase.enableSqlTracing(path);
        }
        if (SQLiteDebug.DEBUG_SQL_TIME) {
            sqliteDatabase.enableSqlProfiling(path);
        }
    } catch (SQLiteDatabaseCorruptException e) {
        // Try to recover from this, if we can.
        // TODO: should we do this for other open failures?
        Log.e(TAG, "Deleting and re-creating corrupt database " + path, e);
        EventLog.writeEvent(EVENT_DB_CORRUPT, path);
        if (!path.equalsIgnoreCase(":memory")) {
            // delete is only for non-memory database files
            new File(path).delete();
        }
        sqliteDatabase = new SQLiteDatabase(path, factory, flags);
    }
    ActiveDatabases.getInstance().mActiveDatabases.add(
            new WeakReference<SQLiteDatabase>(sqliteDatabase));
    return sqliteDatabase;
}

and:

/* package */ void onCorruption() {
    Log.e(TAG, "Removing corrupt database: " + mPath);
    EventLog.writeEvent(EVENT_DB_CORRUPT, mPath);
    try {
        // Close the database (if we can), which will cause subsequent operations to fail.
        close();
    } finally {
        // Delete the corrupt file.  Don't re-create it now -- that would just confuse people
        // -- but the next time someone tries to open it, they can set it up from scratch.
        if (!mPath.equalsIgnoreCase(":memory")) {
            // delete is only for non-memory database files
            new File(mPath).delete();
        }
    }
}

The dangerous part about that is, that you also would have to reimplement the helper classes that access the SQLiteDatabase such as SQLiteOpenHelper. Since the SQLiteDatabase class uses factory methods you could face unexpected side effects.

Answer 2

I faced the same issue and asked a question about it here. I regularly backup my database to the SD-card, but I cannot recommend it. It seems as if a database that is copied from SD-cards used in newer Android phones is considered corrupt after the copy is completed on the older versions of SQLite that is still used on android 2.3.6.

If your database is small enough then I would recommend keeping a backup, but keep it on the internal memory. Unless it would anger your users, do not enable the "install to sd-card"-option, I believe it is correlated to the issue. After these precautions your database should be relatively safe.

About the slower start time: I do my backups in a background thread when the app is closed, this is the most likely time that the phone has to do some background work without troubling the user.