How to totally fix SQLite "no such table error" on Android 9.0 in Upgrade

Question

I have also met sqlite "no such table" error on Android 9.0, I have found there is a similar issue on SO. (Android P - 'SQLite: No Such Table Error' after copying database from assets).

Base on above link, I tried below solutions 1 and 2.
1) Close connection after read

private void createDataBase() throws IOException {
    this.getReadableDatabase();
    this.close(); 
    try {           
        copyDataBase();            
    } catch (IOException e) {           
       throw new RuntimeException(e);
    }
}

2) Disable WAL.

@Override
public void onConfigure(SQLiteDatabase db) {
    Logger.i(TAG, "onConfigure()");
    super.onConfigure(db);
    db.disableWriteAheadLogging();
}

private boolean checkIfDBExists() {
    File dbFile = new File(DB_PATH + DB_NAME);
    return dbFile.exists();
}

public void openDataBase() throws SQLException {
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null,SQLiteDatabase.OPEN_READWRITE);

}

After that, I found a problem, These solutions are only fine when I remove installed App, and fresh install App.

When I have installed a buggy App with "no such table error" on Android 9.0 device, Then I add above fixed code, and install it directly with Android Studio which looks like an App upgrade scenario.

In this case, The db file will be over there, checkIfDBExists() will return true, then we will call openDataBase() instead of createDataBase() The sqlite "no such table" error will still happen, So How can I totally fix this issue no matter fresh install or upgrade? Thanks in advance.

score 0 · Accepted Answer · answered Jan 21 '19 at 09:02

After trying several times, I have found a solution to fix this issue. In databases folder, there are some temp files xxx.db-shm, xxx.db-wal, These files should be generated when enable WAL, so even after we disable WAL later, they are already there which will affect db query.

So the solution is simple, delete these files when you disable WAL.

How to totally fix SQLite "no such table error" on Android 9.0 in Upgrade

1 Answers1