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

Question

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?

Answer 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!

Answer 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);
    }
}

Answer 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) {
    }
}

Answer 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.

Answer 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:

1. Have single instance of SQLiteOpenHelper class in your application to access your database.
2. 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.

3. 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.

Answer 6

Solution without disabling the WAL

Android 9 introduces a special mode of SQLiteDatabase called Compatibility WAL (write-ahead loggin) that allows a database to use "journal_mode=WAL" while preserving the behavior of keeping a maximum of one connection per database.

In Detail here:
https://source.android.com/devices/tech/perf/compatibility-wal

The SQLite WAL mode is explained in detail here:
https://www.sqlite.org/wal.html

As of the official docs the WAL mode adds a second database file called databasename and "-wal". So if your database is named "data.db" it is called "data-wal.db" in the same directory.

The solution is now to save and restore BOTH files (data.db and data-wal.db) on Android 9.

Afterwards it is working as in earlier versions.

Answer 7

I had the same thing I had an application in version 4 of android, and when updating my mobile that has android 9, then I was 2 days trying to find the error, thanks for the comments in my case I just had to add this.close ();

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

ready running for all versions !!

Answer 8

First, thank you for posting this question. I had the same thing happen. All was working well, but then when testing against Android P Preview I was getting crashes. Here's the bug that I found for this code:

private void copyDatabase(File dbFile, String db_name) throws IOException{
    InputStream is = null;
    OutputStream os = null;

    SQLiteDatabase db = context.openOrCreateDatabase(db_name, Context.MODE_PRIVATE, null);
    db.close();
    try {
        is = context.getAssets().open(db_name);
        os = new FileOutputStream(dbFile);

        byte[] buffer = new byte[1024];
        while (is.read(buffer) > 0) {
            os.write(buffer);
        }
    } catch (IOException e) {
        e.printStackTrace();
        throw(e);
    } finally {
        try {
            if (os != null) os.close();
            if (is != null) is.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

The issue I ran into was this code works just fine BUT in SDK 28+ openOrCreateDatabase no longer automatically creates the android_metadata table for you. So if you do a query of "select * from TABLE" it will not find that TABLE because the query starts to look after the "first" table which should be the metadata table. I fixed this by manually adding the android_metadata table and all was well. Hope someone else finds this useful. It took forever to figure out because specific queries still worked fine.

Answer 9

Similar issue, only Android P device affected. All previous versions no problems.

Turned off auto restore on Android 9 devices.

We did this to troubleshoot. Would not recommend for production cases.

Auto restore was placing a copy of the database file in the data directory before the copy database function is called in the database helper. Therefore the a file.exists() returned true.

The database that was backed up from the development device was missing the table. Therefore "no table found" was in fact correct.

Answer 10

Here's the perfect solution for this problem:

Just override this method in your SQLiteOpenHelper class:

@Override
public void onOpen(SQLiteDatabase db) {
    super.onOpen(db);
    if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.JELLY_BEAN) {
        db.disableWriteAheadLogging();
    }
}

Answer 11

It seems that you don't close the output stream. While it probably does not explain why the db is not really created (unless Android P added a multi MB buffer) it is a good practice to use a try-with-resource, something like :

// garantees that the data are flushed and the resources freed
try (FileOutputStream 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);
    }

    // maybe a bit overkill
    output.getFD().sync();
}

Answer 12

In version P, the major change is WAL (Write Ahead Log). The following two steps are required.

1. Disable the same by the following line in config.xml in the values folder under resources.

false

2. Make the following change in the DBAdapter class in createDatabase method. Otherwise phones with earlier Android versions crash.

   private void createDataBase() throws IOException {

   if (android.os.Build.VERSION.SDK_INT < android.os.Build.VERSION_CODES.P) {
                   this.getWritableDatabase();
       try {           
           copyDataBase();            
       } catch (IOException e) {           
           throw new RuntimeException(e);
       }
   }
   

   }

Answer 13

The issue occurring in Android Pie, Solution is:

 SQLiteDatabase db = this.getReadableDatabase();
        if (db != null && db.isOpen())
            db.close();
   copyDataBase();

Answer 14

Simplest answer to use following line for Database file path in Android PIE and above:

DB_NAME="xyz.db";
DB_Path = "/data/data/" + BuildConfig.APPLICATION_ID + "/databases/"+DB_NAME;