How to solve the issue SQLiteException: no such table: Cars (code 1 SQLITE_ERROR): , while compiling: SELECT DISTINCT * FROM Cars?

Question

I am playing with SQLite in my app and tring to import a backed up database into the app. Everything works fine for Oreo and below SDK versions (until SDK 16), but not for Android P and above. My database:

    private val dbTemp = DB_NAME + "_tmp"
    private val dbBackup = context.openOrCreateDatabase(dbTemp, Context.MODE_PRIVATE, null)

    override fun onCreate(db: SQLiteDatabase) {
            db.execSQL("CREATE TABLE $TABLE ($ID INTEGER PRIMARY KEY AUTOINCREMENT, $TITLE TEXT, $CAR TEXT, $DATE TEXT);")
        }

        override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
            db.execSQL("DROP TABLE IF EXISTS $TABLE")
            onCreate(db)
        }

I am coping the the backed up database by replacing the current one as follows:

private fun copyData() {
        db.delete(TABLE, null, null)
        val cursor = dbBackup.query(  // <- here occurs the problem!
            true,
            TABLE,
            null,
            null,
            null,
            null,
            null,
            null,
            null
        )
        cursor.moveToFirst()
        while (!cursor.isAfterLast) {
            db.insert(TABLE, null, modelToValues(cursorToModel(cursor)))
            cursor.moveToNext()
        }
        cursor.close()
        context.deleteDatabase(dataTmp)
    }

This is the thrown issue:

Process: com.example.cars, PID: 18674
        android.database.sqlite.SQLiteException: no such table: Cars (code 1 SQLITE_ERROR): , while compiling: SELECT DISTINCT * FROM Cars
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:903)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:514)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
            at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
            at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1408)
            at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1255)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1126)
            at com.easyapps.cryptnote.ListDatabase.copyData(CarsDatabase.kt:163)
            at com.easyapps.cryptnote.ListDatabase.importToApp(CarsDatabase.kt:155)
            at com.example.cars.BackupActivity$onCreate$3$$special$$inlined$apply$lambda$2.onClick(BackupActivity.kt:104)
            at com.android.internal.app.AlertController$ButtonHandler.handleMessage(AlertController.java:172)
            at android.os.Handler.dispatchMessage(Handler.java:106)
            at android.os.Looper.loop(Looper.java:193)
            at android.app.ActivityThread.main(ActivityThread.java:6669)
            at java.lang.reflect.Method.invoke(Native Method)
            at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)

I tried to combine the solutions from these links link and link, but no success. For more info, my backup and import of database solution is based on this demo project, which is too old, but nevertheless, I could use it for my project in Kotlin.

Backup method:

fun exportToSD() {
        createFolderOnSD()
        val data = Environment.getDataDirectory()
        val backupDbPath = File(sdFolder, "/(${utilities.getDate("dd_MM_yyyy_HH_mm")}) " + DB_NAME)

        try {
            val source = FileInputStream(File(data, currentDbPath)).channel
            val destination = FileOutputStream(backupDbPath).channel
            destination.transferFrom(source, 0, source.size())
            source.close()
            destination.close()
        } catch (e: IOException) {
            e.printStackTrace()
        }
    }

Import to App method:

fun importToApp(fileNameOnSD: String) {
        val sd = File(sdFolder)
        if (sd.canWrite()) {
            val currentDB = File(Environment.getDataDirectory(), dataTmp)
            val backupDB = File(sd, fileNameOnSD)

            if (currentDB.exists()) {
                try {
                    val src = FileInputStream(backupDB).channel
                    val dst = FileOutputStream(currentDB).channel
                    dst.transferFrom(src, 0, src.size())
                    src.close()
                    dst.close()
                } catch (e: FileNotFoundException) {
                    e.printStackTrace()
                } catch (e: IOException) {
                    e.printStackTrace()
                }
            }
        }
        copyData()
    }

Answer 1

You can disable Write Ahead Logging option by adding the following to your SQLiteOpenHelper class (Note in onConfigure not onOpen as in other linked answers) - This works in my App.

// Called when the database connection is being configured.
    // Configure database settings for things like foreign key support, write-ahead logging, etc.
    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        // Later version of Android seem to enable writeAhead by default, so for consistency explicitly disable it
        db.disableWriteAheadLogging();
    }

Answer 2

When you do the backup is the -wal file (database file name suffixed with -wal) not empty and also not backed/restored along with the database file?

What you describe is symptomatic of the issues that having a -wal file that is not empty (fully checkpointed) and not backed up and subsequently restored.

The correct way to back a database using WAL mode is to either backup the database file and the -wal file and to restore both, or to ensure that the database is fully check-pointed (i.e. changes in the -wal file have been applied to the database file) and then there is only the need to backup the database file.

• closing all database connections will fully checkpoint the database.

The reason behind this is that with Android 9+ the default mode is WAL (Write Ahead Logging) instead of Journal. With journal mode changes are applied to the database and a log/journal of those changes is stored in the journal file. Loss of the journal file means that you cannot roll back changes. Whilst in WAL mode changes are written to the WAL file, which is effectively part of the database. Should the WAL file be lost then at best the changes will be lost.

Answer 3

Try to call db.close() once after data is copied in copyData() method