Android Room SQLite Data migration - attempt to re-open an already-closed object

Question

Sorry if this seems like a stupid question. I'm very new to programming (3 weeks). I've added a new Membership table and wrote a migration from v3 to 4, but it doesn't seem to work. I've listed the new table, the code used for the migration as well as the error message I'm getting. I spent several hours trying different things. If someone can point me in the right direction, that would be very much appreciated. Apologize in advance for the poor formatting. Awesome karma will come to anyone that helps.

Update - FIXED!

I finally figured it out. It's with my migration.

database.execSQL("CREATE TABLE Membership (mID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, mName TEXT, mBarcode TEXT, mType TEXT);");

Here's what I learned and the steps I followed. I hope this helps someone else with this cryptic error message.

1. Rooted an emulator using Android SDK Platform Tools - adb root
2. adb pull /data/user/0/com.xxxx/databases/workout-database.db
3. Open database file with DB Browser for SQLite to understand the CREATE TABLE statements made by Room
4. In the create statement Room does a few "extra" things. All INTEGERS are NOT NULL, ADD AUTOINCREMENT to the PRIMARY KEY, decimal numbers are REAL NOT NULL.

BTW - Here's why I think the message happens. If your schema doesn't match perfectly based on your migration, Room reverts back to the original version and closes the database to protect it. Then when it tries to access it again somewhere in the program, it fails with the re-open a closed database error. This is just a guess, but I think it works somewhat like this. Basically fix your migration and everything is good. Another way to try to understand how Room creates databases, you could try to decipher your Database class implementation (click the green button to the left of the abstract Dao statements in the Database class).

Error Log

E/AndroidRuntime: FATAL EXCEPTION: main Process: com.xxxx, PID: 5343
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.xxxx/com.xxxx.MainActivity}: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/user/0/com.xxxx/databases/workout-database
                  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2984)
                  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3045)
                  at android.app.ActivityThread.-wrap14(ActivityThread.java)
                  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1642)
                  at android.os.Handler.dispatchMessage(Handler.java:102)
                  at android.os.Looper.loop(Looper.java:154)
                  at android.app.ActivityThread.main(ActivityThread.java:6776)
                  at java.lang.reflect.Method.invoke(Native Method)
                  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1520)
                  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1410)
               Caused by: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/user/0/com.xxxx/databases/workout-database
                  at android.database.sqlite.SQLiteClosable.acquireReference(SQLiteClosable.java:55)
                  at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:2054)
                  at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:2000)
                  at android.arch.persistence.db.framework.FrameworkSQLiteDatabase.execSQL(FrameworkSQLiteDatabase.java:240)
                  at com.xxxx.data.utils.AppDatabase$1.migrate(AppDatabase.java:47)
                  at android.arch.persistence.room.RoomOpenHelper.onUpgrade(RoomOpenHelper.java:73)
                  at android.arch.persistence.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.onUpgrade(FrameworkSQLiteOpenHelper.java:118)
                  at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:256)
                  at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163)
                  at android.arch.persistence.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.getWritableSupportDatabase(FrameworkSQLiteOpenHelper.java:93)
                  at android.arch.persistence.db.framework.FrameworkSQLiteOpenHelper.getWritableDatabase(FrameworkSQLiteOpenHelper.java:54)
                  at android.arch.persistence.room.RoomDatabase.query(RoomDatabase.java:193)
                  at com.xxxx.data.ExerciseDao_Impl.countExercises(ExerciseDao_Impl.java:275)
                  at com.xxxx.fragments.WorkoutSummaryFragment.onActivityCreated(WorkoutSummaryFragment.java:200)
                  at android.app.Fragment.performActivityCreated(Fragment.java:2361)
                  at android.app.FragmentManagerImpl.moveToState(FragmentManager.java:1014)
                  at android.app.FragmentManagerImpl.moveToState(FragmentManager.java:1171)
                  at android.app.BackStackRecord.run(BackStackRecord.java:815)
                  at android.app.FragmentManagerImpl.execPendingActions(FragmentManager.java:1582)
                  at android.app.FragmentController.execPendingActions(FragmentController.java:372)
                  at android.app.Activity.performStart(Activity.java:6971)
                  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2937)
                  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3045) 
                  at android.app.ActivityThread.-wrap14(ActivityThread.java) 
                  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1642) 
                  at android.os.Handler.dispatchMessage(Handler.java:102) 
                  at android.os.Looper.loop(Looper.java:154) 
                  at android.app.ActivityThread.main(ActivityThread.java:6776) 
                  at java.lang.reflect.Method.invoke(Native Method) 
                  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1520) 
                  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1410) 
Application terminated.

Database class

@Database(entities = {Workout.class, Exercise.class, Membership.class}, version = 4)

public abstract class AppDatabase extends RoomDatabase {

private static AppDatabase INSTANCE;

public abstract WorkoutDao workoutDao();
public abstract ExerciseDao exerciseDao();
public abstract MembershipDao membershipDao();

public static AppDatabase getAppDatabase(Context context) {
    if (INSTANCE == null) {
        INSTANCE =
                Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, "workout-database")
                        //.fallbackToDestructiveMigration()
                        .addMigrations(MIGRATION_3_4)
                        .build();
    }
    return INSTANCE;
}

static final Migration MIGRATION_3_4 = new Migration(3, 4) {
    @Override
    public void migrate(SupportSQLiteDatabase database) {
        // Create the new table
        database.execSQL("CREATE TABLE Membership (mID INTEGER, mName TEXT, mBarcode TEXT, mType TEXT, PRIMARY KEY(mID))");
    }
};

public static void destroyInstance() {
    INSTANCE = null;
}

}

Answer 1

See Update above. The issue was with the migration despite what the error message says