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]> 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] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[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.
