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.

Android Content Provider Limiting rows returned

To limit the number of rows returned by a content provider, try this code
Product obj = new Product();

        Cursor cursor = getContentResolver().query(
                ProviderHelper.determineURI(obj), null, null, null, " _ID DESC LIMIT 5000");

This will return the last 5000 rows in descending order.Limit needs to be last and don't forget the column name you want to descend.

Android Cursor code example

Processing cursors is a very common task that developers do when creating android apps. Here's some sample loops for processing cursors. Pay attention to log files to make sure that there are no null pointer exceptions when developing your cursor processing.

Cursor cur = dB.rawQuery("SELECT firstname, salary FROM demos " +
           "where salary>100,000 LIMIT 10", null);

if (cur != null ) {

    if  (cur.moveToFirst()) {

        do {
            String firstName = cur.getString(cur.getColumnIndex("firstname"));

            int salary = cur.getInt(cur.getColumnIndex("salary"));

            results.add("" + firstName + ",salary: " + salary);
        } while (cur.moveToNext());

    }
}
cur.close();