0

I wanted to truncated all tables from database. I am using room persistent library. I am calling truncateDatabase() method on button click. It some times do the job successfully, but sometimes produces errors. I have searched a lot but there are very few resources regarding this issue.

I have tried both approaches:

  1. Calling db.clearAllTables();
  2. Deleting using sql query db.settingsDao().deleteAll();

SettingsDao.class file:

@Dao
 public interface SettingsDao {
    ... 
    @Query("DELETE FROM settings")
    void deleteAll();
    ...
}

Here is the code of AppDatabase file:

 @Database(entities = {Settings.class, ....}, version = 1, exportSchema = false)
public abstract class AppDatabase extends RoomDatabase {
    private static final String TAG = "AppDatabase";
    public static String DATABASE_NAME = ....;

    public abstract SettingsDao settingsDao();
    ....

    public static AppDatabase instance;

    public static synchronized AppDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(context.getApplicationContext(),
                    AppDatabase.class, DATABASE_NAME)
                    .fallbackToDestructiveMigration()
                    .addCallback(roomCallback)
                    .build();
        }
        return instance;
    }

    .....

    public void truncateDatabase() {
        Log.d(TAG, "Truncate Database");
        if (isOpen()) {
            Log.d(TAG, "Database is opened");
            new TruncateAndInitializeAsyncTask(instance).execute();
        }
    }

    private class TruncateAndInitializeAsyncTask extends AsyncTask<Void, 
Void, Void> {
        AppDatabase db;

        private TruncateAndInitializeAsyncTask(AppDatabase db) {
            this.db = db;
        }

        @Override
        protected Void doInBackground(Void... voids) {
            Log.d(TAG, "Truncate tables from database "+isOpen()+db.isOpen());
            if (isOpen()) {
                Log.d(TAG, "Database is opened "+db.isOpen());
//                db.clearAllTables();
                try {
                    db.settingsDao().deleteAll();
                    ....
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            return null;
        }

        @Override
        protected void onPostExecute(Void aVoid) {
            super.onPostExecute(aVoid);
            ....
        }
    }
}

Logcat errors are:

D/AppDatabase: Truncate Database
D/AppDatabase: Database is opened
D/AppDatabase: Truncate tables from database truetrue
D/AppDatabase: Database is opened true
E/SQLiteLog: (1) no such table: room_table_modification_log
E/ROOM: Cannot run invalidation tracker. Is the db closed?
android.database.sqlite.SQLiteException: no such table: room_table_modification_log (code 1): , while compiling: INSERT OR IGNORE INTO room_table_modification_log VALUES(3, 0)
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1678)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1608)
    at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.execSQL(FrameworkSQLiteDatabase.java:242)
    at androidx.room.InvalidationTracker.startTrackingTable(InvalidationTracker.java:231)
    at androidx.room.InvalidationTracker.syncTriggers(InvalidationTracker.java:518)
    at androidx.room.RoomDatabase.beginTransaction(RoomDatabase.java:329)
    at ....Database.Dao.SettingsDao_Impl.deleteAll(SettingsDao_Impl.java:130)
    at ....Database.AppDatabase$TruncateAndInitializeAsyncTask.doInBackground(AppDatabase.java:336)
    at ....Database.AppDatabase$TruncateAndInitializeAsyncTask.doInBackground(AppDatabase.java:321)
    at android.os.AsyncTask$2.call(AsyncTask.java:333)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:245)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1162)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:636)
    at java.lang.Thread.run(Thread.java:764)
W/System.err:     java.lang.IllegalStateException: The database '/data/user/0/.../databases/MONEY_MANAGER_DB' is not open.
W/System.err:     at android.database.sqlite.SQLiteDatabase.throwIfNotOpenLocked(SQLiteDatabase.java:2188)
W/System.err:     at android.database.sqlite.SQLiteDatabase.createSession(SQLiteDatabase.java:367)
W/System.err:     at android.database.sqlite.SQLiteDatabase$1.initialValue(SQLiteDatabase.java:86)
E/SQLiteLog: (1) no such table: room_table_modification_log
W/System.err:     at android.database.sqlite.SQLiteDatabase$1.initialValue(SQLiteDatabase.java:84)
W/System.err:     at java.lang.ThreadLocal.setInitialValue(ThreadLocal.java:180)
W/System.err:     at java.lang.ThreadLocal.get(ThreadLocal.java:170)
W/System.err:     at android.database.sqlite.SQLiteDatabase.getThreadSession(SQLiteDatabase.java:361)
W/System.err:     at android.database.sqlite.SQLiteProgram.getSession(SQLiteProgram.java:101)
W/System.err:     at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64)
W/System.err:     at androidx.sqlite.db.framework.FrameworkSQLiteStatement.executeUpdateDelete(FrameworkSQLiteStatement.java:46)
W/System.err:     at ....Database.Dao.SettingsDao_Impl.deleteAll(SettingsDao_Impl.java:132)
W/System.err:     at com....Database.AppDatabase$TruncateAndInitializeAsyncTask.doInBackground(AppDatabase.java:336)
W/System.err:     at com.....Database.AppDatabase$TruncateAndInitializeAsyncTask.doInBackground(AppDatabase.java:321)
W/System.err:     at android.os.AsyncTask$2.call(AsyncTask.java:333)
W/System.err:     at java.util.concurrent.FutureTask.run(FutureTask.java:266)
W/System.err:     at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:245)
W/System.err:     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1162)
W/System.err:     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:636)
E/ROOM: Cannot run invalidation tracker. Is the db closed?
android.database.sqlite.SQLiteException: no such table: room_table_modification_log (code 1): , while compiling: SELECT * FROM room_table_modification_log WHERE invalidated = 1;
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
    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:1318)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1293)
    at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.query(FrameworkSQLiteDatabase.java:161)
    at androidx.room.RoomDatabase.query(RoomDatabase.java:305)
    at androidx.room.InvalidationTracker$1.checkUpdatedTable(InvalidationTracker.java:428)
    at androidx.room.InvalidationTracker$1.run(InvalidationTracker.java:400)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1162)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:636)
    at java.lang.Thread.run(Thread.java:764)
halfer
  • 19,824
  • 17
  • 99
  • 186
Patriotic
  • 2,103
  • 4
  • 26
  • 36
  • Have you come across [Room migration: “no such table: room_table_modification_log”](https://stackoverflow.com/questions/50370683/room-migration-no-such-table-room-table-modification-log) – MikeT May 14 '19 at 21:24
  • I have seen the link. However, I am not doing migration, therefore, this doesn't solve my issue. I have also tried with changing the version but it didn't help. – Patriotic May 15 '19 at 15:57
  • @Patriotic did you manage to solve this? I have similar error – gts13 Sep 13 '21 at 19:40
  • @gts13 I have deleted the database using context.deleteDatabase(DATABASE_NAME); then recreate it using context.openOrCreateDatabase(AppDatabase.DATABASE_NAME, Context.MODE_PRIVATE, null); – Patriotic Sep 15 '21 at 04:38
  • @Patriotic finally I found out yesterday what was my issue. I was deleting app's data/files/cache (programmatically) which means also the database file was deleted and every second time I launched the app it crashed with the above error. I am wrting this here, in case you or someone else makes the same mistake. – gts13 Sep 15 '21 at 07:18
  • I face the exact same issue and this is how I resolved it https://stackoverflow.com/questions/51421312/reopen-room-database/70147223#70147223 – Muazzam A. Nov 28 '21 at 20:53

0 Answers0