Android P - 'Sqlite: No Such Table Error' After Copying Database from Assets

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

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

SQLite: No Such Table Error Android P problem

Arg finally solved it adding this.close(); after this.getReadableDatabase();

That openned connection was generating the original no such table exception

So I used Util.getDatabasePath(DB_NAME); instead of the complex propossed solution in Android P - 'SQLite: No Such Table Error' after copying database from assets and now the code is much more simpler

Thanks a lot to @LifeStyle who found the real problem.

Now the code is much more simpler:

public static String getDatabasePath(String fileNname){
    return ApplicationContextProvider.getContext().getDatabasePath(fileNname).getAbsolutePath();
}

public class DbHelper extends SQLiteOpenHelper{
    private String DB_NAME;
    private String DB_COMPLETE_PATH;
    private SQLiteDatabase mDataBase;
    private static final int DATABASE_VERSION = 1;

    public DbHelper(String name) throws IOException {
        super(ApplicationContextProvider.getContext(), name+".db", null, DATABASE_VERSION);
        this.DB_NAME = name+".db";
        this.DB_COMPLETE_PATH = Util.getDatabasePath(DB_NAME);

        if (checkIfDBExists()==false){
            createDataBase();
        }

        openDataBase();
    }

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

    private boolean checkIfDBExists() {
        File dbFile = new File(DB_COMPLETE_PATH);
        return dbFile.exists();
    }
}

no such table error in android pie after copying database from assets folder

All I had to do was to close the DB connection after this.getReadableDatabase() in createDataBase():

public void createDataBase() throws IOException {
    boolean dbExist = checkDataBase();    
    if(dbExist){
        //do nothing - database already exist    
    } else {
        //By calling this method and empty database will be created into the default system path
        //of your application so we are gonna be able to overwrite that database with our database.
        this.getReadableDatabase();
        this.close()
        try {
            copyDataBase();
        } catch (IOException e) {
            throw new Error("Error copying database");
        }
    }   
}

Android P - 'SQLite: No Such Table Error' after copying database from assets in android version 9

This is caused because of the use of this.getReadableDatabase() before the copy.

This creates a database and since Android 9 the database is opened, by default, in WAL mode. This results in two files -wal and -shm (each preceded with the database file name) that are owned by the database which is overwritten, not by database that overwrites the former. This anomaly is detected and results in an empty database being returned and hence the table not found.

There are a few get-arounds but the suggested and most efficient way is to not open the database but to instead check if the file exists.

e.g. :-

private boolean checkDB() {

    File cDB = new File(con.getDatabasePath(db_name).getPath());
    if (cDB.exists()) return true;
    if (!cDB.getParentFile().exists()) {
        cDB.getParentFile().mkdirs();
    }
    return false;
}

• Note this additionally gets around the issue why opening the database was likely introduced instead of the the file check; that is checking the file, without creating the parent directory (the databases folder) would subsequently fail when attempting the copy of the database file with an ENOENT cannot open file or directory exception.

• databases are stored, unless otherwise specified, at data/data/the_package_name/databases/. However, when an App is installed only data/data/package_name/ exists, there is no databases folder/directory.

• Opening the database as an SQLiteDatabase is quite expensive resource wise, a fair bit has to be done, the header checked, the schema generated and written to disk or read from disk into memory, the creation of the android_metadata table, the writing to the -wal and -shm files in addition to actually getting a File.

• The only flaw as such with this method is that if the asset weren't a valid database in which case a Corrupt Database exception would occur. If this is an issue then you can easily check the first 16 bytes of the header as per Database File Format.

In addition to the above you should also remove/comment out this.getReadableDatabase(); in the createDB method. As this will also result in the same issue.

A more in-depth answer

sqlite database error no such table in android P

In your createDB function you must add this.close() after this.getReadableDatabase()

this.getReadableDatabase();
this.close();

This link can help you

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

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.

Android PIE API 28 - 'SQLite: No Such Table Error'

Don't forgot to close db before copy it from asset.

public void createDataBase() {
    boolean dbExist = checkDataBase();
    if (!dbExist) {
        this.getWritableDatabase();
        this.close(); //Should close here
        try {
            copyDataBase();
        } catch (IOException e) {
            Log.e(this.getClass().toString(), "Copying error");
            throw new Error("Error copying database!");
        }
    } else {
        Log.i(this.getClass().toString(), "Database already exists");
    }
}

A pre-populated database does not work at API 28 throws no such table exception

The typical cause of an App, that uses SQLite and that copies a pre-existing database suddenly not working for API 28 is that to get around the issue of the database folder not existing (the copy would fail if the directory didn't exist) is to create an empty database and then overwrite the database.

However, as by default, from API 28, the SDK uses WAL (Write-ahead logging) and that creating the empty database to be overwritten, results in the -shm and -wal files being created. It is the existence of these files that result in the database being empty after the copy.

• I believe that this is because once the copied database is opened, a mismach is detected and the SDK's methods create an empty usable database (this is conjecture and hasn't actually been shown to be).

Quick Fix

The quick, but not recommended fix, is to override the onConfigure method in the class that subclasses SQLiteOpenHelper to use the disableWriteAheadLogging method so that the database is opened in journal mode.

• the full code below (2nd piece of code) includes this, but the line has been commented out.

Recommended Fix

The recommended method, so as to gain from the benefits of WAL, is to check for the existence of the database directory and create the directory if it doesn't exist rather than create a database to be overwritten (and therefore the -shm and -wal file don't exist when the database is copied)

The following is an example method where the directory is checked/created when checking to see if the database exists (obviously this would need to be tailored accordingly) :-

private boolean checkDataBase() {
    /**
     * Does not open the database instead checks to see if the file exists
     * also creates the databases directory if it does not exists
     * (the real reason why the database is opened, which appears to result in issues)
     */

    File db = new File(myContext.getDatabasePath(DB_NAME).getPath()); //Get the file name of the database
    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;
}

• Note that this relies upon the variable DB_NAME being the database name (file name of the database file) and that the final location of the database is the standard location (data/data/the_package/databases/).

The above has been extracted from the following subclass of SQLiteOpenHelper :-

public class DBHelper extends SQLiteOpenHelper {

    private static String DB_NAME = "db";
    private SQLiteDatabase myDataBase;
    private final Context myContext;

    private int bytes_copied = 0;
    private static int buffer_size = 1024;
    private int blocks_copied = 0;

    public DBHelper(Context context) {
        super(context, DB_NAME, null, 1);

        this.myContext = context;
        // Check for and create (copy DB from assets) when constructing the DBHelper
        if (!checkDataBase()) {
            bytes_copied = 0;
            blocks_copied = 0;
            createDataBase();
        }
    }

    /**
     * Creates an empty database on the system and rewrites it with your own database.
     * */
    public void createDataBase() {

        boolean dbExist = checkDataBase(); // Double check
        if(dbExist){
            //do nothing - database already exists
        } else {
            //By calling this method an empty database will be created into the default system path
            //of your application so we are gonna be able to overwrite that database with our database.
            //this.getReadableDatabase();
            //<<<<<<<<<< Dimsiss the above comment
            //By calling this method an empty database IS NOT created nor are the related -shm and -wal files
            //The method that creates the database is flawed and was only used to resolve the issue
            //of the copy failing in the absence of the databases directory.
            //The dbExist method, now utilised, checks for and creates the database directory, so there
            //is then no need to create the database just to create the databases library. As a result
            //the -shm and -wal files will not exist and thus result in the error associated with
            //Android 9+ failing with due to tables not existining after an apparently successful
            //copy.
            try {
                copyDataBase();
            } catch (IOException e) {
                File db = new File(myContext.getDatabasePath(DB_NAME).getPath());
                if (db.exists()) {
                    db.delete();
                }
                e.printStackTrace();
                throw new RuntimeException("Error copying database (see stack-trace above)");
            }
        }
    }

    /**
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     * @return true if it exists, false if it doesn't
     */
    private boolean checkDataBase() {
        /**
         * Does not open the database instead checks to see if the file exists
         * also creates the databases directory if it does not exists
         * (the real reason why the database is opened, which appears to result in issues)
         */

        File db = new File(myContext.getDatabasePath(DB_NAME).getPath()); //Get the file name of the database
        Log.d("DBPATH","DB Path is " + db.getPath()); //TODO remove for Live 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;
    }

    /**
     * Copies your database from your local assets-folder to the just created empty database in the
     * system folder, from where it can be accessed and handled.
     * This is done by transfering bytestream.
     * */
    private void copyDataBase() throws IOException {

        final String TAG = "COPYDATABASE";

        //Open your local db as the input stream
        Log.d(TAG,"Initiated Copy of the database file " + DB_NAME + " from the assets folder."); //TODO remove for Live App
        InputStream myInput = myContext.getAssets().open(DB_NAME); // Open the Asset file
        String dbpath = myContext.getDatabasePath(DB_NAME).getPath();
        Log.d(TAG,"Asset file " + DB_NAME + " found so attmepting to copy to " + dbpath); //TODO remove for Live App

        // Path to the just created empty db
        //String outFileName = DB_PATH + DB_NAME;
        //Open the empty db as the output stream
        File outfile = new File(myContext.getDatabasePath(DB_NAME).toString());
        Log.d("DBPATH","path is " + outfile.getPath()); //TODO remove for Live App
        //outfile.setWritable(true); // NOT NEEDED as permission already applies
        //OutputStream myoutputx2 = new FileOutputStream(outfile);
        /* Note done in checkDatabase method
        if (!outfile.getParentFile().exists()) {
            outfile.getParentFile().mkdirs();
        }
        */

        OutputStream myOutput = new FileOutputStream(outfile);
        //transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[buffer_size];
        int length;
        while ((length = myInput.read(buffer))>0) {
            blocks_copied++;
            Log.d(TAG,"Ateempting copy of block " + String.valueOf(blocks_copied) + " which has " + String.valueOf(length) + " bytes."); //TODO remove for Live App
            myOutput.write(buffer, 0, length);
            bytes_copied += length;
        }
        Log.d(TAG,
                "Finished copying Database " + DB_NAME +
                        " from the assets folder, to  " + dbpath +
                        String.valueOf(bytes_copied) + "were copied, in " +
                        String.valueOf(blocks_copied) + " blocks of size " +
                        String.valueOf(buffer_size) + "."
        ); //TODO remove for Live App
        //Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();
        Log.d(TAG,"All Streams have been flushed and closed."); //TODO remove for Live App
    }


    @Override
    public synchronized void close() {
        if(myDataBase != null)
            myDataBase.close();
        super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

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

    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        Log.d("DBCONFIGURE","Database has been configured "); //TODO remove for Live App
        //db.disableWriteAheadLogging(); //<<<<<<<<<< un-comment to force journal mode
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        Log.d("DBOPENED","Database has been opened."); //TODO remove for live App
    }
}

• Note the above code is/was intended for development/experimentation and thus includes code that could be removed.

---

Related Topics