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?

Codie

can you check if your inputStream is not null? using Android debugger? – Sherrillsherrington

I can confirm the InputStream is not null. – Codie

Immediately before return true add Log.d("COPYINFO","Bytes Copied = " + String.valueOf(totalcount) + " to " + filepath); what is the resultant output to the Log? – Unconditioned

The output on my device, which works, running Android 8.1 is (D/COPYINFO: Bytes Copied = 1687552 to /data/user/0/am.radiogr/databases/s.db) The output on Android P is (D/COPYINFO: Bytes Copied = 1687552 to /data/user/0/am.radiogr/databases/s.db) They are exactly the same. – Codie

My next move, as obviously the DB is being copied, would to to check the tables in the database. Either be querying the sqlite_master table or personally i'd use the logDatabaseInfo method from here [Are there any methods that assist with resolving common SQLite issues? ](#46642769). – Unconditioned

Using logDatabaseInfo I get the following: D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/user/0/my.packagename/databases/databasename.db Database Version = 1 D/SQLITE_CSU: Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT) D/SQLITE_CSU: Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0 This, I assume, is showing an empty database on Android P. Testing on Android 8.1 it logs all the tables I'd expect to see. – Codie

@MichaelJ yep that's an empty/raw database. Unfortunately I don't have an 8.1 device/emulator to test on. There is another question very similar to yours and I'm wondering if there is an issue. I did some looking around but didn't find anything re issues with 8.1. – Unconditioned

are you copying your database in "/data/data/<packagename>/database" folder? – Tega

Disabling write ahead logging as Ramon pointed out below worked for me. https://mcmap.net/q/212527/-android-p-39-sqlite-no-such-table-error-39-after-copying-database-from-assets – Chromoprotein

Answer 1

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

Babara

Thanks for the continued assistance. I replaced the code with yours to obtain the filePath but the same outcome persists. An empty database is created and not populated with the database in the assets folder. I should add I am testing using an emulator running Android P and not an actual device, this shouldn't prevent the code from working as intended should it? – Codie

I can reproduce the problem on an emulator, that's not a problem. Maybe you should make sure that instant run is turned off, though, just to be sure. Anyway, the problem gets resolved for me when I ensure that there are no open connections to the database before copying the file from assets. That's the key. – Babara

Apologies, you were correct. I overlooked I had called this (rather pointless) code before: openOrCreateDatabase(Utils.getDatabaseName(), MODE_PRIVATE, null); This kept an open connection to the database and caused the problem. I have since removed that code and all works fine. Thanks for the help! – Codie

Nice! Glad you got it figured out. – Babara

Disabling write ahead logging as Ramon pointed out below worked for me. https://mcmap.net/q/212527/-android-p-39-sqlite-no-such-table-error-39-after-copying-database-from-assets – Chromoprotein

@Babara it still has a problem, how you can know if the database is already created? every time you call "helper.getReadableDatabase();" to get the path you are creating the database, so checking if it exists is always true even before creating it. So... how can we know if it's created or not to fill it with assets db content the first time only? – Paragon

It works. I can copy db file to database after using SQLiteOpenHelper connect old database again and close it. – Pasargadae

Answer 2

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!

Saundrasaunter

Good answer, but to nitpick, onConfigure is a better place for this. The javadoc for onConfigure specifically mentions it is the place for things like enableWriteAheadLogging. In my testing, both places work to solve the issue on Android 9. – Zinn

Work form me! If "problem" only on 9 Android version in my case: if(Build.VERSION.SDK_INT >= 28) {database.disableWriteAheadLogging();} – Paulo

me too got stuck with the same problem, that it was working fine in devices below version 9 and now with your solution its working fine on Pie also, Thanks – Braud

It works for me. I had an error just on Android9. thanks – Annaleeannaliese

It's Work for me, But if we disable WAL it decrease amount of writes by 10% to 15% as per this source.android.com/devices/tech/perf/compatibility-wal – Landtag

Answer 3

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

Selfpronouncing

I faced exact issue, solution worked too but I am curious about the problem, Why & how does this solved problem in Android P? – Samuella

Thanks you! i spent 3 days looking for a solution, but why? – Stellular

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.

Chancroid

This is a great explanation and more detailed then the others. Thank you – Dvina

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://mcmap.net/q/211040/-android-sqlite-db-when-to-close

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.

Radmen

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.

Dismissive

how I suppose to generate an data-wal.db file? Does emulator can generate it? Because the problem is appearing on real devices and not on emulators. – Decani

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

Ardie

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.

Pecksniffian

how you add the android_metadata table? You didn't tell it in your answer – Paragon

I added it using DB browser for SQLite. You can added tables manually with that program. – Pecksniffian

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.

Rhymester

Hey, I'm currently facing the same issue. I cannot understand how it can restore a copy of the file but missing a table, how is that even possible? Any clue on how to work around it in production? – Shay

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

Abject

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

Steepen

Closing the FileOutputStream had no effect unfortunately. Also switching to the 'try with resources' method resulted in the same empty database. – Codie

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

   }

Kitchenmaid

Answer 13

The issue occurring in Android Pie, Solution is:

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

Freddiefreddy

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;

Koblick

This doesn't answer the question. Although that's the correct database path for Android 6.0 and below, this falls flat on version above that because it uses app storage per user. Better to use context.getDatabasePath(). – Sweetheart