Android Sqlite 应用程序在 Android pie 及更高版本中崩溃给出 SQLiteDiskIOException(代码 522)

Question

我正在尝试在调用 onupgrade 方法时从我的数据库备份表。我正在使用此处提供的解决方案 Android Sqlite onupgrade delete table from database .它在 Android 8 上运行良好，但在 Android 9 及更高版本上崩溃。

这是方法。

public static void restoreTable(Context context, String dbName, String table) {
    ContentValues cv = new ContentValues();
    SQLiteDatabase dbNew = SQLiteDatabase.openDatabase(context.getDatabasePath(dbName).toString(), null,SQLiteDatabase.OPEN_READWRITE);
    SQLiteDatabase dbOld = SQLiteDatabase.openDatabase(context.getDatabasePath(dbName + backup).toString(),null,SQLiteDatabase.OPEN_READONLY);
    Cursor csr = dbOld.query(table,null,null,null,null,null,null);
    dbNew.beginTransaction();
    while (csr.moveToNext()) {
        cv.clear();
        int offset = 0;
        for (String column: csr.getColumnNames()) {
            switch (csr.getType(offset++)){
                case Cursor.FIELD_TYPE_NULL:
                    break;
                case Cursor.FIELD_TYPE_INTEGER:
                    cv.put(column,csr.getLong(csr.getColumnIndex(column)));
                    break;
                case Cursor.FIELD_TYPE_FLOAT:
                    cv.put(column,csr.getFloat(csr.getColumnIndex(column)));
                    break;
                case Cursor.FIELD_TYPE_STRING:
                    cv.put(column,csr.getString(csr.getColumnIndex(column)));
                    break;
                case Cursor.FIELD_TYPE_BLOB:
                    cv.put(column,csr.getBlob(csr.getColumnIndex(column)));
            }
        }
        dbNew.insert(DatabaseHelper.TABLE_BOOKMARK,null,cv);
    }
    dbNew.setTransactionSuccessful();
    dbNew.endTransaction();
    csr.close();
    dbNew.close();
    dbOld.close();
}

这是日志。

Fatal Exception: java.lang.RuntimeException: Unable to start activity ComponentInfo{com.elytelabs.literarytermsdictionary/com.elytelabs.literarytermsdictionary.MainActivity}: android.database.sqlite.SQLiteException: no such table: bookmark (code 1): , while compiling: SELECT * FROM bookmark
#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
    (no such table: bookmark (code 1): , while compiling: SELECT * FROM bookmark)
#################################################################
       at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2957)
       at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3032)
       at android.app.ActivityThread.-wrap11(Unknown Source)
       at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1696)
       at android.os.Handler.dispatchMessage(Handler.java:105)
       at android.os.Looper.loop(Looper.java:164)
       at android.app.ActivityThread.main(ActivityThread.java:6944)
       at java.lang.reflect.Method.invoke(Method.java)
       at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:327)
       at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1374)
Caused by android.database.sqlite.SQLiteException: no such table: bookmark (code 1): , while compiling: SELECT * FROM bookmark
#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
    (no such table: bookmark (code 1): , while compiling: SELECT * FROM bookmark)
#################################################################
       at android.database.sqlite.SQLiteConnection.nativePrepareStatement(SQLiteConnection.java)
       at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1096)
       at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:661)
       at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
       at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
       at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
       at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
       at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1746)
       at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1593)
       at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1464)
       at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1632)
       at com.elytelabs.literarytermsdictionary.database.DatabaseHandler.restoreTable(DatabaseHandler.java:240)
       at com.elytelabs.literarytermsdictionary.database.DatabaseHelper.<init>(DatabaseHelper.java:34)
       at com.elytelabs.literarytermsdictionary.MainActivity.onCreate(MainActivity.java:72)
       at android.app.Activity.performCreate(Activity.java:7183)
       at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1220)
       at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2910)
       at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3032)
       at android.app.ActivityThread.-wrap11(Unknown Source)
       at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1696)
       at android.os.Handler.dispatchMessage(Handler.java:105)
       at android.os.Looper.loop(Looper.java:164)
       at android.app.ActivityThread.main(ActivityThread.java:6944)
       at java.lang.reflect.Method.invoke(Method.java)
       at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:327)
       at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1374)

Answer 1

该消息表明文件(猜测的备份)已被截断。根据

(522) SQLITE_IOERR_SHORT_READ

The SQLITE_IOERR_SHORT_READ error code is an extended error code for SQLITE_IOERR indicating that a read attempt in the VFS layer was unable to obtain as many bytes as was requested. This might be due to a truncated file.

Result and Error Codes

我怀疑您的问题与 Android 9 默认使用 WAL(Write-Ahead Logging 而不是日志模式)有关。这会产生两个附加文件(后缀为 -shm 和 -wal 的数据库文件)。这些包含未提交的事务。

您可能需要在备份之前完全检查数据库，或者您需要备份 -wal 和 -shm 文件。

或者，当仍有事务等待提交时，您可能正在从 SQLite 管理工具中保存文件。因此，我建议您始终关闭该工具，然后重新打开该工具，然后复制文件。我还建议您检查 -wal 和 -shm 文件是否存在，以及 -wal 文件的长度是否大于 0(如果大于 0，则 -wal 将有未完成的交易被提交)。运行 PRAGMA wal_checkpoint(TRUNCATE)，然后运行 ​​PRAGMA wal_checkpoint(您将寻找第一个结果中匹配的第 2 个和第 3 个数字，并且它们在第一个结果中都为 0第二个结果)。

或者，您可以使用 SQliteDatabase disableWriteAheadLogging 禁用 WAL方法。

这是我在备份前用来检查数据库的方法(处理 WAl 和 JORUNAL 模式):-

private void checkpointIfWALEnabled(Context context) {
    final String TAG = "WALCHKPNT";
    Cursor csr;
    int wal_busy = -99, wal_log = -99, wal_checkpointed = -99;
    SQLiteDatabase db = SQLiteDatabase.openDatabase(context.getDatabasePath(DBConstants.DATABASE_NAME).getPath(),null,SQLiteDatabase.OPEN_READWRITE);
    csr = db.rawQuery("PRAGMA journal_mode",null);
    if (csr.moveToFirst()) {
        String mode = csr.getString(0);
        //Log.d(TAG, "Mode is " + mode);
        if (mode.toLowerCase().equals("wal")) {
            csr = db.rawQuery("PRAGMA wal_checkpoint",null);
            if (csr.moveToFirst()) {
                wal_busy = csr.getInt(0);
                wal_log = csr.getInt(1);
                wal_checkpointed = csr.getInt(2);
            }
            //Log.d(TAG,"Checkpoint pre checkpointing Busy = " + String.valueOf(wal_busy) + " LOG = " + String.valueOf(wal_log) + " CHECKPOINTED = " + String.valueOf(wal_checkpointed) );
            csr = db.rawQuery("PRAGMA wal_checkpoint(TRUNCATE)",null);
            csr.getCount();
            csr = db.rawQuery("PRAGMA wal_checkpoint",null);
            if (csr.moveToFirst()) {
                wal_busy = csr.getInt(0);
                wal_log = csr.getInt(1);
                wal_checkpointed = csr.getInt(2);
            }
            //Log.d(TAG,"Checkpoint post checkpointing Busy = " + String.valueOf(wal_busy) + " LOG = " + String.valueOf(wal_log) + " CHECKPOINTED = " + String.valueOf(wal_checkpointed) );
        }
    }
    csr.close();
    db.close();
}

我已经重写了 DatabaseAssetHandler(如链接中所用)类以包含上述 checkpointIfWALEnabled 方法并专门设置版本号，因为它似乎没有这样做(并且会重新复制数据库来自 Assets )。

这确实需要传递版本号的调用。

新的 DatabaseAssetHandler 类是:-

public class DatabaseAssetHandler {

    static final String[] tempfiles = new String[]{"-journal","-wal","-shm"}; // temporary files to rename
    public static final String backup = "-backup"; //value to be appended to file name when renaming (psuedo delete)
    public static final  int OUCH = -666666666;

    /**
     * Check if the database already exists. NOTE will create the databases folder is it doesn't exist
     * @return true if it exists, false if it doesn't
     */
    public static boolean checkDataBase(Context context, String dbname) {

        File db = new File(context.getDatabasePath(dbname).getPath()); //Get the file name of the database
        Log.d("DBPATH","DB Path is " + db.getPath()); //TODO remove if publish App
        if (db.exists()) return true; // If it exists then return doing nothing

        // Get the parent (directory in which the database file would be)
        File dbdir = db.getParentFile();
        // If the directory does not exits then make the directory (and higher level directories)
        if (!dbdir.exists()) {
            db.getParentFile().mkdirs();
            dbdir.mkdirs();
        }
        return false;
    }

    /**
     * Copy database file from the assets folder
     * (long version caters for asset file name being different to the database name)
     * @param context           Context is needed to get the applicable package
     * @param dbname            name of the database file
     * @param assetfilename     name of the asset file
     * @param deleteExistingDB  true if an existing database file should be deleted
     *                              note will delete journal and wal files
     *                              note doen't actually delete the files rater it renames
     *                              the files by appended -backup to the file name
     *                              SEE/USE clearForceBackups below to delete the renamed files
     */
    public static void copyDataBase(Context context, String dbname, String assetfilename, boolean deleteExistingDB, int version) {

        checkpointIfWALEnabled(context,dbname);
        final String TAG = "COPYDATABASE";
        int stage = 0, buffer_size = 4096, blocks_copied = 0, bytes_copied = 0;
        File f = new File(context.getDatabasePath(dbname).toString());
        InputStream is;
        OutputStream os;

        /**
         * If forcing then effectively delete (rename) current database files
         */
        if (deleteExistingDB) {
            f.renameTo(context.getDatabasePath(dbname + backup));
            for (String s: tempfiles) {
                File tmpf = new File(context.getDatabasePath(dbname + s).toString());
                if (tmpf.exists()) {
                    tmpf.renameTo(context.getDatabasePath(dbname + s + backup));
                }
            }
        }

        //Open your local db as the input stream
        Log.d(TAG,"Initiated Copy of the database file " + assetfilename + " from the assets folder."); //TODO remove if publishing
        try {
            is = context.getAssets().open(assetfilename); // Open the Asset file
            stage++;
            Log.d(TAG, "Asset file " + assetfilename + " found so attmepting to copy to " + f.getPath()); //TODO remove if publishing

            os = new FileOutputStream(f);
            stage++;
            //transfer bytes from the inputfile to the outputfile
            byte[] buffer = new byte[buffer_size];
            int length;
            while ((length = is.read(buffer)) > 0) {
                blocks_copied++;
                Log.d(TAG, "Attempting copy of block " + String.valueOf(blocks_copied) + " which has " + String.valueOf(length) + " bytes."); //TODO remove if publishing
                os.write(buffer, 0, length);
                bytes_copied += length;
            }
            stage++;
            Log.d(TAG,
                    "Finished copying Database " + dbname +
                            " from the assets folder, to  " + f.getPath() +
                            String.valueOf(bytes_copied) + "were copied, in " +
                            String.valueOf(blocks_copied) + " blocks of size " +
                            String.valueOf(buffer_size) + "."
            ); //TODO remove if publishing
            //Close the streams
            os.flush();
            stage++;
            os.close();
            stage++;
            is.close();
            Log.d(TAG, "All Streams have been flushed and closed.");
            if (version > 0) {
                setVersion(context,dbname,version);
            }
        } catch (IOException e) {
            String exception_message = "";
            e.printStackTrace();
            switch (stage) {
                case 0:
                    exception_message = "Error trying to open the asset " + dbname;
                    break;
                case 1:
                    exception_message = "Error opening Database file for output, path is " + f.getPath();
                    break;
                case 2:
                    exception_message = "Error flushing written database file " + f.getPath();
                    break;
                case 3:
                    exception_message = "Error closing written database file " + f.getPath();
                    break;
                case 4:
                    exception_message = "Error closing asset file " + f.getPath();

            }
            throw new RuntimeException("Unable to copy the database from the asset folder." + exception_message + " see starck-trace above.");
        }
    }

    /**
     * Copy the databsse from the assets folder where asset name and dbname are the same
     * @param context
     * @param dbname
     * @param deleteExistingDB
     */
    public static void copyDataBase(Context context, String dbname, boolean deleteExistingDB, int version) {
        copyDataBase(context, dbname,dbname,deleteExistingDB, version);
    }

    /**
     * Get the SQLite_user_vesrion from the DB in the asset folder
     *
     * @param context           needed to get the appropriate package assets
     * @param assetfilename     the name of the asset file (assumes/requires name matches database)
     * @return                  the version number as stored in the asset DB
     */
    public static int getVersionFromDBInAssetFolder(Context context, String assetfilename) {
        InputStream is;
        try {
            is = context.getAssets().open(assetfilename);
        } catch (IOException e) {
            return OUCH;
        }
        return getDBVersionFromInputStream(is);
    }

    /**
     * Get the version from the database itself without opening the database as an SQliteDatabase
     * @param context   Needed to ascertain package
     * @param dbname    the name of the dataabase
     * @return          the version number extracted
     */
    public static int getVersionFromDBFile(Context context, String dbname) {
        InputStream is;
        try {
            is = new FileInputStream(new File(context.getDatabasePath(dbname).toString()));
        } catch (IOException e) {
            return OUCH;
        }
        return getDBVersionFromInputStream(is);
    }

    /**
     * Get the Database Version (user_version) from an inputstream
     *  Note the inputstream is closed
     * @param is    The Inputstream
     * @return      The extracted version number
     */
    private static int getDBVersionFromInputStream(InputStream is) {
        int rv = -1, dbversion_offset = 60, dbversion_length = 4 ;
        byte[] dbfileheader = new byte[64];
        byte[] dbversion = new byte[4];
        try {
            is.read(dbfileheader);
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
            return rv;
        }

        for (int i = 0; i < dbversion_length; i++ ) {
            dbversion[i] = dbfileheader[dbversion_offset + i];
        }
        return ByteBuffer.wrap(dbversion).getInt();
    }

    /**
     * Check to see if the asset file exists
     *
     * @param context           needed to get the appropriate package
     * @param assetfilename     the name of the asset file to check
     * @return                  true if the asset file exists, else false
     */
    public static boolean ifAssetFileExists(Context context, String assetfilename) {
        try {
            context.getAssets().open(assetfilename);
        } catch (IOException e) {
            return false;
        }
        return true;
    }


    /**
     * Delete the backup
     * @param context
     * @param dbname
     */
    public static void clearForceBackups(Context context, String dbname) {
        String[] fulllist = new String[tempfiles.length + 1];

        for (int i = 0;i < tempfiles.length; i++) {
            fulllist[i] = tempfiles[i];
        }
        fulllist[tempfiles.length] = ""; // Add "" so database file backup is also deleted
        for (String s: fulllist) {
            File tmpf = new File(context.getDatabasePath(dbname + s + backup).toString());
            if (tmpf.exists()) {
                tmpf.delete();
            }
        }
    }

    /**
     *
     * @param context   The context so that the respective package is used
     * @param dbname    The name of the database (the old will have -backup appended)
     * @param table     The table from which to copy the data
     */
    public static void restoreTable(Context context, String dbname, String table) {
        ContentValues cv = new ContentValues();
        SQLiteDatabase dbnew = SQLiteDatabase.openDatabase(context.getDatabasePath(dbname).toString(), null,SQLiteDatabase.OPEN_READWRITE);
        SQLiteDatabase dbold = SQLiteDatabase.openDatabase(context.getDatabasePath(dbname + backup).toString(),null,SQLiteDatabase.OPEN_READONLY);
        Cursor csr = dbold.query(table,null,null,null,null,null,null);
        dbnew.beginTransaction();
        while (csr.moveToNext()) {
            cv.clear();
            int offset = 0;
            for (String column: csr.getColumnNames()) {
                switch (csr.getType(offset++)){
                    case Cursor.FIELD_TYPE_NULL:
                        break;
                    case Cursor.FIELD_TYPE_INTEGER:
                        cv.put(column,csr.getLong(csr.getColumnIndex(column)));
                        break;
                    case Cursor.FIELD_TYPE_FLOAT:
                        cv.put(column,csr.getFloat(csr.getColumnIndex(column)));
                        break;
                    case Cursor.FIELD_TYPE_STRING:
                        cv.put(column,csr.getString(csr.getColumnIndex(column)));
                        break;
                    case Cursor.FIELD_TYPE_BLOB:
                        cv.put(column,csr.getBlob(csr.getColumnIndex(column)));
                }
            }
            dbnew.insert(DatabaseHelper.TABLE_BOOKMARK,null,cv);
        }
        dbnew.setTransactionSuccessful();
        dbnew.endTransaction();
        csr.close();
        dbnew.close();
        dbold.close();
    }

    private static void checkpointIfWALEnabled(Context context, String dbname) {
        final String TAG = "WALCHKPNT";
        Cursor csr;
        int wal_busy = -99, wal_log = -99, wal_checkpointed = -99;
        if (!new File(context.getDatabasePath(dbname).getPath()).exists()) {
            return;
        }
        SQLiteDatabase db = SQLiteDatabase.openDatabase(context.getDatabasePath(dbname).getPath(),null,SQLiteDatabase.OPEN_READWRITE);
        csr = db.rawQuery("PRAGMA journal_mode",null);
        if (csr.moveToFirst()) {
            String mode = csr.getString(0);
            //Log.d(TAG, "Mode is " + mode);
            if (mode.toLowerCase().equals("wal")) {
                csr = db.rawQuery("PRAGMA wal_checkpoint",null);
                if (csr.moveToFirst()) {
                    wal_busy = csr.getInt(0);
                    wal_log = csr.getInt(1);
                    wal_checkpointed = csr.getInt(2);
                }
                //Log.d(TAG,"Checkpoint pre checkpointing Busy = " + String.valueOf(wal_busy) + " LOG = " + String.valueOf(wal_log) + " CHECKPOINTED = " + String.valueOf(wal_checkpointed) );
                csr = db.rawQuery("PRAGMA wal_checkpoint(TRUNCATE)",null);
                csr.getCount();
                csr = db.rawQuery("PRAGMA wal_checkpoint",null);
                if (csr.moveToFirst()) {
                    wal_busy = csr.getInt(0);
                    wal_log = csr.getInt(1);
                    wal_checkpointed = csr.getInt(2);
                }
                //Log.d(TAG,"Checkpoint post checkpointing Busy = " + String.valueOf(wal_busy) + " LOG = " + String.valueOf(wal_log) + " CHECKPOINTED = " + String.valueOf(wal_checkpointed) );
            }
        }
        csr.close();
        db.close();
    }

    private static void setVersion(Context context, String dbname, int version) {
        SQLiteDatabase db = SQLiteDatabase.openDatabase(context.getDatabasePath(dbname).getPath(),null,SQLiteDatabase.OPEN_READWRITE);
        db.setVersion(version);
        db.close();

    }
}

DatabaseHelper(即 SQLiteOpenHelper 的子类)的使用示例

public class DatabaseHelper extends SQLiteOpenHelper {

    private  static final String DB_NAME = "dictionary.db"; // The database file name
    private static final int DB_VERSION = 1;
    public Context mcontext;
    public SQLiteDatabase mDatabase;

    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        this.mcontext = context;
        Log.d("DBVERSION","The Database Version (as hard coded) is " + String.valueOf(DB_VERSION));

        int dbversion = DatabaseAssetHandler.getVersionFromDBFile(context,DB_NAME);
        Log.d("DBVERSION","The Database Version (as per the database file) is " + String.valueOf(dbversion));

        // Copy the Database if no database exists
        if (!DatabaseAssetHandler.checkDataBase(context,DB_NAME)) {
            DatabaseAssetHandler.copyDataBase(context,DB_NAME,true,DB_VERSION);
        } else {
            // Copy the database if DB_VERSION is greater then the version stored in the database (user_version value in the db header)
            if (DB_VERSION > dbversion && DatabaseAssetHandler.checkDataBase(context, DB_NAME)) {
                DatabaseAssetHandler.copyDataBase(context, DB_NAME, true,DB_VERSION);
                DatabaseAssetHandler.restoreTable(context,DB_NAME,????THE_TABLE_NAME????); // Example of restoring a table (note ????THE_TABLE_NAME???? must be changed accordingly)
                DatabaseAssetHandler.clearForceBackups(context, DB_NAME); // Clear the backups
            }
        }
        mDatabase = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    // onUpgrade should not be used for the copy as may be issues due to db being opened
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
}

1. 因此在构建数据库版本时(根据 user_version)使用 getVersionFromDBFile 方法从文件头中提取(如果没有这样的文件则返回 -666666)。
2. checkDataBase 方法用于查看数据库文件是否存在，如果不存在，则从 Assets 文件夹中复制数据库。
3. 否则，将标题中的版本与编码的版本号进行比较。如果编码版本号大于 header 中存储的版本号，则复制数据库，保留旧数据库文件的副本。
4. 然后通过 restoreTable 方法从旧数据库中恢复要恢复的表(可以恢复更多)。
5. 使用 clearForceBackups 方法删除数据库文件的旧副本。