Room Database Migration Failed: ALTER TABLE to add multiple columns

Question

I'm upgrading my Database from version 3 to version 4 by providing migration from 3 to 4.

Here's my code for migration:

private static Migration MIGRATION_3_4 = new Migration(3, 4) {
    @Override
    public void migrate(@NonNull SupportSQLiteDatabase database) {
      database.execSQL("ALTER TABLE caption_table ADD COLUMN localVideoUrl TEXT;");
      database.execSQL("ALTER TABLE caption_table ADD COLUMN postType TEXT");
      database.execSQL("ALTER TABLE caption_table ADD COLUMN videoUrl TEXT");
    }
};

Here's the code which create room database

this.mAppDataBase = Room.databaseBuilder(getApplicationContext(), AppDataBase.class, "my_db")
                        .addMigrations(MIGRATION_2_3, MIGRATION_3_4)
                        .build();

Here's the piece of code that I have added on my PostModel

@Expose
private String postType;

@Expose
private String videoUrl;

@Expose
private String localVideoUrl;

public String getPostType() {
    return postType;
}

public void setPostType(String postType) {
    this.postType = postType;
}

public String getVideoUrl() {
    return videoUrl;
}

public void setVideoUrl(String videoUrl) {
    this.videoUrl = videoUrl;
}

public String getLocalVideoUrl() {
    return localVideoUrl;
}

public void setLocalVideoUrl(String localVideoUrl) {
    this.localVideoUrl = localVideoUrl;
}

And below is the error I'm getting. The error is not related to the notNull property of room entity.

java.lang.IllegalStateException: Migration didn't properly handle posts(com.myapp.Database.PostModel).

Expected: TableInfo{name='posts', columns={imageWidth=Column{name='imageWidth', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, localVideoUrl=Column{name='localVideoUrl', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, authorImageLocalUrl=Column{name='authorImageLocalUrl', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, videoUrl=Column{name='videoUrl', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, imageLocalUrl=Column{name='imageLocalUrl', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, postType=Column{name='postType', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, authorName=Column{name='authorName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, imageUrl=Column{name='imageUrl', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1}, title=Column{name='title', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, authorImageUrl=Column{name='authorImageUrl', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, imageHeight=Column{name='imageHeight', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}}, foreignKeys=[], indices=[]}

Found: TableInfo{name='posts', columns={imageWidth=Column{name='imageWidth', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, authorImageLocalUrl=Column{name='authorImageLocalUrl', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, imageLocalUrl=Column{name='imageLocalUrl', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, authorName=Column{name='authorName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, imageUrl=Column{name='imageUrl', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1}, title=Column{name='title', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, authorImageUrl=Column{name='authorImageUrl', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, imageHeight=Column{name='imageHeight', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}}, foreignKeys=[], indices=[]}

Answer 1

Comparing the Expected vs. Found JSON that it's in your log, clearly, the error happens because the Table that was found doesn't have the 3 new columns you intended to add: postType, videoUrl and localVideoUrl. (You can use this script to compare both JSON objects from the log)

The problem might be that in your migration code you are adding the new columns to a table named caption_table, whereas according to the log the table that fails is called posts. Try adjusting your migration code to add the new columns to the proper table.