Android P - 'SQLite: No Such Table Error' after copying database from assets

I have a database saved in my apps assets folder and I copy the database using the below code when the app first opens.

inputStream = mContext.getAssets().open(Utils.getDatabaseName());

        if(inputStream != null) {

            int mFileLength = inputStream.available();

            String filePath = mContext.getDatabasePath(Utils.getDatabaseName()).getAbsolutePath();

            // Save the downloaded file
            output = new FileOutputStream(filePath);

            byte data[] = new byte[1024];
            long total = 0;
            int count;
            while ((count = inputStream.read(data)) != -1) {
                total += count;
                if(mFileLength != -1) {
                    // Publish the progress
                    publishProgress((int) (total * 100 / mFileLength));
                }
                output.write(data, 0, count);
            }
            return true;
        }

The above code runs without problem but when you try to query the database you get an SQLite: No such table exception.

This issue only occurs in Android P, all earlier versions of Android work correctly.

Is this a known issue with Android P or has something changed?

Solution 1:

Was having a similar issue, and solved this adding this to my SQLiteOpenHelper

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        db.disableWriteAheadLogging();
    }

Apparently Android P sets the PRAGMA Log thing different. Still no idea if will have side effects, but seems to be working!

Solution 2:

My issues with Android P got solved by adding 'this.close()' after this.getReadableDatabase() in createDataBase() method as below.

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

Solution 3:

This issue seems to lead to a crash much more often on Android P than on previous versions, but it's not a bug on Android P itself.

The problem is that your line where you assign the value to your String filePath opens a connection to the database that remains open when you copy the file from assets.

To fix the problem, replace the line

String filePath = mContext.getDatabasePath(Utils.getDatabaseName()).getAbsolutePath();

with code to get the file path value and then close the database:

MySQLiteOpenHelper helper = new MySQLiteOpenHelper();
SQLiteDatabase database = helper.getReadableDatabase();
String filePath = database.getPath();
database.close();

And also add an inner helper class:

class MySQLiteOpenHelper extends SQLiteOpenHelper {

    MySQLiteOpenHelper(Context context, String databaseName) {
        super(context, databaseName, null, 2);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
}

Solution 4:

I ran into a similar issue. I was copying a database but not from an asset. What I found is that the problem had nothing to do with my database file copying code at all. Nor did it have to do with files left open, not closed, flushing or syncing. My code typically overwrites an existing unopen database. What appears to be new/diffferent with Android Pie and different from previous releases of Android, is that when Android Pie creates a SQLite database, it sets journal_mode to WAL (write-ahead logging), by default. I've never used WAL mode and the SQLite docs say that journal_mode should be DELETE by default. The problem is if I overwrite an existing database file, let's call it my.db, the write-ahead log, my.db-wal, still exists and effectively "overrides" what's in the newly copied my.db file. When I opened my database, the sqlite_master table typically only contained a row for android_metadata. All the tables I was expecting were missing. My solution is to simply set journal_mode back to DELETE after opening the database, especially when creating a new database with Android Pie.

PRAGMA journal_mode=DELETE;

Perhaps WAL is better and there's probably some way to close the database so that the write-ahead log doesn't get in the way but I don't really need WAL and haven't needed it for all previous versions of Android.

Solution 5:

Unfortunately, the accepted answer just "happens to work" in very concrete cases, but it doesn't give a consistently working advice to avoid such an error in Android 9.

Here it is:

Have single instance of SQLiteOpenHelper class in your application to access your database.
If you need to rewrite / copy the database, close the database (and close all connections to this database) using SQLiteOpenHelper.close() method of this instance AND don't use this SQLiteOpenHelper instance anymore.

After calling close(), not only all connections to the database are closed, but additional database log files are flushed to the main .sqlite file and deleted. So you have one database.sqlite file only, ready to be rewritten or copied.

After copying / rewriting etc. create a new singleton of the SQLiteOpenHelper, which getWritableDatabase() method will return new instance of the SQLite database! And use it till next time you will need your database to be copied / rewritten...

This answer helped me to figure that out: https://stackoverflow.com/a/35648781/297710

I had this problem in Android 9 in my AndStatus application https://github.com/andstatus/andstatus which has quite large suite of automated tests that consistently reproduced "SQLiteException: no such table" in Android 9 emulator before this commit: https://github.com/andstatus/andstatus/commit/1e3ca0eee8c9fbb8f6326b72dc4c393143a70538 So if you're really curious, you can run All tests before and after this commit to see a difference.