Ryan Ryan - 3 days ago 5
Android Question

Android SQLite - drop table after failed transaction during upgrade

The drop table is not doing anything after a failed transaction during an SQLite upgrade. The drop SQL doesn't throw an exception.

The only way I can get this to work is remove everything to do with transactions, but I want the database to be unchanged to analyse what went wrong during the upgrade.

private void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
try {
db.beginTransaction();
// Exception occurs here
db.setTransactionSuccessful();
db.endTransaction();
}catch(Exception e){
db.endTransaction(); // Transaction is unsuccessful and changes rolled back
// Save for analyses
db.execSQL("DROP TABLE ..."); // Drop table does nothing
}
}


SOLUTION:
onUpgrade
is already in a transaction block. To make this work:

// Path to db on local storage
private String mDbToUpload = null;

@Override
private void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
try {
// Exception occurs here
}catch(Exception e){
db.endTransaction(); // End transaction to rollback changes
mDbToUpload = ...// Save db to local storage
}
}

@Override
private void onOpen(SQLiteDatabase db){
if(mDbToUpload != null){
... // Upload db from path
mDbToUpload = null;
}
}

Answer

SQLiteOpenHelper lifecycle methods such as onUpgrade() are executed in a transaction. sqlite does not support nested transactions, and Android's SQLiteDatabase only emulates nested transactions with reference counting but such emulated nested transaction is not in fact a real transaction.

If you have a problem in a lifecycle method, throw an exception and the framework will roll back the outermost, actual transaction for you.

Comments