Room Migration Error - NO SUCH COLUMN EXISTS

Question

I have written a room migration code which crashes due to the following error:

This is the error that I'm getting : no such column: LocationID (code 1): , while compiling: CREATE TABLE IF NOT EXISTS LocationData (Latitude INTEGER, Longitude INTEGER, Accuracy INTEGER, DateTime TEXT, status TEXT NOT NULL, TechUserID INTEGER, Speed INTEGER, AndroidVersion TEXT, AppVersion TEXT, PRIMARY KEY(LocationID))

This is the migration code that I have written

private val MIGRATION_13_14 = object : Migration(13, 14) {
            override fun migrate(database: SupportSQLiteDatabase) {
                database.execSQL(
                    "CREATE TABLE IF NOT EXISTS surveyReasonField (id INTEGER NOT NULL, Reason TEXT, ReasonID INTEGER, IsAccountNumberRequired INTEGER, IsMeterSerialNumberRequired INTEGER, IsCTRatioRequired INTEGER, IsMeterTypeRequired INTEGER, IsGPSCoordinateRequired INTEGER, IsCustomerNameRequired INTEGER, IsBuildingNameandNumberRequired INTEGER, IsStreetNumberRequired INTEGER, IsInstallLocationRequired INTEGER, IsBuildingTypeRequired INTEGER, IsSignalAvailabilityRequired INTEGER, IsTypeofAntennaRequired INTEGER, IsMeterPanelConditionRequired INTEGER, IsMeterPanelLockStatusRequired INTEGER, IsSafeToWorkRequired INTEGER, IsCutoutConditionRequired INTEGER, IsCableConditionRequired INTEGER, IsCustomerContactDetailsRequired INTEGER, IsReadinessofInstallationRequired INTEGER, IsMeterPanelNumberRequired INTEGER, IsMeterDataImageRequired INTEGER, IsMeterPanelImageRequired INTEGER, IsCutOutImageRequired INTEGER, IsCableConditionImageRequired INTEGER, IsCustomerEmailRequired INTEGER, IsSignalTypeRequired INTEGER, PRIMARY KEY(`id`))"
                )
                database.execSQL(
                    "ALTER TABLE settingEntity ADD COLUMN NoOfDaysForImageDeletion INTEGER"
                )
                database.execSQL(
                    "CREATE TABLE IF NOT EXISTS LocationData (`Latitude` INTEGER, `Longitude` INTEGER, `Accuracy` INTEGER, `DateTime` TEXT, `status` TEXT NOT NULL, `TechUserID` INTEGER, `Speed` INTEGER, `AndroidVersion` TEXT, `AppVersion` TEXT, PRIMARY KEY(`LocationID`))"
                )
                database.execSQL(
                    "ALTER TABLE settingEntity ADD COLUMN IsLiveTrackingEnabled INTEGER"
                )
            }
        }

The correct statement for "LocationData" should be

database.execSQL(
    "CREATE TABLE IF NOT EXISTS LocationData (`LocationID` INTEGER NOT NULL,`Latitude` INTEGER NOT NULL, `Longitude` INTEGER NOT NULL, `Accuracy` INTEGER NOT NULL, `DateTime` TEXT NOT NULL, `status` TEXT, `TechUserID` INTEGER NOT NULL, `Speed` INTEGER NOT NULL, `AndroidVersion` TEXT NOT NULL, `AppVersion` TEXT NOT NULL, PRIMARY KEY(`LocationID`))"
)

My doubt is what should I do so that the app will not crash next time. Should I use "fallbackToDestructiveMigrationFrom()" or "fallbackToDestructiveMigrationOnDowngrade()". My objective is to stop the app from crashing and any method given here or from anywhere is fine as long the existing data is not losed.

Answer 1

What you need to do is ensure that the Migration code is correct. That is that it creates tables and alters them in-line with the tables that are expected.

The actual issue you encountered was an attempt to specify the LocationID column as a primary key when the column itself was not defined.

Perhaps the simplest way to handle a new table or in the case of the ALTER TABLE statements for what the new column definitions should be is to refer to the SQL that Room itself would use and to then copy the relevant SQL.

This SQL is available, after successfully compiling the project, in a function name createALLTables that is in the generated java (visible via Android Studio's Android view under java(generated) in the class that is the same name as the class (or classes) that is (are) annotated with @Database but is suffixed with _Impl.

• NOTE the project should be compiled after all @Entity annotated classes have been changed or created AND that the classes have been specified in the entities parameter of the @Database annotation.

If you invoke any of the fallbackToDestructiveMigration.... functions, then the entire database will effectively be deleted and all existing data will be lost.

AS an example here's a project that has been compiled and that indicates how/where the respective expected SQL exists:-

• The centre pane/window shows the @Database annotated class named in this case TheDatabase.
• The Left pane/window shows the project explorer as per the Android View (topmost highlighted) with the java(generated) directory expanded and the TheDatabase_Impl class highlighted. With a arrow from the centre pane/window.
• The Right pane/window is the opened TheDatabase_Impl class with the createAllTables function highlighted and along with the SQL for the CREATE TABLE statments, according to Room's interpretation of the @Entity annotated classes. i.e. the table definitions according to Room' expectation. The respective SQL or part thereof can then be copied and pasted.

Note when using ALTER TABLE statements then you have to comply with SQLite's rules/restrictions and consider compatibility and thus you may wish to reference https://www.sqlite.org/lang_altertable.html and in regards to what SQLite version https://developer.android.com/reference/android/database/sqlite/package-summary.html