SQLITE_MISMATCH android data_error ai_generated true

android.database.sqlite.SQLiteException: Cannot read from cursor: column 'price' has type REAL but expected INTEGER (code 0 SQLITE_MISMATCH)

ID: android/room-cursor-mismatch-column-type

Also available as: JSON · Markdown · 中文
82%Fix Rate
87%Confidence
1Evidence
2023-09-05First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
Room 2.5.0 active — — —
Room 2.6.0 active — — —
SQLite 3.32.2 active — — —

Root Cause

Room entity defines a column as an integer type (e.g., Int or Long) in Kotlin/Java, but the actual SQLite column contains a floating-point value (REAL) due to a migration failure or direct database manipulation.

generic

中文

Room 实体在 Kotlin/Java 中将列定义为整数类型（例如 Int 或 Long），但由于迁移失败或直接数据库操作，实际的 SQLite 列包含浮点值（REAL）。

Official Documentation

https://developer.android.com/training/data-storage/room/migrating-dbversions

Workarounds

  1. 90% success Create a manual migration in your RoomDatabase class that converts the column type. For example: `val MIGRATION_1_2 = object : Migration(1, 2) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL("ALTER TABLE products ADD COLUMN price_new INTEGER") database.execSQL("UPDATE products SET price_new = CAST(price AS INTEGER)") database.execSQL("ALTER TABLE products DROP COLUMN price") database.execSQL("ALTER TABLE products RENAME COLUMN price_new TO price") } }` 
    Create a manual migration in your RoomDatabase class that converts the column type. For example: `val MIGRATION_1_2 = object : Migration(1, 2) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL("ALTER TABLE products ADD COLUMN price_new INTEGER") database.execSQL("UPDATE products SET price_new = CAST(price AS INTEGER)") database.execSQL("ALTER TABLE products DROP COLUMN price") database.execSQL("ALTER TABLE products RENAME COLUMN price_new TO price") } }`
  2. 80% success Use `@ColumnInfo(typeAffinity = ColumnInfo.REAL)` in your entity to match the actual column type, then handle the conversion in your app logic (e.g., `price.toInt()`). 
    Use `@ColumnInfo(typeAffinity = ColumnInfo.REAL)` in your entity to match the actual column type, then handle the conversion in your app logic (e.g., `price.toInt()`).

中文步骤

  1. Create a manual migration in your RoomDatabase class that converts the column type. For example: `val MIGRATION_1_2 = object : Migration(1, 2) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL("ALTER TABLE products ADD COLUMN price_new INTEGER") database.execSQL("UPDATE products SET price_new = CAST(price AS INTEGER)") database.execSQL("ALTER TABLE products DROP COLUMN price") database.execSQL("ALTER TABLE products RENAME COLUMN price_new TO price") } }`
  2. Use `@ColumnInfo(typeAffinity = ColumnInfo.REAL)` in your entity to match the actual column type, then handle the conversion in your app logic (e.g., `price.toInt()`).

Dead Ends

Common approaches that don't work:

  1. 70% fail

    This only works during development; in production, users lose all local data. The underlying schema mismatch returns when migration is reapplied.

  2. 85% fail

    Changing the entity type alone does not fix the existing data; Room still reads the column as REAL, and the migration must handle type conversion explicitly.