CODEWITHSUNDEEP
android-studioandroid-room
Michel
Michel

Reputation: 111

Android studio Room database migration error - table scrambled


I am working on my first migration of a Room database version. For this i have choosen the "manual" migration. In my table i added 1 field/column at the end and an index.
Then in my migration function i added this

private val migration2To3: Migration = object: Migration(2,3) {
        override fun migrate(database: SupportSQLiteDatabase) {

            Log.i("database", "migration from $startVersion to $endVersion started")

            database.execSQL("ALTER TABLE 'gps' ADD COLUMN 'isTrack' BOOLEAN NOT NULL DEFAULT false")
            Log.i("database", "TABLE gps altered. 1 new column added")

            database.execSQL("CREATE INDEX IF NOT EXISTS `index_gps_tripId` ON `gps` (`tripId`)")
            Log.i("database", "TABLE gps index on 'tripId' added")

            Log.i("database", "migration from $startVersion to $endVersion ended")
        }
 }

Now i see the logging lines which let me to believe al was ok but a second later my app crashes with this message:

E/AndroidRuntime: FATAL EXCEPTION: arch_disk_io_0 Process: com.example.tripmemory, PID: 15190 java.lang.RuntimeException: Exception while computing database live data. at androidx.room.RoomTrackingLiveData.refreshRunnable$lambda$0(RoomTrackingLiveData.kt:74) at androidx.room.RoomTrackingLiveData.$r8$lambda$PhMGW5zFk4QWazERd2lfEeLZqW0(Unknown Source:0) at androidx.room.RoomTrackingLiveData$$ExternalSyntheticLambda0.run(Unknown Source:2) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1137) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:637) at java.lang.Thread.run(Thread.java:1012) Caused by: java.lang.IllegalStateException: Migration didn't properly handle: gps(com.example.tripmemory.objects.GPSDataObject). Expected: TableInfo{name='gps', columns={altitude=Column{name='altitude', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, isTrack=Column{name='isTrack', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, datetime=Column{name='datetime', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, latitude=Column{name='latitude', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, icon=Column{name='icon', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, description=Column{name='description', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, tripId=Column{name='tripId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, memoryId=Column{name='memoryId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, longitude=Column{name='longitude', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}}, foreignKeys=[ForeignKey{referenceTable='memory', onDelete='CASCADE +', onUpdate='NO ACTION', columnNames=[memoryId], referenceColumnNames=[id]}, ForeignKey{referenceTable='trip', onDelete='CASCADE +', onUpdate='NO ACTION', columnNames=[tripId], referenceColumnNames=[id]}], indices=[Index{name='index_gps_memoryId', unique=false, columns=[memoryId], orders=[ASC]'}, Index{name='index_gps_tripId', unique=false, columns=[tripId], orders=[ASC]'}]} Found: E/AndroidRuntime: TableInfo{name='gps', columns={id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, tripId=Column{name='tripId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, memoryId=Column{name='memoryId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, longitude=Column{name='longitude', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, latitude=Column{name='latitude', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, altitude=Column{name='altitude', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, datetime=Column{name='datetime', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, description=Column{name='description', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, icon=Column{name='icon', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, isTrack=Column{name='isTrack', type='BOOLEAN', affinity='1', notNull=true, primaryKeyPosition=0, defaultValue='false'}}, foreignKeys=[ForeignKey{referenceTable='memory', onDelete='CASCADE +', onUpdate='NO ACTION', columnNames=[memoryId], referenceColumnNames=[id]}], indices=[Index{name='index_gps_tripId', unique=false, columns=[tripId], orders=[ASC]'}, Index{name='index_gps_memoryId', unique=false, columns=[memoryId], orders=[ASC]'}]} at androidx.room.RoomOpenHelper.onUpgrade(RoomOpenHelper.kt:94) at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.onUpgrade(FrameworkSQLiteOpenHelper.kt:252) at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:416) at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:316) at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.getWritableOrReadableDatabase(FrameworkSQLiteOpenHelper.kt:231) at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.innerGetDatabase(FrameworkSQLiteOpenHelper.kt:189) at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.getSupportDatabase(FrameworkSQLiteOpenHelper.kt:151) at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper.getWritableDatabase(FrameworkSQLiteOpenHelper.kt:104) at androidx.room.RoomDatabase.inTransaction(RoomDatabase.kt:629) at androidx.room.RoomDatabase.assertNotSuspendingTransaction(RoomDatabase.kt:448) at androidx.room.RoomDatabase.query(RoomDatabase.kt:477) at androidx.room.util.DBUtil.query(DBUtil.kt:75) at com.example.tripmemory.dataabase.TripMemoryDao_Impl$23.call(TripMemoryDao_Impl.java:493) at com.example.tripmemory.dataabase.TripMemoryDao_Impl$23.call(TripMemoryDao_Impl.java:490) at androidx.room.RoomTrackingLiveData.refreshRunnable$lambda$0(RoomTrackingLiveData.kt:72) ... 5 more


What is strange is column order of the expected table is completly scrambled. I did not change anything in the order of the fields. when i look at the schema export of the two version (before and after) it also there has the same field order in both version just with the new extra field at the end in the newest version.
So what am i doing wrong. Only thing i can think of to fix it is to use "fallbackToDestructiveMigration" which is an option for now as the app is not live but when it is that is not an option.

Upvotes: 1

Views: 972

Answers (1)

MikeT
MikeT

Reputation: 57043

What is strange is column order of the expected table is completly scrambled.

The order of the columns is irrelevant. Your issue is the use of the column type BOOLEAN and also the use of a DEFAULT value.

The type should instead be INTEGER.

  • Room only supports column types that are one of:-
    • INTEGER, REAL, TEXT or BLOB

So you want to use

database.execSQL("ALTER TABLE 'gps' ADD COLUMN 'isTrack' INTEGER NOT NULL DEFAULT false")

However, you then have the issue that if the NOT NULL constraint is used, you MUST have a default value when using the ADD COLUMN. To indicate to Room that you are using a DEFAULT value then you need to use defaultValue parameter of the @ColumnInfo annotation for the member variable/field specifying false as the value.

The issues can be seen by looking at the isTrack column in the expected and found. The result showing a difference in the affinity, type and default value. As per:-

isTrack=Column{name='isTrack', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'},

isTrack=Column{name='isTrack', type='BOOLEAN', affinity='1', notNull=true, primaryKeyPosition=0, defaultValue='false'

Upvotes: 0

Related Questions