SQLiteBlobTooBigException - 华为设备，安卓9和10

Question

我在华为手机上使用Android 9和10版本时，只遇到android.database.sqlite.SQLiteBlobTooBigException

设备崩溃信息

我想在onUpgrade方法中向表格添加新列。

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
...
        case 43:
               addColumn(db, TABLE_LESSONS, _SPEED_GAME_EASY_FINISHED, " INTEGER DEFAULT 0");
               addColumn(db, TABLE_LESSONS, _SPEED_GAME_HARD_FINISHED, " INTEGER DEFAULT 0");
               addColumn(db, TABLE_LESSONS, _MEMORY_GAME_EASY_FINISHED, " INTEGER DEFAULT 0");
...
}

    private void addColumn(SQLiteDatabase db, String table, String column, String columnType) {
        boolean isColumnExist = false;
        AbstractWindowedCursor cursor = null;
        String selectQuery = "SELECT * FROM " + table + " LIMIT 0,1";

        try {
            cursor = getSqlCursorWithIncreasedWindowSize(db.rawQuery(selectQuery, null));

            if (cursor.moveToFirst()) {
                isColumnExist = cursor.getColumnIndex(column) != -1;
            }

            if (!isColumnExist) {
                db.execSQL("ALTER TABLE " + table + " ADD COLUMN " + column + columnType);
            }

        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
    }

我试图增加光标窗口的大小，但并没有帮助

fun getSqlCursorWithIncreasedWindowSize(cursor: Cursor): AbstractWindowedCursor {
    if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.P) {
        val cw = CursorWindow(null, 10000L)
        (cursor as AbstractWindowedCursor).window = cw
    }
    return cursor as AbstractWindowedCursor
}

TABLE_LESSONS 包含 10 列整数、长整数和短文本。

问题发生在 cursor.moveToFirst() 中。

java.lang.RuntimeException: 
  at android.app.ActivityThread.performLaunchActivity (ActivityThread.java:3430)
  at android.app.ActivityThread.handleLaunchActivity (ActivityThread.java:3614)
  at android.app.servertransaction.LaunchActivityItem.execute (LaunchActivityItem.java:86)
  at android.app.servertransaction.TransactionExecutor.executeCallbacks (TransactionExecutor.java:108)
  at android.app.servertransaction.TransactionExecutor.execute (TransactionExecutor.java:68)
  at android.app.ActivityThread$H.handleMessage (ActivityThread.java:2199)
  at android.os.Handler.dispatchMessage (Handler.java:112)
  at android.os.Looper.loop (Looper.java:216)
  at android.app.ActivityThread.main (ActivityThread.java:7625)
  at java.lang.reflect.Method.invoke (Method.java)
  at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run (RuntimeInit.java:524)
  at com.android.internal.os.ZygoteInit.main (ZygoteInit.java:987)
Caused by: android.database.sqlite.SQLiteBlobTooBigException: 
  at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow (SQLiteConnection.java)
  at android.database.sqlite.SQLiteConnection.executeForCursorWindow (SQLiteConnection.java:904)
  at android.database.sqlite.SQLiteSession.executeForCursorWindow (SQLiteSession.java:851)
  at android.database.sqlite.SQLiteQuery.fillWindow (SQLiteQuery.java:62)
  at android.database.sqlite.SQLiteCursor.fillWindow (SQLiteCursor.java:149)
  at android.database.sqlite.SQLiteCursor.getCount (SQLiteCursor.java:137)
  at android.database.AbstractCursor.moveToPosition (AbstractCursor.java:220)
  at android.database.AbstractCursor.moveToFirst (AbstractCursor.java:259)
  at com.kkk.english_words.data.local.db.DatabaseHelper.addColumn (DatabaseHelper.java:31)
  at com.kkk.english_words.data.local.db.DatabaseHelper.onUpgrade (DatabaseHelper.java:161)
  at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked (SQLiteOpenHelper.java:417)
  at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase (SQLiteOpenHelper.java:313)
  at com.kkk.english_words.data.local.db.DatabaseHelper.getDb (DatabaseHelper.java:2)
  at com.kkk.english_words.data.local.db.DatabaseHelper.getActivityTimeDbHelper (DatabaseHelper.java:2)
  at com.kkk.english_words.data.local.db.DatabaseHelper.getActivityTimeDbHelper (DatabaseHelper.java:1)
  at com.kkk.english_words.ui.main.MainActivity.onCreate (MainActivity.java:187)
  at android.app.Activity.performCreate (Activity.java:7458)
  at android.app.Activity.performCreate (Activity.java:7448)
  at android.app.Instrumentation.callActivityOnCreate (Instrumentation.java:1286)
  at android.app.ActivityThread.performLaunchActivity (ActivityThread.java:3409)
  at android.app.ActivityThread.handleLaunchActivity (ActivityThread.java:3614)
  at android.app.servertransaction.LaunchActivityItem.execute (LaunchActivityItem.java:86)
  at android.app.servertransaction.TransactionExecutor.executeCallbacks (TransactionExecutor.java:108)
  at android.app.servertransaction.TransactionExecutor.execute (TransactionExecutor.java:68)
  at android.app.ActivityThread$H.handleMessage (ActivityThread.java:2199)
  at android.os.Handler.dispatchMessage (Handler.java:112)
  at android.os.Looper.loop (Looper.java:216)
  at android.app.ActivityThread.main (ActivityThread.java:7625)
  at java.lang.reflect.Method.invoke (Method.java)
  at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run (RuntimeInit.java:524)
  at com.android.internal.os.ZygoteInit.main (ZygoteInit.java:987)

你有任何关于如何修复它的想法吗？

敬礼

- DonKamillo

Answer 1

当创建列时，不会发生崩溃，而是在获取数据时发生，因为数据太大而无法适应游标。

如果您想检查某个列是否存在，我建议查询sqlite_master表（https://www.sqlite.org/faq.html#q7）。

一般而言，我不建议在Android的SQLite单个记录中保留大小超过几兆字节的数据。如果您别无选择，必须在本地存储此数据，则建议将数据写入文件并将其路径存储在数据库中。

- m0skit0