Android: Database corruption issue

I recently ran into an issue where all the rows of all the tables in a user's database were deleted. For this app, there is some security code that would do this, but it would also delete temp files and backup data files. No exceptions were thrown and no app crashes were reported.Those files were still there which indicated that the service for wiping the db didn't run, but I wasn't able to duplicate it and there were no other instances of this happening.

The other day I was running some backend tests and after the test run, went to restore the previous version of the DB to rerun the test and got this error : android.database.sqlite.SQLiteDatabaseCorruptException



12-29 10:22:39.190: D/(14156): Here
12-29 10:22:39.200: I/SqliteDatabaseCpp(14156): sqlite returned: error code = 11, msg = database corruption at line 46978 of [8609a15dfa], db=/data/data/com.myapp.android/databases/db_file
12-29 10:22:39.200: I/SqliteDatabaseCpp(14156): sqlite returned: error code = 11, msg = database corruption at line 46978 of [8609a15dfa], db=/data/data/com.myapp.android/databases/db_file

/data/data/com.myapp.android/databases/db_file
12-29 10:22:39.200: E/DefaultDatabaseErrorHandler(14156): Corruption reported by sqlite on database: /data/data/com.myapp.android/databases/db_file
12-29 10:22:39.200: E/DefaultDatabaseErrorHandler(14156): deleting the database file: /data/data/com.myapp.android/databases/db_file

12-29 10:22:39.220: E/SQLiteDatabase(14156): android.database.sqlite.SQLiteDatabaseCorruptException: error code 11: database disk image is malformed
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.database.sqlite.SQLiteStatement.native_executeInsert(Native Method)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:112)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1737)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1610)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at com.myapp.android.database.DBHelper.createRecord(DBHelper.java:80)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at com.myapp.android.contentproviders.SynchLogProvider.insert(SynchLogProvider.java:117)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.content.ContentProvider$Transport.insert(ContentProvider.java:203)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.content.ContentProviderNative.onTransact(ContentProviderNative.java:153)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.os.Binder.execTransact(Binder.java:339)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at dalvik.system.NativeStart.run(Native Method)
12-29 10:22:39.220: E/DatabaseUtils(14156): Writing exception to parcel

The database was completely wiped. Turns out that if a Sqlite database is corrupted, Android automatically deletes the database file and recreates it. This is a known Android bug/feature.

I didn't get this error until I tried to restore the db backup file and Android even thought that a desktop backup of the file was corrupted (although I was able to open it up in a third party tool on the desktop.) Likely the cause was the backup (simply a file copy) occurred while a service was updating the db.  We use the backup all the time and this was the first time that it had occurred. (The user issue was caused by something else corrupting the db file not the backup)

http://code.google.com/p/android/issues/detail?id=10127

http://stackoverflow.com/questions/2960015/android-database-disk-image-is-malformed

http://www.sqlite.org/lockingv3.html#how_to_corrupt

Solution appears to be to have a backup and restore routine so that data can be recovered if necessary.