Room Database Migration: java.lang.IllegalStateException: Migration didn't properly handle <table_name>

Question

I am trying to create a new table using Room Database. I followed the same principles that I used for the first table that I already have in my program, but when the time comes to run the app, I get this error:

Caused by: java.lang.IllegalStateException: Migration didn't properly handle: WaterFountainEntry(com.mpms.relatorioacessibilidadecortec.entities.WaterFountainEntry).
     Expected:
    TableInfo{name='WaterFountainEntry', columns={waterFountainHeight=Column{name='waterFountainHeight', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}, waterFountainID=Column{name='waterFountainID', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, cupHolderHeight=Column{name='cupHolderHeight', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}, schoolEntryID=Column{name='schoolEntryID', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, waterFountApproximation=Column{name='waterFountApproximation', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[ForeignKey{referenceTable='SchoolEntry', onDelete='CASCADE', onUpdate='CASCADE', columnNames=[schoolEntryID], referenceColumnNames=[cadID]}], indices=[]}
     Found:
    TableInfo{name='WaterFountainEntry', columns={waterFountainID=Column{name='waterFountainID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, waterFountainHeight=Column{name='waterFountainHeight', type='DOUBLE', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}, cupHolderHeight=Column{name='cupHolderHeight', type='DOUBLE', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}, schoolEntryID=Column{name='schoolEntryID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, waterFountApproximation=Column{name='waterFountApproximation', type='DOUBLE', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[ForeignKey{referenceTable='SchoolEntry', onDelete='NO ACTION', onUpdate='NO ACTION', columnNames=[schoolEntryID], referenceColumnNames=[cadID]}], indices=[]}

I do gather, by reading this error message, that the program expected a different type from some fields (and probably a different order from them as well), but the thing is that it shouldn't be like that. I made sure to write my migration class exactly as I put the fields in my entity, as follows:

<Migration Class and Database Creation>

 static final Migration MIGRATION_2_3 = new Migration(2,3) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            database.execSQL("CREATE TABLE WaterFountainEntry (waterFountainID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                    "schoolEntryID INTEGER NOT NULL, waterFountainHeight DOUBLE NOT NULL, cupHolderHeight DOUBLE NOT NULL," +
                    "waterFountApproximation DOUBLE NOT NULL, FOREIGN KEY (schoolEntryID) REFERENCES SchoolEntry (cadID))");
        }
    };

    public static ReportDatabase getDatabase(final Context context) {
        if (INSTANCE == null){
            synchronized (ReportDatabase.class) {
                if (INSTANCE == null) {
                    INSTANCE = Room.databaseBuilder(context.getApplicationContext(), ReportDatabase.class, "ReportDatabase")
                            .addCallback(roomCallback).addMigrations(MIGRATION_2_3).build();
                }
            }
        }
        return INSTANCE;
    }

<WaterFountainEntry entity>

@Entity(foreignKeys = @ForeignKey(entity = SchoolEntry.class, parentColumns = "cadID", childColumns = "schoolEntryID",
        onDelete = CASCADE, onUpdate = CASCADE))
public class WaterFountainEntry {

    @PrimaryKey(autoGenerate = true)
    @NonNull
    private Integer waterFountainID;
    @NonNull
    private Integer schoolEntryID;
    @NonNull
    private Double waterFountainHeight;
    @NonNull
    private Double cupHolderHeight;
    @NonNull
    private Double waterFountApproximation;

/** getters & setters **/

    public WaterFountainEntry(@NonNull Integer schoolEntryID, @NonNull Double waterFountainHeight,
                              @NonNull Double cupHolderHeight, @NonNull Double waterFountApproximation) {
        this.schoolEntryID = schoolEntryID;
        this.waterFountainHeight = waterFountainHeight;
        this.cupHolderHeight = cupHolderHeight;
        this.waterFountApproximation = waterFountApproximation;
    }

So, what could be the error? I really read a lot of documentations and can't find the cause of this problem.

Answer 1

The simple trick to getting the correct SQL is to let Room generate the SQL on your behalf.

Create the Entity(s) then compile (Cntrl+F9) then look in the java generated (from the Android view) and then look for the file that is the same as the @Database class suffixed with _Impl and then find the createAllTables method and the SQL is there. It will be exactly what Room will expect.

e.g. :-

Answer 2

There are many differences between the expected & found SQL scripts; taking cupHolderHeight column for instance:

cupHolderHeight column:

• Expected: REAL found: DOUBLE --> Change it to REAL (Basically there is no DOUBLE type in SQLite)
• Expected notNull=false, found notNull=true --> Change it to false (i.e. NULL)

Applying that to your migration SQL script:

Change cupHolderHeight DOUBLE NOT NULL into cupHolderHeight REAL

You need to go through other columns and do the same. Eventually the script should be like:

database.execSQL("CREATE TABLE WaterFountainEntry (waterFountainID INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "schoolEntryID INTEGER, waterFountainHeight REAL, cupHolderHeight REAL," +
                    "waterFountApproximation REAL, FOREIGN KEY (schoolEntryID) REFERENCES SchoolEntry (cadID) ON UPDATE CASCADE ON DELETE CASCADE)");

You can also consider this answer for more illustration.

For arranging and comparing both found & expected scripts:

You can sort them with a programming script, but Here is a way for sorting them using some tools:

1. Copy the first one (Expected) into a Text Editor:

TableInfo{name='WaterFountainEntry', columns={waterFountainHeight=Column{name='waterFountainHeight', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}, waterFountainID=Column{name='waterFountainID', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, cupHolderHeight=Column{name='cupHolderHeight', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}, schoolEntryID=Column{name='schoolEntryID', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, waterFountApproximation=Column{name='waterFountApproximation', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[ForeignKey{referenceTable='SchoolEntry', onDelete='CASCADE', onUpdate='CASCADE', columnNames=[schoolEntryID], referenceColumnNames=[cadID]}], indices=[]}

2. Replace }, with },\n --> You've to know how to add line break in your text editor:

TableInfo{name='WaterFountainEntry', columns={waterFountainHeight=Column{name='waterFountainHeight', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'},
waterFountainID=Column{name='waterFountainID', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'},
cupHolderHeight=Column{name='cupHolderHeight', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'},
schoolEntryID=Column{name='schoolEntryID', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'},
waterFountApproximation=Column{name='waterFountApproximation', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}},
foreignKeys=[ForeignKey{referenceTable='SchoolEntry', onDelete='CASCADE', onUpdate='CASCADE', columnNames=[schoolEntryID], referenceColumnNames=[cadID]}], indices=[]}

Now you'll have each column in a separate row:

3. Do 1 & 2 for the other one (Found):

TableInfo{name='WaterFountainEntry', columns={waterFountainID=Column{name='waterFountainID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'},
waterFountainHeight=Column{name='waterFountainHeight', type='DOUBLE', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'},
cupHolderHeight=Column{name='cupHolderHeight', type='DOUBLE', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'},
schoolEntryID=Column{name='schoolEntryID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'},
waterFountApproximation=Column{name='waterFountApproximation', type='DOUBLE', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}},
foreignKeys=[ForeignKey{referenceTable='SchoolEntry', onDelete='NO ACTION', onUpdate='NO ACTION', columnNames=[schoolEntryID], referenceColumnNames=[cadID]}], indices=[]}

4. Now it's easy to sorting them alphabetically and compare one value to another, (Excel can be a tool for this).

Answer 3

The Green column is what your migration should look like.

Please check the highlighted fields and make the changes similar to green as that's your expected result.

Here is the link to diff checker for comparison

Just compare the found and expected result you will know what changes you have to do in your migration

 Expected:
    TableInfo{name='WaterFountainEntry', columns={waterFountainHeight=Column{name='waterFountainHeight', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}, waterFountainID=Column{name='waterFountainID', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, cupHolderHeight=Column{name='cupHolderHeight', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}, schoolEntryID=Column{name='schoolEntryID', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, waterFountApproximation=Column{name='waterFountApproximation', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[ForeignKey{referenceTable='SchoolEntry', onDelete='CASCADE', onUpdate='CASCADE', columnNames=[schoolEntryID], referenceColumnNames=[cadID]}], indices=[]}
     Found:
    TableInfo{name='WaterFountainEntry', columns={waterFountainID=Column{name='waterFountainID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, waterFountainHeight=Column{name='waterFountainHeight', type='DOUBLE', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}, cupHolderHeight=Column{name='cupHolderHeight', type='DOUBLE', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}, schoolEntryID=Column{name='schoolEntryID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, waterFountApproximation=Column{name='waterFountApproximation', type='DOUBLE', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[ForeignKey{referenceTable='SchoolEntry', onDelete='NO ACTION', onUpdate='NO ACTION', columnNames=[schoolEntryID], referenceColumnNames=[cadID]}], indices=[]}