Finally, PULL request was created at github.
On Wednesday, July 20, 2016 at 8:49:29 AM UTC+3, Thomas Mueller Graf wrote: > > Hi, > > It would be better if the recovery mechanism does not use an internal > mechanism of H2 (not use the "hidden" INFORMATION_SCHEMA.LOBS tables, and > instead use "real" tables). This is fixed in the MVStore case, where "real" > tables are used (actually just INFORMATION_SCHEMA.LOB_BLOCKS). > > So there are two ways to fix this for the PageStore: use a "real" table as > well (INFORMATION_SCHEMA.LOB_BLOCKS just as for the MVStore case), or your > fix. When using your fix, we would need test cases as well. > > Regards, > Thomas > > > > On Wednesday, July 13, 2016, Vitali <[email protected] <javascript:>> > wrote: > >> Hi. >> >> Recently I have observed that recovery tool strangely breaks LOBs (in >> particular CLOB objects in my case, but same is applicable to BLOB). >> After recovery CLOB values are not what they were in original H2 file >> before recovery process is run. >> >> More than a day of debugging and some understanding came. >> >> It seems other improvements made for LOB types handling affected recovery >> workflow. In particular, functions "READ_CLOB_DB" or "READ_BLOB_DB" do >> not work as they worked long time ago. >> >> >> In recovery script we can see this type of SQL in case when table has >> LOB columns: >> >> >> CREATE TABLE O_1460(C0 CLOB, C1 CLOB); >> INSERT INTO O_1460 VALUES(READ_CLOB_DB(1, 1006), READ_CLOB_DB(2, 3692)); >> >> ... >> CREATE TABLE O_1462(C0 CLOB, C1 CLOB); >> INSERT INTO O_1462 VALUES(READ_CLOB_DB(5, 1006), READ_CLOB_DB(6, 4152)); >> >> >> These are temporary tables populated during recovery. In my case the >> table really looks like (CLOB, CLOB) - it does not matter, main idea is >> that table has some LOB column. >> >> let's continue. >> >> READ_CLOB_DB(5, 1006) leads to Recover.readClobDb which creates >> ValueLobDb object, but inserting this object into table O_1462 calls the >> method LobStorageBackend.copyLob: >> >> long lobId = getNextLobId(); >> String sql = "INSERT INTO " + LOB_MAP + "(LOB, >> SEQ, POS, HASH, BLOCK) " + >> "SELECT ?, SEQ, POS, HASH, BLOCK FROM " + >> LOB_MAP + " WHERE LOB = ?"; >> PreparedStatement prep = prepare(sql); >> prep.setLong(1, lobId); >> prep.setLong(2, oldLobId); >> prep.executeUpdate(); >> reuse(sql, prep); >> >> sql = "INSERT INTO " + LOBS + "(ID, BYTE_COUNT, >> TABLE) " + >> "SELECT ?, BYTE_COUNT, ? FROM " + LOBS + " >> WHERE ID = ?"; >> prep = prepare(sql); >> prep.setLong(1, lobId); >> prep.setLong(2, tableId); >> prep.setLong(3, oldLobId); >> prep.executeUpdate(); >> reuse(sql, prep); >> >> v = ValueLobDb.create(type, database, tableId, >> lobId, null, length); >> >> >> At this moment tables INFORMATION_SCHEMA.LOBS, LOBS_MAP are completely >> empty. >> >> So, it means "getNextLobId()" returns always 1. So, instead of original >> LOBID which is passed in READ_CLOB_DB(6, 4152) we get a LOB reference in >> target table O_1462 that has LOBID=1. >> >> If there are 400 CLOB values in H2 database then all of them gets >> LOBID=1. >> >> When the whole recovery script is run including also logic below >> >> ... >> DELETE FROM INFORMATION_SCHEMA.LOBS; >> INSERT INTO INFORMATION_SCHEMA.LOBS SELECT * FROM O_2; >> UPDATE INFORMATION_SCHEMA.LOBS SET TABLE = -2; >> DELETE FROM INFORMATION_SCHEMA.LOB_MAP; >> INSERT INTO INFORMATION_SCHEMA.LOB_MAP SELECT * FROM O_6; >> DELETE FROM INFORMATION_SCHEMA.LOB_DATA; >> INSERT INTO INFORMATION_SCHEMA.LOB_DATA SELECT * FROM O_10; >> ... >> >> INSERT INTO SYMDS.SYM_ON_I_FOR_SYM_TRNSFRM_TBL_FLD_CONFIG SELECT * FROM >> O_1460; >> ... >> DROP TABLE O_1460; >> ... >> >> >> we get local database where all CLOB values instead of referencing to >> correct INFORMATION_SCHEMA.LOB_DATA blocks as in original H2 file they >> reference same LOB object, that has had LOBID=1. >> >> >> ----------------------------- >> >> Problem is that LobStorageBackend.copyLob: should work differently at >> recovery case because system tables with LOB information is empty. >> >> I did a 1 minute fix that solves the problem: >> >> >> ValueLobDb: >> >> private boolean isRecoveryReference = false; >> public void setRecoveryReference(boolean isRecoveryReference) { >> this.isRecoveryReference = isRecoveryReference; >> } >> >> public boolean isRecoveryReference() { >> return isRecoveryReference; >> } >> >> Recover: >> >> /** >> * INTERNAL >> */ >> public static Value.ValueClob readClobDb(Connection conn, long lobId, >> long precision) { >> DataHandler h = ((JdbcConnection) conn).getSession(). >> getDataHandler(); >> verifyPageStore(h); >> ValueLobDb lob = ValueLobDb.create(Value.CLOB, h, >> LobStorageFrontend.TABLE_TEMP, >> lobId, null, precision); >> lob.setRecoveryReference(true); >> return lob; >> } >> >> /** >> * INTERNAL >> */ >> public static Value.ValueBlob readBlobDb(Connection conn, long lobId, >> long precision) { >> DataHandler h = ((JdbcConnection) conn).getSession(). >> getDataHandler(); >> verifyPageStore(h); >> ValueLobDb lob = ValueLobDb.create(Value.BLOB, h, >> LobStorageFrontend.TABLE_TEMP, >> lobId, null, precision); >> lob.setRecoveryReference(true); >> return lob; >> } >> >> >> And in LobStorageBackend: >> >> @Override >> public ValueLobDb copyLob(ValueLobDb old, int tableId, long length) { >> int type = old.getType(); >> long oldLobId = old.getLobId(); >> assertNotHolds(conn.getSession()); >> // see locking discussion at the top >> synchronized (database) { >> synchronized (conn.getSession()) { >> try { >> init(); >> ValueLobDb v = null; >> if(!old.isRecoveryReference()){ >> long lobId = getNextLobId(); >> String sql = "INSERT INTO " + LOB_MAP + "(LOB, >> SEQ, POS, HASH, BLOCK) " + >> "SELECT ?, SEQ, POS, HASH, BLOCK FROM " + >> LOB_MAP + " WHERE LOB = ?"; >> PreparedStatement prep = prepare(sql); >> prep.setLong(1, lobId); >> prep.setLong(2, oldLobId); >> prep.executeUpdate(); >> reuse(sql, prep); >> >> sql = "INSERT INTO " + LOBS + "(ID, BYTE_COUNT, >> TABLE) " + >> "SELECT ?, BYTE_COUNT, ? FROM " + LOBS + " >> WHERE ID = ?"; >> prep = prepare(sql); >> prep.setLong(1, lobId); >> prep.setLong(2, tableId); >> prep.setLong(3, oldLobId); >> prep.executeUpdate(); >> reuse(sql, prep); >> >> v = ValueLobDb.create(type, database, tableId, >> lobId, null, length); >> }else{ >> //Recovery process, no need to copy LOB using >> normal infrastructure >> v = ValueLobDb.create(type, database, tableId, >> oldLobId, null, length); >> } >> return v; >> } catch (SQLException e) { >> throw DbException.convert(e); >> } >> } >> } >> } >> >> Must be tested more, of course, but seems as a simple solution - handle >> copying of LOB reference at recovery as a reference with original LOBID. >> >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at https://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
