Insert from attach datebase (no such table)

[pecet86]

Hello I found an interesting bug that does not occur on the standard android library.

The point is that when I attach the second base, only main SELECT works, but not sub SELECT

protected final static GraphSearchDatabase buildRoomDb(Context context) {
        return Room.databaseBuilder(context, GraphSearchDatabase.class, "GraphSearch.db")
                .fallbackToDestructiveMigration()
                .openHelperFactory(new **RequerySQLiteOpenHelperFactory()**)
                .build();
    }

    public static synchronized void working(SupportSQLiteDatabase db) {
        Cursor c = db.query("SELECT * FROM db2.sqlite_master WHERE type = 'table';");
        List<Map<String, Object>> rows = toList(c);

        Cursor c2 = db.query("SELECT * FROM db2.UPDATED_FEATURES;");
        List<Map<String, Object>> rows2 = toList(c2);
    }

    public static synchronized void notWorking(SupportSQLiteDatabase db, String alians) {
        boolean ok = false;
        try {
            db.beginTransaction();

            db.execSQL("DELETE FROM FEATURES WHERE FEATURE_ID IN (SELECT FEATURE_ID FROM " + alians + ".DELETED_FEATURES);");

            db.execSQL("INSERT INTO FEATURES(FEATURE_ID, PROPERTIES) " +
                    "SELECT FEATURE_ID, PROPERTIES FROM " + alians + ".ADDED_FEATURES;");

            db.setTransactionSuccessful();
            ok = true;
        } catch (SQLException ex) {
            log("updateIncrement", ex.getMessage(), ex);
        } finally {
            db.endTransaction();
        }
    }

public static synchronized boolean updateIncrement(Context context) {
        SupportSQLiteDatabase db = INSTANCE.getOpenHelper().getWritableDatabase();

        String path = context.getDatabasePath("GraphSearch2.db").getPath();
        String alians = "db2";

        db.execSQL("ATTACH DATABASE '" + path + "' AS " + alians);

        working(db, alians);

        notWorking(db, alians);

        db.execSQL("DETACH DATABASE " + alians);

        return ok;
    }

[andob]

experiencing the same issue, insert into main.table(....) select .... from slave.table does not work.

[andob]

will probably try to fix it myself, open a pull request.

[andob]

can't figure out where is the bug and even how to replicate it (sometimes it works, sometimes it doesn't). I resorted to system sqlite to clone the table, then use requery sqlite for all other things.

[tuomas2]

Any solution to this? I tried to use sqlite-android but am facing this issue consistently on my project.

[tuomas2]

Tested with Room version 2.5.1 and sqlite-android version 3.42.0.

I found a workaround. I'm using Room primarily to access databases. When I need ATTACH to work properly, I found out that if I close room database and then open new database connection with SQLiteDatabase.openDatabase from sqlite-android, then everything works. After I have done needed actions, I close that db and reset room database in database container.

So something like this works:

roomDb.close()
val newDb = 
SQLiteDatabase.openDatabase(application.getDatabasePath(dbFileName).absolutePath, null, SQLiteDatabase.OPEN_READWRITE)

newDb.use { db -> db.run {
    execSQL("ATTACH DATABASE '${patchDbFile.absolutePath}' AS patch")
    // Do queries here
    execSQL("DETACH DATABASE patch")
}}
resetRoomDb()

Furthermore, I noticed that just closing and opening db does not work, if I use Room to initialize my DB.

So this does not:

roomDb.close()
val newDb = Room.databaseBuilder(
        application, MyDatabase::class.java, filename
    )
        .allowMainThreadQueries()
        .addMigrations(*myMigrations)
        .openHelperFactory(RequerySQLiteOpenHelperFactory())
        .build()

newDb.openHelper.writableDatabase.use { db -> db.run {
    execSQL("ATTACH DATABASE '${patchDbFile.absolutePath}' AS patch")
    // Do queries here
    execSQL("DETACH DATABASE patch")
}}
resetRoomDb()

Tested also that it is not directly related to usage of RequerySQliteOpenHelperFactory. This works too:

roomDb.close()

val fac = RequerySQLiteOpenHelperFactory()
val cb = object: SupportSQLiteOpenHelper.Callback(1) {
    override fun onCreate(db: SupportSQLiteDatabase) {}
    override fun onUpgrade(db: SupportSQLiteDatabase, oldVersion: Int, newVersion: Int) {}
}
val facConfig = SupportSQLiteOpenHelper.Configuration.builder(application)
    .name(filename)
    .callback(cb)
    .build();
val helper = fac.create(facConfig)

helper.writableDatabase.use { db -> db.run {
    execSQL("ATTACH DATABASE '${patchDbFile.absolutePath}' AS patch")
    // Do queries here
    execSQL("DETACH DATABASE patch")
}}
resetRoomDb()

Concluding there's something different how Room initializes the DB that triggers this to fail.

[tuomas2]

Ultimate workaround: Set Journal mode to TRUNCATE

I found out that if you use TRUNCATE (instead of AUTOMATIC or WRITE_AHEAD_LOGGING) journal mode when building Room database, everything works.

Room.databaseBuilder(
    application, MyDatabase::class.java, filename
)
    .allowMainThreadQueries()
    .addMigrations(*myMigrations)
    .setJournalMode(RoomDatabase.JournalMode.TRUNCATE)
    .openHelperFactory(RequerySQLiteOpenHelperFactory())
    .build()