Android upgrade db not updating db version after successful upgrade

Question

I am trying to add three new table to my existing sqlite db and I'm running into problems with the db version not updating after a successful upgrade. Below is the DatabaseHelper that runs:

private static class DatabaseHelper extends SQLiteOpenHelper {

    public DatabaseHelper(Context context, String name, CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.w(TAG, "Current db version is " + db.getVersion());
        db.execSQL(ORIGINAL_DATABASE_CREATE);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Old Version " + oldVersion + " New Version " + newVersion + " db.getVersion is " + db.getVersion());
        db.execSQL(NEW_TABLE_1_DATABASE_CREATE);
        db.execSQL(NEW_TABLE_2_DATABASE_CREATE);
        db.execSQL(NEW_TABLE_3_DATABASE_CREATE);
        db.setVersion(newVersion);
        Log.w(TAG, "Version after onUpgrade is " + db.getVersion());
    }       
}

Below is my open() function:

public VehicleExpDbAdapter open() throws SQLException {
    mDbHelper = new DatabaseHelper(mCtx, DATABASE_NAME, null, DATABASE_VERSION);
    mDb = mDbHelper.getWritableDatabase();
    return this;
}

Below is my close() function:

public void close() {
    mDb.close();
    mDbHelper.close();
}

So what happens on the first run with a higher DATABASE_VERSION is the first onUpgrade Log reads out: Old Version 1 New Version 2 db.getVersion is 1

The second Log is: Version after onUpgrade is 2

But then when the db is accessed again after the onUpgrade was ran the version number of the DB is not updated and it runs through the onUpgrade with the first log in the onUpgrade reading: Old Version 1 New Version 2 db.getVersion is 1

Then the app crashed because it tries creating a table that is already there.

I've tried not manually setting the db version in the onUpgrade as well. This didn't work either. I've also tried updating the version number by running...

db.execSQL("PRAGMA user_version = " + newVersion);

...at the end of the onUpgrade.

EDIT:

Per Aswin Kumar's suggestion I've changed my onUpgrade to backing up my existing table and dropping all table and then recreating them. This has not fix my version issue. Below is my onUpgrade and onCreate:

@Override
    public void onCreate(SQLiteDatabase db) {
        Log.w(TAG, "Current db version is " + db.getVersion());

        db.execSQL(ORIGINAL_DATABASE_CREATE);

        db.execSQL(NEW_TABLE_1_DATABASE_CREATE);
        db.execSQL(NEW_TABLE_2_DATABASE_CREATE);
        db.execSQL(NEW_TABLE_3_DATABASE_CREATE);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        Log.w(TAG, "Old Version " + oldVersion + " New Version " + newVersion + " db.getVersion is " + db.getVersion());

        mOldDbContents = backupTables(db);

        db.execSQL("DROP TABLE IF EXISTS " + ORIGINAL_DATABASE_CREATE);
        db.execSQL("DROP TABLE IF EXISTS " + NEW_TABLE_1_DATABASE_CREATE);
        db.execSQL("DROP TABLE IF EXISTS " + NEW_TABLE_2_DATABASE_CREATE);
        db.execSQL("DROP TABLE IF EXISTS " + NEW_TABLE_3_DATABASE_CREATE);

        onCreate(db);
    }

Below are the sqlite statements that I use to create the tables:

private static final String ORIGINAL_DATABASE_CREATE = "create table " + VEHICLE_EXPENSE_TABLE_NAME + 
        " (" + KEY_ROW_ID + " integer primary key autoincrement, " + 
        KEY_UNIX_DATE + " integer, " + 
        KEY_DATE + " not null default current_date, " +
        KEY_TIME + " not null default current_time, " + 
        KEY_DESCRIPTION + " text, " + 
        KEY_START_MILE + " integer, " + 
        KEY_END_MILE + " integer, " + 
        KEY_MILES + " text, " + 
        KEY_AMOUNT + " text, " 
        + KEY_PARTY_ID + " integer)";

private static final String NEW_TABLE_1_DATABASE_CREATE = "CREATE TABLE " + PURCHASE_HISTORY_TABLE_NAME + 
        "(" + HISTORY_ORDER_ID_COL + " TEXT PRIMARY KEY, " +
        HISTORY_STATE_COL + " INTEGER, " + 
        HISTORY_PRODUCT_ID_COL + " TEXT, " + 
        HISTORY_DEVELOPER_PAYLOAD_COL + " TEXT, " + 
        HISTORY_PURCHASE_TIME_COL + " INTEGER)";

private static final String NEW_TABLE_2_DATABASE_CREATE = "CREATE TABLE " + PURCHASED_ITEMS_TABLE_NAME + 
        "(" + PURCHASED_PRODUCT_ID_COL + " TEXT PRIMARY KEY, " + 
        PURCHASED_QUANTITY_COL + " INTEGER)";

private static final String NEW_TABLE_3_DATABASE_CREATE = "CREATE TABLE " + TRIAL_LIMIT_TABLE_NAME + 
        "(" + TRIAL_PRODUCT_ID_COL + " TEXT PRIMARY KEY, " + 
        TRIAL_PRODUCT_NAME + " TEXT, " + 
        TRIAL_START_DATE + " INTEGER)";

Any help would be great appreciated.

Thank you, Kevin

score 3 · Answer 1 · answered Jul 27 '12 at 12:13

3

In onUpgrade(), You have to drop the existing tables (after migrating the data to some temporary datastructure), then create the new tables (using the old data from datastructure). If you are not, the old tables will still exist.

If this does not solve the issue, paste the exact commands you are using in db.execSQL()

answered Jul 27 '12 at 12:13

Aswin Kumar

5,158
5
34
39

There is only one table in this database that I would need to migrate before I drop the tables, Do you know a good source that could help me see how to do this? Thanks! – KevinM Jul 27 '12 at 14:18
Well it depends on what data you are storing, how many columns are there, etc. Out of the wild, one way that occurs to me is to use an array of `ContentValues`. Get a cursor of all the objects currently in the table, and put each row into a `ContentValues` object. `drop` the table, `create` the new table, and `insert` each ContentValue, bingo. – Aswin Kumar Jul 27 '12 at 15:19
That makes perfect sense. I appreciate the help. I'm going to give it a try. – KevinM Jul 27 '12 at 15:25
I've made the changes, but my versioning still isn't sticking. I've updated my original post to how I now have the onUpgrade. I've also included the sql statements. Thanks!!! – KevinM Jul 27 '12 at 18:49
just to clarify, you *are* using a higher value for DATABASE_VERSION in the second version of your application right? – Aswin Kumar Jul 27 '12 at 19:04
and here is [a link](http://stackoverflow.com/questions/3505900/sqliteopenhelper-onupgrade-confusion-android) where you can see a more efficient (though bit complicated) way to migrate your data. – Aswin Kumar Jul 27 '12 at 19:05
Yes. I'm starting with version 1 and then updating it to version 2. I'll check out that link you just posted. Thanks, – KevinM Jul 27 '12 at 19:14
I've had no luck with getting this to work. It seems like I'm doing everything right. I've dropped all the tables, I've delete the whole database, I've tried setting the version manually. The database will take all the modifications I've done, but the version number never updates, so the onUpgrade is called every time I open the db. – KevinM Jul 28 '12 at 14:32

score 1 · Accepted Answer · answered Jul 28 '12 at 18:00

Fixed my own problem. The issue was that I had created a separate DB helper class that was accessing the same DB that was was trying to upgrade. In this separate DB helper class the version number was set to 1. This app was my first Java and Android program and I setup the DB classes horribly. I've consolidated all the DB class to the one i'm now upgrading and now my DB version is updating like it should. Below are my final classes if anyone is interested.

@Override
    public void onCreate(SQLiteDatabase db) {
        Log.w(TAG, "Current db version is " + db.getVersion());

        db.execSQL(VEHICLE_EXPENSE_DATABASE_CREATE);

        db.execSQL(PURCHASE_HISTORY_DATABASE_CREATE);
        db.execSQL(PURCHASE_ITEMS_DATABASE_CREATE);
        db.execSQL(TRIAL_TIME_DATABASE_CREATE);
    }


@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        Log.w(TAG, "Old Version " + oldVersion + " New Version " + newVersion + " db.getVersion is " + db.getVersion());

        if (oldVersion < newVersion) {

            if (oldVersion == 1) {
                db = upgradeTo2(db);
            }
        }
    }

private SQLiteDatabase upgradeTo2(SQLiteDatabase db) {

        db.beginTransaction();
        try {
            db.execSQL(PURCHASE_HISTORY_DATABASE_CREATE);
            db.execSQL(PURCHASE_ITEMS_DATABASE_CREATE);
            db.execSQL(TRIAL_TIME_DATABASE_CREATE);
            db.setTransactionSuccessful();
        } catch (Exception e) {
            Log.w(TAG, "onUpgrade failed");
        } finally {
            db.endTransaction();
        }

        return db;
    }

Android upgrade db not updating db version after successful upgrade

2 Answers2

Linked