尝试编写一个只读数据库...但是我不是 [英] Attempt to write a readonly database... but I'm not

问题描述

我有一个只读数据库连接.有时，当使用SELECT查询从数据库中读取数据时，它会抛出SQLiteReadOnlyDatabaseException.

我这样打开连接:

 return SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
 

查询是:

Select * FROM BudgetVersions WHERE entityId = ?

我使用db.rawQuery()从数据库中读取数据，如下所示:

 String query = ...;
Cursor c = db.rawQuery(query, new String[]{ activeBudgetId });
try {
    if (c.moveToFirst()) {            
        bv.versionName = c.getString(c.getColumnIndexOrThrow("versionName"));
        return bv;
    } else {
        return null;
    }
} finally {
    c.close();
}
 

很少会在c.moveToFirst()的调用中出现这样的崩溃:

 Caused by: android.database.sqlite.SQLiteReadOnlyDatabaseException: attempt to write a readonly database (code 776)
at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)
at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:845)
at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:836)
at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:144)
at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:133)
at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:197)
at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:237)
 

作为一种解决方法，我可以尝试使用可写的数据库连接，但我想知道崩溃发生的原因.

我正在读取的表是标准的SQLite表:

CREATE TABLE BudgetVersions (
    entityId        VARCHAR  PRIMARY KEY NOT NULL UNIQUE,
    budgetId        VARCHAR  NOT NULL,
    versionName     VARCHAR  NOT NULL,
    dateFormat      VARCHAR,
    currencyFormat  VARCHAR,
    lastAccessedOn  DATETIME,
    isTombstone     BOOL     NOT NULL,
    deviceKnowledge NUMERIC  NOT NULL
);

我已经看到崩溃同时发生在KitKat模拟器和运行Lollipop的设备上.

---

有一个单独可写连接，同时打开了一个由WebView拥有的同时数据库.正在使用WebView中的Javascript代码更新数据库，并使用此只读连接在本机Android/Java层中进行读取.

我希望这可能是导致问题的最终原因，但我想详细了解为什么只读连接会干扰单独的可写连接.

我很清楚，一般建议是使用到数据库的单个连接，但是由于可写连接由WebView拥有，所以我不容易从Java代码访问它.

解决方案

通过将其更改为可写的数据库连接来解决.线索位于文档中，有关776错误代码:

(776)SQLITE_READONLY_ROLLBACK

SQLITE_READONLY_ROLLBACK错误代码是针对以下内容的扩展错误代码 SQLITE_READONLY. SQLITE_READONLY_ROLLBACK错误代码指示 无法打开数据库，因为它有一个热日志 需要回滚，但不能，因为数据库是只读的.

在开发过程中，我经常打断当前正在运行的应用程序以安装和运行新版本.这将导致当前正在运行的应用被系统强制停止.如果在对应用程序进行裸操作时，WebView中的Javascript代码正在通过其单独的可写连接写入数据库，则热门期刊将被抛在后面.

启动新版本的应用程序时，将打开本机Java代码中的只读数据库连接.当此连接发现日志时，它将尝试回滚日志.而且由于它是只读连接，所以会失败.

(这与我进行更改后立即在启动时观察到的崩溃相符.)

因此，正确的解决方法是使Java连接成为可写连接.在正常操作期间，此连接从不尝试写入，但是在通过WebView的可写连接从先前中断的写入中恢复时，它必须写入.

I have a read-only database connection. Sometimes, when reading data from the database with a SELECT query, it throws a SQLiteReadOnlyDatabaseException.

I open the connection like this:

return SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);

The query is:

Select * FROM BudgetVersions WHERE entityId = ?

I read data from the database using db.rawQuery(), like this:

String query = ...;
Cursor c = db.rawQuery(query, new String[]{ activeBudgetId });
try {
    if (c.moveToFirst()) {            
        bv.versionName = c.getString(c.getColumnIndexOrThrow("versionName"));
        return bv;
    } else {
        return null;
    }
} finally {
    c.close();
}

Very rarely, I get a crash like this, inside the call to c.moveToFirst():

Caused by: android.database.sqlite.SQLiteReadOnlyDatabaseException: attempt to write a readonly database (code 776)
at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)
at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:845)
at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:836)
at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:144)
at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:133)
at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:197)
at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:237)

As a workaround, I could try using a writable database connection instead, but I'd like to know why the crash is happening.

The table I'm reading from is a standard SQLite table:

CREATE TABLE BudgetVersions (
    entityId        VARCHAR  PRIMARY KEY NOT NULL UNIQUE,
    budgetId        VARCHAR  NOT NULL,
    versionName     VARCHAR  NOT NULL,
    dateFormat      VARCHAR,
    currencyFormat  VARCHAR,
    lastAccessedOn  DATETIME,
    isTombstone     BOOL     NOT NULL,
    deviceKnowledge NUMERIC  NOT NULL
);

I've seen the crash happen on both a KitKat emulator and a device running Lollipop.

---

There is a separate writeable connection open to the same database at the same time, owned by a WebView. The database is being updated by Javascript code in the WebView, and read from in the native Android/Java layer with this read-only connection.

I expect this may prove to be the ultimate cause of the problem, but I'd like to understand in detail why a read-only connection would interfere with a separate writeable connection.

I am well aware that the general advice is to use a single connection to the database, but since the writeable connection is owned by the WebView, I don't have easy access to it from the Java code.

解决方案

Solved by changing it to a writeable database connection. The clue was in the documentation for the 776 error code:

(776) SQLITE_READONLY_ROLLBACK

The SQLITE_READONLY_ROLLBACK error code is an extended error code for SQLITE_READONLY. The SQLITE_READONLY_ROLLBACK error code indicates that a database cannot be opened because it has a hot journal that needs to be rolled back but cannot because the database is readonly.

During development, I am frequently interrupting the currently-running app to install and run a new version. This causes the currently-running app to be force-stopped by the system. If the Javascript code in the WebView is in the middle of writing to the database via its separate writeable connection when the app is nuked, then a hot journal will be left behind.

When the new version of the app starts up, the read-only database connection in the native Java code is opened. When this connection spots the journal, it tries to roll back the journal. And because it's a read-only connection, it fails.

(This fits with the crash being observed immediately on startup after I've made a change.)

The correct fix is therefore to make the Java connection a writeable connection. This connection never attempts a write during normal operation, but it must write when recovering from a previous interrupted write through the WebView's writeable connection.

这篇关于尝试编写一个只读数据库...但是我不是的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！