"android.database.sqlite.SQLiteException: no such table" error in rare cases

Question

I have an app currently available in the app store, but there is one type of error report that I can't seem to figure out completely. My app uses an internal sqlite database, but on some devices (certainly not the majority) the following error sometimes occurs:

android.database.sqlite.SQLiteException: no such table: image_data (code 1): , while compiling: SELECT Min(stamp) FROM image_data WHERE category = 'Astronomy' AND stamp >= 1357426800 and coalesce(title_nl, '') = ''

I am sure this table exists and I'm sure this query is correct. I know that this error only occurs in the widgets of the app and in a BroadcastReceiver fired by the AlarmManager (the app once in a while tries to download new entries, as it's a picture of the day app).

I think it has something to do with the Context I am in when accessing the database. I have a class called AppContextHelper which extends Application and has a static member in which I store the context. That context is always used when accessing the database.

My question: could it be that THAT context is invalid in some cases when acquiring the database in a widget or the aforementioned BroadcastReceiver fired by the AlarmManager and that in that case I should use the provided Context in favor of the 'application' context? If so, why is that context invalid or better yet, which context is it that is provided?

Thanks in advance!

As requested the code leading to the problem, again, only on SOME devices and ONLY in the widgets or AlarmManager class. I will post the code leading to the error in the AlarmManager class (that is the code with the least lines)

The code initializing the alarm:

    Intent myIntent = new Intent(AppContextHelper.getContext(), ApodDownloader.class);
    mPendingIntent = PendingIntent.getBroadcast(AppContextHelper.getContext(), 0, myIntent, 0);

    AlarmManager alarmManager = (AlarmManager)AppContextHelper.getContext().getSystemService(Context.ALARM_SERVICE);
    Calendar calendar = Calendar.getInstance();
    calendar.setTimeInMillis(System.currentTimeMillis());

    alarmManager.setRepeating(AlarmManager.RTC_WAKEUP, calendar.getTimeInMillis() + 10000, AlarmManager.INTERVAL_HOUR, mPendingIntent);

AppContextHelper.java

public class AppContextHelper extends Application {

    private static Context mContext;

    @Override
    public void onCreate() {
        super.onCreate();
        mContext = this;
    }

    public static Context getContext(){
        return mContext;
    }
}

(part of) ApodDownloader.java (this contains the line of the exception being thrown)

@Override
public void onReceive(Context arg0, Intent arg1) {
    (new AsyncTaskThreadPool<Integer, Void, Boolean>() {

        @Override
        protected Boolean doInBackground(Integer... params) {
            Helpers.logMessage("Checking new entries.");


            SQLiteDatabase db = FrescoDatabase.acquireReadableDatabase(AppContextHelper.getContext());
            try {
                >>> THIS LINE THROWS THE EXCEPTION <<<
                maxStamp = Helpers.executeScalarLong(db, "SELECT Min(stamp) FROM image_data WHERE category = 'Astronomy' AND stamp >= 1357426800 and coalesce(title_nl, '') = ''");

                [...]
            } finally {
                FrescoDatabase.releaseReadableDatabase();
            }

            [...] more code

        }
        [...] onPostExecute
    }).executeOnExecutor(AsyncTaskThreadPool.THREAD_POOL_EXECUTOR, 0);

FrescoDatabase.java The database is automatically generated by the app on startup, this code is working, also on the devices that fire the exception. I cannot emphasize enough that the database exists on the problematic devices, since app is running flawlessly with the exception of the widgets and BroadcastReceiver of the AlarmManager, so please don't tell me the db is not initialized correctly :)

public class FrescoDatabase extends SQLiteOpenHelper {

    public static final String[] OBSOLETE_DATABASE_FILE_NAMES = new String[] { "Fresco.v1.sqlite", "Fresco.v2.sqlite", "Fresco.v3.sqlite", "Fresco.v4.sqlite", "Fresco.v5.sqlite" };
    public static final String DATABASE_FILE_NAME = "Fresco.v6.sqlite";
    public  static final int DATABASE_FILE_SIZE = 15302656;
    private static final int DATABASE_VERSION = 7;

    private static final Lock writeLock = new ReentrantLock();
    private static SQLiteDatabase currentDB = null;

    public static final SQLiteDatabase acquireWritableDatabase(Context c) {
        writeLock.lock();
        currentDB = new FrescoDatabase(c).getWritableDatabase();
        return currentDB;
    }

    public static final void releaseWritableDatabase() {
        currentDB.close();
        currentDB = null;
        writeLock.unlock();
    }

    public static final SQLiteDatabase acquireReadableDatabase(Context c) {
        writeLock.lock();
        currentDB = new FrescoDatabase(c).getReadableDatabase();
        return currentDB;
    }

    public static final void releaseReadableDatabase() {
        currentDB.close();
        currentDB = null;
        writeLock.unlock();
    }

    private FrescoDatabase(Context context) {
        super(context, InitializeFrescoDatabaseTask.getDatabaseFileName(context), null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // database is automatically generated, this should not be called
    }

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

(part of) Helpers.java

    public class Helpers {
        [...]
        public static long executeScalarLong(SQLiteDatabase db, String query) {
            return executeScalarLong(db, query, new String[] { });
        }
        public static long executeScalarLong(SQLiteDatabase db, String query, String... parameters) {
            (line 85, see stack trace down below) Cursor cursor = db.rawQuery(query, parameters);
            try {
                cursor.moveToNext();
                long val = cursor.getLong(0);
                return val;
            }   
            catch (Exception e) {
                ;
            }
            finally {
                cursor.close();
            }
            return -1;
        }
    }

The exception log (as requested):

            java.lang.RuntimeException: An error occured while executing doInBackground()
                at nl.tagpulse.utils.AsyncTaskThreadPool$3.done(AsyncTaskThreadPool.java:329)
                at java.util.concurrent.FutureTask$Sync.innerSetException(FutureTask.java:273)
                at java.util.concurrent.FutureTask.setException(FutureTask.java:124)
                at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:307)
                at java.util.concurrent.FutureTask.run(FutureTask.java:137)
                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1076)
                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:569)
                at java.lang.Thread.run(Thread.java:856)
                Caused by: android.database.sqlite.SQLiteException: no such table: image_data (code 1): , while compiling: SELECT Min(stamp) FROM image_data WHERE category = 'Astronomy' AND stamp >= 1357426800 and coalesce(title_nl, '') = ''
                at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1012)
                at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:623)
                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:44)
                at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
                at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
                at nl.tagpulse.fresco.other.Helpers.executeScalarLong(Helpers.java:85)
                at nl.tagpulse.fresco.other.Helpers.executeScalarLong(Helpers.java:82)
                at nl.tagpulse.fresco.business.FrescoDatabase.retrieveNewEntries(FrescoDatabase.java:64)
                at nl.tagpulse.fresco.business.ApodDownloader$1.doInBackground(ApodDownloader.java:192)
                at nl.tagpulse.fresco.business.ApodDownloader$1.doInBackground(ApodDownloader.java:1)
                at nl.tagpulse.utils.AsyncTaskThreadPool$2.call(AsyncTaskThreadPool.java:317)
                at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:305)
                ... 4 more

I added a line 85 marker at the Helpers.java block.

Hope this helps!

I've added the stack trace and added the line 85 marker at Helpers.js. Hopefully this will clarify even more :) The table is defined as follows (but I have to add (again) that the code works when not in an event fired by AlarmManager or in the Widgets): `CREATE TABLE "image_data" ( "image_source" TEXT, "category" TEXT, "title" TEXT, "title_nl" TEXT, "description" TEXT, "description_nl" TEXT, "image_thumb" TEXT, "stamp" INTEGER, "random_tag" INTEGER NOT NULL DEFAULT 0, PRIMARY KEY ("image_source","category") )` — Toverbal, Apr 12 '13 at 16:27
I have a similar situation, getting the same exception. Did you find a solution? Do you have details on the Device/version/ROM that you encounter this issue in? — Raanan, Apr 17 '13 at 14:28
@Raanan it seems to happen on a variety of devices. I will try to follow up on my suspicion (the context is wrong), but it will take some time, because I know just one guy who has this problem and he's busy in the coming weeks, so it'll take some time to confirm this (I sure hope I can confirm it though :)) — Toverbal, Apr 17 '13 at 21:22
@Raanan could you provide me with some information about your issue, it might actually reveal a clue (I just discovered you can't contact users privately using this site, that's why I commented it here :)). — Toverbal, Apr 23 '13 at 09:18
So, My app (RefreshMe) is also on Play for sometime, I have a bit of a complicated setup since it's using Services and Activities in different processes so DB is wrapped with a Content Provider. A user has complained that the app isn't working at all, crashes. In his logs the "android.database.sqlite.SQLiteException: no such table" exception is always the cause. I use the onCreate function to create the DB so there is no way to access the DB before the tables are created. so far only one user reported is with Galaxy S2, 4.1.2 using Resurrection Remix JB v3.1.2 ROM. Ideas? — Raanan, Apr 23 '13 at 21:09
Just a wild stab in the dark but did you consider that this might be a permission issue? I could be that the widget is somehow considered outside of the application and therefore cant access the content provider (just a random idea, nothing to back this up) — Corey Scott, Apr 24 '13 at 01:39
In addition to what Corey says, are you an Application context like me, Raanan? I am still suspecting that the application context might be different in services/widgets/alarmmanager events/etc. Problem is that I cannot test it, since the problem is not really reproducable. — Toverbal, Apr 24 '13 at 06:44
In my case I don't think it has something to do with Context as I am accessing the Database trough a ContentProvider. — Raanan, Apr 24 '13 at 13:08
Can you post the InitializeFrescoDatabaseTask.getDatabaseFileName(context) method? — Emanuel Moecklin, Apr 25 '13 at 01:26
@Raanan: you are right about BroadcastReceivers starting after Application.onCreate() has been called. ContentProviders on the other hand can start before the Application.onCreate() is called so maybe in your case there is indeed a racing condition? — Emanuel Moecklin, Apr 25 '13 at 01:27
@Emanuel: I think you just might have hit the spot! That method jumps through a few hoops and then finally calls an overloaded method, which does this: `return (context == null ? "" : "/data/data/" + context.getPackageName() + "/databases/");`. Could it somehow be that context is null? I remember another answer being posted yesterday evening, discussing about when the application context is created, but it seems to have been deleted? — Toverbal, Apr 25 '13 at 06:17
@Toverbal: that was my answer but it was written under the false assumption that like ContentProviders also BroadcastReceivers can be started before the Application.onCreate() is called. In Raanan's case that answer might still be the right one but I'll focus on your case first because there's not enough information about Raanan's problem to give qualified advice. — Emanuel Moecklin, Apr 25 '13 at 12:11
@Toverbal: if context is null then it would certainly not have access to the correct database and could explain the exception your users get. There's more than one scenario I could think of that could explain the context being null e.g. what Corey mentions about permissions or the widget running in a different process (using android:process in your manifest). But this would crash the app reliably not just occasionally. — Emanuel Moecklin, Apr 25 '13 at 12:35
@Toverbal: one thing I noticed is that you're using the application context to create the intent when creating your alarm (new Intent(AppContextHelper.getContext(), ApodDownloader.class);). When doing this you NEED to set the FLAG_ACTIVITY_NEW_TASK or you might get a android.util.AndroidRuntimeException: Calling startActivity() from outside of an Activity context requires the FLAG_ACTIVITY_NEW_TASK flag. Is this really what you want? — Emanuel Moecklin, Apr 25 '13 at 12:38
@Toverbal: I might just have discovered your real issue. You're starting an AsyncTask of some sort in your onReceive(). The documentation is clear on that: — Emanuel Moecklin, Apr 25 '13 at 12:49
"A BroadcastReceiver object is only valid for the duration of the call to onReceive(). Once your code returns from this function, the system considers the object to be finished and no longer active. This has important repercussions to what you can do in an onReceive() implementation: anything that requires asynchronous operation is not available, because you will need to return from the function to handle the asynchronous operation, but at that point the BroadcastReceiver is no longer active and thus the system is free to kill its process before the asynchronous operation completes." — Emanuel Moecklin, Apr 25 '13 at 12:49
That explains why it doesn't happen very often. Android might or might not kill the process. If it does the application context isn't available any longer and thus the db would be created in-memory without any tables. I'll write an answer to your question. — Emanuel Moecklin, Apr 25 '13 at 12:51
@Emanual: wow, what an awesome eye opener! I must admit that I probably have read it once, but I can't remember it and also I wouldn't have read it for a solution to my problem if you hadn't noticed it! When I'm off from work I'll test this theory for my code and I'll let you know if I can find anything that will break it, but I'm actually quite confident that you're absolutely right. I promise I will accept the answer within the bounty time. Thanks again and I'm looking forward to hitting that accept button! — Toverbal, Apr 25 '13 at 13:05

Emanuel Moecklin · Accepted Answer · 2013-04-25T13:12:27.363

Here's what's happening:

ApodDownloader is a BroadcastReceiver and in its onReceive() you start an AsyncTask of some sort which is not allowed. The documentation states:

A BroadcastReceiver object is only valid for the duration of the call to onReceive(Context, Intent). Once your code returns from this function, the system considers the object to be finished and no longer active.

This has important repercussions to what you can do in an onReceive(Context, Intent) implementation: anything that requires asynchronous operation is not available, because you will need to return from the function to handle the asynchronous operation, but at that point the BroadcastReceiver is no longer active and thus the system is free to kill its process before the asynchronous operation completes.

http://developer.android.com/reference/android/content/BroadcastReceiver.html#ReceiverLifecycle

When the process is killed the Application context isn't available any longer. You can still access the AppContextHelper.getContext() but that would be a newly loaded class because the old one was killed when eliminating the process. In other words the context returned would be null. This happens only in the rare case when Android actually kills a process which isn't very often in my experience.

If the Context your AppContextHelper provides hasn't been initialized yet, then your InitializeFrescoDatabaseTask.getDatabaseFileName(context) call returns an empty string and it would naturally open the wrong database. Because you do nothing in onCreate() of your FrescoDatabase class, the database isn't initialized correctly and it won't find the tables. But even if you'd create the tables it would be the the wrong database and although the crashes were gone it wouldn't show any data.

To prevent this from happening you need to do all the work in the same thread the onReceive() is running in or if that takes too long start a service to do the heavy lifting.

One more thing I noticed when analyzing your code is that you're using the application context to create the intent when creating your alarm (new Intent(AppContextHelper.getContext(), ApodDownloader.class);). When doing this you NEED to set the FLAG_ACTIVITY_NEW_TASK or you might get a "android.util.AndroidRuntimeException: Calling startActivity() from outside of an Activity context requires the FLAG_ACTIVITY_NEW_TASK flag. Is this really what you want?" which might or might not crash the app.

I agree that this might be an answer for the issue Toverbal encountered. Doesn't solve my issue but gave me some new ideas to check. — Raanan, Apr 25 '13 at 14:42
@Raanan: unless you provide some information about your project there's really no way to help. Maybe open your own question? — Emanuel Moecklin, Apr 25 '13 at 15:04
Yes I agree, but I will run some more tests first before asking my own question. If your answer is accepted by Toverbal I'll award you the bounty. — Raanan, Apr 25 '13 at 15:40
I've checked everything and all symptoms seem to indicate that this is the issue, thanks for everyone's support in this quest! About the intent in the alarm: I will change the context or use FLAG_ACTIVITY_NEW_TASK when no Context is available, thanks for spotting that! Also, if you open a issue, please make a link here, Raanan, people might read this in the future and then they can jump to your issue immediately :) — Toverbal, Apr 25 '13 at 17:16
You need to use FLAG_ACTIVITY_NEW_TASK whenever you use the Application context instead of an Activity context. I can't tell for sure in what context you set the alarm but if it's not an Activity then you should set that flag. — Emanuel Moecklin, Apr 25 '13 at 17:29
I'm glad I spent so much time finding the right answer to the question without getting the bounty while someone else gets awarded a 25 reputation bounty for a wrong answer. That's a big incentive to answer more questions on SO. — Emanuel Moecklin, Apr 26 '13 at 03:20
@EmanuelMoecklin sorry, I was distracted by personal issues, you should get that bounty at least, ill try and fix the matter. — Raanan, Apr 26 '13 at 09:31

"android.database.sqlite.SQLiteException: no such table" error in rare cases

1 Answers1