"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)

1. 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);
   

2. 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;
       }
   }
   

3. (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);
   

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

5. (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!

Answer 1

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.