Add a column to existing room database whilst using the current auto-migration feature available in-CodePudding

I have a room database containing data, and now i want to add another column to it.

Room version 2.4.0-alpha01 and above was supposed to make auto-migrations easier, so i used it like this:

...
version = 2,
autoMigrations = {
        @AutoMigration(from = 1, to = 2)
        }, 
exportSchema = true

Then in my model class, i added the new column name and generated its setters and getters, just like the others.

The room documentation says that if room cannot perform the migration due to complex schema changes, a compile time error is thrown. In my case, however, I get a runtime error about the discrepancy between the expected schema and the new schema (the one i added a column to).

Below is the error:

java.lang.RuntimeException: Exception while computing database live data.
        at androidx.room.RoomTrackingLiveData$1.run(RoomTrackingLiveData.java:92)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
        at java.lang.Thread.run(Thread.java:919)
     Caused by: java.lang.IllegalStateException: Migration didn't properly handle: accounts(com.bisform.susu.models.Account).
     Expected:
    TableInfo{name='accounts', columns={accountSavings=Column{name='accountSavings', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountName=Column{name='accountName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountCreateTime=Column{name='accountCreateTime', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, numberOfDeposits=Column{name='numberOfDeposits', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, accountNumber=Column{name='accountNumber', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, numberOfWithdrawals=Column{name='numberOfWithdrawals', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, accountPayoutDate=Column{name='accountPayoutDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, phoneOfNextOfKin=Column{name='phoneOfNextOfKin', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountImageURI=Column{name='accountImageURI', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountID=Column{name='accountID', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, lastUpdatedDate=Column{name='lastUpdatedDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, phoneNumber=Column{name='phoneNumber', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountPayoutTime=Column{name='accountPayoutTime', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, lastUpdatedTime=Column{name='lastUpdatedTime', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, nameOfNextOfKin=Column{name='nameOfNextOfKin', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountCreateDate=Column{name='accountCreateDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isPaidOut=Column{name='isPaidOut', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
E/AndroidRuntime: TableInfo{name='accounts', columns={accountSavings=Column{name='accountSavings', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountName=Column{name='accountName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountCreateTime=Column{name='accountCreateTime', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, numberOfDeposits=Column{name='numberOfDeposits', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, accountNumber=Column{name='accountNumber', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, numberOfWithdrawals=Column{name='numberOfWithdrawals', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, accountPayoutDate=Column{name='accountPayoutDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountImageURI=Column{name='accountImageURI', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountID=Column{name='accountID', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, lastUpdatedDate=Column{name='lastUpdatedDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, phoneNumber=Column{name='phoneNumber', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountPayoutTime=Column{name='accountPayoutTime', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, lastUpdatedTime=Column{name='lastUpdatedTime', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountCreateDate=Column{name='accountCreateDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isPaidOut=Column{name='isPaidOut', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
        at androidx.room.RoomOpenHelper.onUpgrade(RoomOpenHelper.java:103)
...

Please what should I do to handle this error? How do I get room to migrate properly using the auto-migrate feature currently present?

CodePudding user response：

Please what should I do to handle this error? How do I get room to migrate properly using the auto-migrate feature currently present?

I believe you would only get this if you are somehow omitting the Auto Migration. With the code you have shown the only way, I believe, would be to include a manual migration as this would then override the Auto Migration.

Testing, as below, shows this.

Testing

This testing shows the effect of overriding the AutoMigration from a V1 to V2 and also how the migration works when not overridden.

The test is based upon an Accounts class built according to your expected (v2) and found (v1) messages.

So the Accounts class is :-

@Entity(tableName = "accounts")
class Accounts {

    //String phoneOfNextOfKin; //<<<<< For  V2 else commented out
    //String nameOfNextOfKin; //<<<<< for V2 else commented out

    String accountSavings;
    String accountName;
    String accountCreateTime;
    Integer numberOfDeposits;
    String accountNumber;
    int numberOfWithdrawals;
    String accountPayoutDate;
    String accountImageURI;
    @PrimaryKey
    Long accountId;
    String lastUpdatedDate;
    String phoneNumber;
    String accountPayoutTime;
    String lastUpdatedTime;
    String accountCreateDate;
    boolean isPaidOut;
}

Noting that the lines with the comment //<<<<< ...., are lines that are changed (commented out for V1 and included for V2)
i.e. the 2 columns phoneOfNextOfKin and nameOfNextOfKin are added for V2

An @Dao class that doesn't change between versions :-

@Dao
abstract class AccountsDao {
    @Insert
    abstract long insert(Accounts accounts);
    @Query("SELECT * FROM accounts")
    abstract List<Accounts> getAllFromAccounts();
}

An @Database class :-

@Database(
        entities = {Accounts.class},
        version = TheDatabase.DBVERSION
        /* following line, if no schema saved, needs to be commented out */
        , autoMigrations = {@AutoMigration(from = 1, to = 2)} 
)
abstract class TheDatabase extends RoomDatabase {
    abstract AccountsDao getAccountsDao();

    public static final int DBVERSION = 1; //<<<<< change accordingly

    private static volatile TheDatabase instance = null;

    static TheDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(
                    context,
                    TheDatabase.class,
                    "accounts.db"
            )
                    .allowMainThreadQueries() //run on main thread for brevity and convenience
                    .addMigrations(MIGRATION_1_2) //<<<<< if included then Migration didn't properly handle:
                    .build();
        }
        return instance;
    }

    /* Only needed for creating error shown in question */
    /* doesn't hurt if no addMigrations or when creating V1 */
    static final Migration MIGRATION_1_2 = new Migration(1,2) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {

        }
    };
}

Lastly the following activity :-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AccountsDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = TheDatabase.getInstance(this);
        dao = db.getAccountsDao();

        Accounts a = new Accounts();
        a.accountCreateDate = "2021-01-01";
        a.accountCreateTime = "08:00:00";
        a.accountImageURI = "imageURI";
        a.accountName = "MyAccount";
        a.accountNumber = "0000000000";
        a.accountPayoutDate = "2021-05-05";
        a.accountPayoutTime = "10:00:00";
        a.accountSavings = "savings";
        a.lastUpdatedDate = "2021-05-03";
        a.lastUpdatedTime = "09:15:00";
        a.numberOfDeposits = 10;
        a.numberOfWithdrawals = 5;
        a.isPaidOut = false;

        //a.nameOfNextOfKin = "Mr Next of Kin"; //<<<<< For V2
        //a.phoneOfNextOfKin = "1111111111"; //<<<<< For V2

        dao.insert(a);
        for(Accounts account: dao.getAllFromAccounts()) {
            String msg = "Account is "   account.accountName   " ID is "   account.accountId;
            if (TheDatabase.DBVERSION > 1) {
                //msg = msg   " next of kin is "   account.nameOfNextOfKin; //<<<<< For V2
            }
            Log.d("ACCOUNTINFO", msg);
        }
    }
}

The Runs and Results:-

With the code (no V2 code included) as above and with the App not installed. i.e. Create V1 database:-

The App runs successfully and Database Inspector shows :-
Schema is as expected as is the single row.

Run again without any changes

The App runs successfully and Database Inspector shows :-

3. Changes made to go to V2 notably:-

in Accounts name and phone of nextof kin included.
in TheDatabase DBVERSION changed from 1 to 2 and .addMigrations left included
in the invoking activity the name and next of kin values are assigned values rather being commented out. the msg variable is concatenated to include the next of kin.
The App crashes with

Caused by: java.lang.IllegalStateException: Migration didn't properly handle: accounts(a.a.so69442030javaroomautomigrationsaddcolumns.Accounts).
     Expected:
2021-10-05 14:31:50.669 30268-30268/a.a.so69442030javaroomautomigrationsaddcolumns E/AndroidRuntime: TableInfo{name='accounts', columns={accountSavings=Column{name='accountSavings', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountName=Column{name='accountName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountCreateTime=Column{name='accountCreateTime', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, numberOfDeposits=Column{name='numberOfDeposits', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountNumber=Column{name='accountNumber', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, numberOfWithdrawals=Column{name='numberOfWithdrawals', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, phoneOfNextOfKin=Column{name='phoneOfNextOfKin', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountPayoutDate=Column{name='accountPayoutDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountImageURI=Column{name='accountImageURI', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountId=Column{name='accountId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, lastUpdatedDate=Column{name='lastUpdatedDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, phoneNumber=Column{name='phoneNumber', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountPayoutTime=Column{name='accountPayoutTime', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, nameOfNextOfKin=Column{name='nameOfNextOfKin', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, lastUpdatedTime=Column{name='lastUpdatedTime', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountCreateDate=Column{name='accountCreateDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isPaidOut=Column{name='isPaidOut', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
2021-10-05 14:31:50.670 30268-30268/a.a.so69442030javaroomautomigrationsaddcolumns E/AndroidRuntime: TableInfo{name='accounts', columns={accountSavings=Column{name='accountSavings', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountName=Column{name='accountName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountCreateTime=Column{name='accountCreateTime', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, numberOfDeposits=Column{name='numberOfDeposits', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountNumber=Column{name='accountNumber', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, numberOfWithdrawals=Column{name='numberOfWithdrawals', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, accountPayoutDate=Column{name='accountPayoutDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountImageURI=Column{name='accountImageURI', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountId=Column{name='accountId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, lastUpdatedDate=Column{name='lastUpdatedDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, phoneNumber=Column{name='phoneNumber', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountPayoutTime=Column{name='accountPayoutTime', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, lastUpdatedTime=Column{name='lastUpdatedTime', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, accountCreateDate=Column{name='accountCreateDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isPaidOut=Column{name='isPaidOut', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
        at androidx.room.RoomOpenHelper.onUpgrade(RoomOpenHelper.java:103)

i.e. replicating the failure encountered

Steps 1-2 were rerun after uninstalling the App and using the code as per V1 (to demonstrate the entire process from scratch)(rerunning with the .addMigrations commented out or removed would overcome the issue)
The changes as per step 3 were applied BUT ADDITONALLY the .addMigrations line in the database build was commented out.

The App does not crash. Database inspector shows the amended schema and appropriate data in the newly inserted row (the other 2 have nulls for the two added columns) :-

CodePudding user response：

All that it took to fix the above problem was cleaning the project and rebuilding. I suppose the old schema was somehow still in memory, hence the crashes.