0

I found the reason for my problem, see below!

In short, since the backup feature retrived an old database scheme with same version number, neither onUpdate() or onCreate() was invoked in my SQLiteHelperClass.

I'm experiencing a strange problem that I can not solve. When I run my app in an emulator, everything is ok. When I run it as a apk-debug.apk on my phone, everything is ok, but when I try to run it as a signed app on my phone, I get this error:

Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
(no such column: listId (code 1): , while compiling: SELECT * FROM mytable WHERE listId=100 ORDER BY id,name,conc ASC)

If I delete all the app data in settings an rerun the app, everything works in the signed app version although it was initially installed on a phone for the first time!

The mytable is created by running db.execSQL(DATABASE_CREATE_MY_TABLE ): (the column names are constants set elsewhere)

private static final String DATABASE_CREATE_MY_TABLE = "CREATE TABLE "
    + MY_TABLE + "("             
    + ID + " INTEGER, "
    + LIST_ID + " INTEGER, "
    + NAME + " STRING, "
    + CONC + " REAL, "
    + "PRIMARY KEY (" + ID + "," + LIST_ID + ")"
    + ")"

The code that creates this error is the following:

    SQLiteDatabase db = this.getReadableDatabase();
    String selectQuery = "SELECT * FROM " + MY_TABLE + " WHERE " + LIST_ID + "=?" + " ORDER BY " + NAME + "," + CONC + " ASC";
    Cursor cursor = db.rawQuery(selectQuery, new String[] {listId});

My main problem is the colum LIST_ID which seems to not be created on the first install of the signed apk, but gets created if I delete the app-data an rerun the signed apk. Gets created every time on the emulator and dubug.apk on phone.

I've tried:

String selectQuery = "SELECT * FROM " + MY_TABLE + " WHERE " + LIST_ID + "=" + listId + " ORDER BY " + NAME + "," + CONC + " ASC";
Cursor cursor = db.rawQuery(selectQuery, null);

and modified it a little bit for debugging:

String selectQuery = "SELECT * FROM " + MY_TABLE + " WHERE " + ID + "=?" + LIST_ID + "=?" + " ORDER BY " + NAME + "," + CONC + " ASC";
Cursor cursor = db.rawQuery(selectQuery, new String[] {id, listId});

with the same error as a result. Tried different columns, but listId is the column that causes the error in all cases. But only when running the signed apk on my phone. My phone is a Samsung S6 if that means anythig.

As a debugging measure I've tried to force creation of the database before my app does anything with it with this code

SQLiteDatabase db = this.getWriteableDatabase();

which show me that the database is NOT created on first install. Actually it seems like there exists an old version of the database despite my uninstall efforts. Hence, I increased the database version and the onUpgrade() was invoked on first install!

It seems like the database is not created correctly on first install as signed apk or not removed properly with uninstall.

I can not seem to find the error in my code or elsewehere, I'm starting to go blind, but I hope someone else can see the bug or has experienced anything like this before and can push me in the right direction.

user1086500
  • 380
  • 5
  • 20
  • Have you made sure you've deleted the app and reinstalled it to make sure no old version data is the cause of the problem? – Morrison Chang Jan 02 '17 at 01:08
  • Yes, many times. Over and over again. It's only the signed build that acts up. – user1086500 Jan 02 '17 at 01:19
  • Log every sql create/select statement starting from a clean install. At worst post those lines up to the error (if possible) - perhaps someone can spot something. – Morrison Chang Jan 02 '17 at 01:32
  • 2
    Found the answer to my problem. As I said in my question. it seemd like the database was not removed when the app was unistalled, but when then app-data was deleted. By default in Android 6.0 this little thing is set to true, android:allowBackup="true", which actually backup the database on uninstall and retrives it on resintall! After android:allowBackup="false", it works! – user1086500 Jan 02 '17 at 13:42
  • Wow! This took me ages to find. I thought uninstalling the app would remove the database. I had no idea this flag in the manifest was preventing that from happening. Thanks! – Anthony. Jan 04 '19 at 15:32
  • I feel you, @Anthony. I've actually answered my own question, see below, and feel free to upvote it! :-) – user1086500 Jan 04 '19 at 15:58

2 Answers2

5

I'll answer my ownn question here.

I created a signed apk in order to test it on my phone and how it worked when upgrading from previous versions.

On my phone I also wanted to try a "clean" install, uninstall and reinstall, to see how that worked. This however resulted in the error described in the question.

The reason for this is that Android 6.0 has allowBackup=true in the manifest.xml. This means that when you uninstall an app, all data is backup and retrived when you reinstall the app. See https://developer.android.com/guide/topics/data/autobackup.html

Normally this is a good thing, but when debugging my app, I had not taken this into concideration. This feature made my app skip both onCreate() and onUpgrade() since the backed up database is the same version, but with some modifications, as the version in the app I tried to install. And when I tried to read a column that neither onCreate() or onUpgrade() has created, I got my error.

user1086500
  • 380
  • 5
  • 20
0

Might be just a typo but you have

The mytable is created by running db.execSQL(DATABASE_CREATE_MY_TABLE )

and 

private static final String DATABASE_CREATE_MYTABLE = "CREATE TABLE "
    + MY_TABLE + "("             
    + ID + " INTEGER, "
    + LIST_ID + " INTEGER, "
    + NAME + " STRING, "
    + CONC + " REAL, "
    + "PRIMARY KEY (" + ID + "," + LIST_ID + ")"
    + ")"

Notice the extra underscore in the first variable

Isaac
  • 1,442
  • 17
  • 26
  • It was a type in my question, not the real code! ;-) However, my code works perfectly in the emulator and in a apk-debug.apk build, but not when the build is signed... – user1086500 Jan 02 '17 at 01:02