android data_error ai_generated true

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

ID: android/room-migration-ambiguous-column-type

Also available as: JSON · Markdown · 中文
87%Fix Rate
88%Confidence
1Evidence
2024-01-10First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
Room 2.6.1 active — — —
Android 12 (API 31) active — — —
Android 11 (API 30) active — — —
Room 2.5.2 active — — —

Root Cause

Room migration script altered the schema without converting existing data types, causing a mismatch between the expected SQLite affinity (INTEGER) and the actual stored type (TEXT) in the column.

generic

中文

Room迁移脚本更改了架构但未转换现有数据类型，导致预期SQLite亲和类型（INTEGER）与列中实际存储类型（TEXT）不匹配。

Official Documentation

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

Workarounds

  1. 90% success Write a manual migration that converts TEXT to INTEGER using CAST. Example in migration: ALTER TABLE my_table ADD COLUMN timestamp_temp INTEGER; UPDATE my_table SET timestamp_temp = CAST(timestamp AS INTEGER); ALTER TABLE my_table DROP COLUMN timestamp; ALTER TABLE my_table RENAME COLUMN timestamp_temp TO timestamp; 
    Write a manual migration that converts TEXT to INTEGER using CAST. Example in migration: ALTER TABLE my_table ADD COLUMN timestamp_temp INTEGER; UPDATE my_table SET timestamp_temp = CAST(timestamp AS INTEGER); ALTER TABLE my_table DROP COLUMN timestamp; ALTER TABLE my_table RENAME COLUMN timestamp_temp TO timestamp;
  2. 85% success Use @TypeConverter to handle both types in the DAO. Example: @TypeConverter public static Long fromTimestamp(String value) { return value == null ? null : Long.parseLong(value); } 
    Use @TypeConverter to handle both types in the DAO. Example: @TypeConverter public static Long fromTimestamp(String value) { return value == null ? null : Long.parseLong(value); }

中文步骤

  1. Write a manual migration that converts TEXT to INTEGER using CAST. Example in migration: ALTER TABLE my_table ADD COLUMN timestamp_temp INTEGER; UPDATE my_table SET timestamp_temp = CAST(timestamp AS INTEGER); ALTER TABLE my_table DROP COLUMN timestamp; ALTER TABLE my_table RENAME COLUMN timestamp_temp TO timestamp;
  2. Use @TypeConverter to handle both types in the DAO. Example: @TypeConverter public static Long fromTimestamp(String value) { return value == null ? null : Long.parseLong(value); }

Dead Ends

Common approaches that don't work:

  1. Delete app data and reinstall 80% fail

    This only works for development but destroys user data in production. The underlying migration issue is not fixed.

  2. Add fallbackToDestructiveMigration() to Room database builder 90% fail

    This drops the entire database on migration failure, losing all user data. It's a nuclear option that doesn't fix the type mismatch.