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]

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