E/SQLiteLog: (1) no such table: Cars on Android 9 Pie

Question

Why does the coping of database not work for Android 9? My implementation is based on this example (video). Everything works fine on Android versions from 4.1 up to 10, but NOT on 9 Pie.

Here what I did:

fun importToApp(fileNameOnSD: String) {
        val sd = File(sdFolder)
        if (sd.canWrite()) {
            val currentDB = File(Environment.getDataDirectory(), dataTemp)
            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()
    }

    private fun copyData() {
        db.delete(TABLE, null, null)
        val dbBackup = context.openOrCreateDatabase(DB_TEMP, Context.MODE_PRIVATE, null)
        val cursor = dbBackup.query(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(dataTemp)
        utilities.toast(context.resources.getString(R.string.db_suc_imported), 0)
    }

I also call the method (as proposed here):

override fun onOpen(db: SQLiteDatabase) {
        super.onOpen(db)
        if (Build.VERSION.SDK_INT == 28)
            db.disableWriteAheadLogging()
    }

Here is the crash caused in Android Pie:

E/SQLiteLog: (1) no such table: Cars 
D/AndroidRuntime: Shutting down VM


    --------- beginning of crash
E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.easyapps.cryptnote, PID: 3174
    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:144)
        at com.easyapps.cryptnote.ListDatabase.importToApp(CarsDatabase.kt:137)
        at com.easyapps.cryptnote.ImportActivity$importDB$$inlined$apply$lambda$1.onClick(CarsActivity.kt:273)
        at android.view.View.performClick(View.java:6597)
        at android.view.View.performClickInternal(View.java:6574)
        at android.view.View.access$3100(View.java:778)
        at android.view.View$PerformClick.run(View.java:25885)
        at android.os.Handler.handleCallback(Handler.java:873)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        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/Process: Sending signal. PID: 3174 SIG: 9

This line val cursor = dbBackup.query(true, TABLE, null, null, null, null, null, null, null) crashes the app. So far I tested in on two different real Android devices, one with Android Lollipop (Moto) and one with Android Pie (Samsung A40). The other versions were virtual. The most interesting thing is that it works on Samsung with Android Pie, but not on a virtual device with Android Pie. Nevertheless, I assume that is the Samsung device is exception, I need to get run on all Android devices, no matter real or virtual.

Just as a note that the answer https://stackoverflow.com/questions/54051322/database-import-and-export-not-working-in-android-pie/54056779 says disable write ahead logging in `onConfigure` not `onOpen` as you have done. I've did it in `onConfigure` and it worked for me. But this might not be the cause of your problems. — Andrew, Mar 09 '20 at 22:24
@Andrew Thank you for your answer. Yes, I did not mention it, but I also tried this way. So, both `onConfigure` and `onOpen` tested and nothing changed. May be you can share your way of implementation, only the using way of that method. Thanks. I do not really understand why it does not work for me and for others does! — Mark Delphi, Mar 09 '20 at 22:29
I disabled write ahead logging in the same manner you have done except for it's being in `onConfigure` and doing it unconditionally for my app for Android 5.1 to 10. But as I said I don't think this might be the cause of your problem — Andrew, Mar 09 '20 at 22:39
I also think in this way, there is missing or wrong something for Pie, but not for other versions. I can't follow this issue, however, tried many times, bit without success... I really need a support to fix it. May be you know someone who could take a look in and share with him/her. — Mark Delphi, Mar 09 '20 at 22:42
Please read [Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers?](//meta.stackoverflow.com/q/326569) - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. — halfer, Mar 10 '20 at 12:29

Mark Delphi · Answer 1 · 2020-03-11T14:09:52.867

So, I fixed the issue by using the first answer. I added an internal class

class TempDataBase(private val context: Context, dbName: String, version: Int) : SQLiteOpenHelper(
        context,
        dbName, null,
        version
    ) {
        override fun onCreate(db: SQLiteDatabase) {
            db.execSQL("CREATE TABLE $TABLE ($ID INTEGER PRIMARY KEY AUTOINCREMENT, $TITLE TEXT, $NOTE TEXT, $DATE TEXT);")
        }

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

        override fun onConfigure(db: SQLiteDatabase) {
            db.disableWriteAheadLogging()
            super.onConfigure(db)
        }
    }

and called it in my copyData() method:

private fun copyData() {
        db.delete(TABLE, null, null)
        val dbBackup = if (Build.VERSION.SDK_INT == Build.VERSION_CODES.P) {
            SQLiteDatabase.openDatabase(TempDataBase(context, DB_TEMP, 1).readableDatabase.path, null, SQLiteDatabase.OPEN_READWRITE)
        }
        else
            context.openOrCreateDatabase(DB_TEMP, Context.MODE_PRIVATE, null)
        val cursor = dbBackup.query(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()
        db.close()
        context.deleteDatabase(dataTemp)
        utilities.toast(context.resources.getString(R.string.db_suc_imported), 0)
    }

Basically, Android P looks for a database and detects that is does not exist while other versions do not really require that a temp database must really exist. My inner class creates a temp database as buffer to copy data in used by Android 9.

Whoever still looking for a solution, please leave a comment for more info and I will respond asap.

Unfortunately, it worked only one time (somehow) and not working anymore... Still testing and looking for a solution, no idea what I can do anymore. — Mark Delphi, Mar 11 '20 at 14:10

E/SQLiteLog: (1) no such table: Cars on Android 9 Pie

1 Answers1

Linked