[h2] Recovery tool does not handle LOBs correctly (explanation and fix is proposed)
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
Re: [h2] Recovery tool does not handle LOBs correctly (explanation and fix is proposed)
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 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.setRecovery
Re: [h2] Recovery tool does not handle LOBs correctly (explanation and fix is proposed)
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 > > 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 con
Re: [h2] Recovery tool does not handle LOBs correctly (explanation and fix is proposed)
Hi, It would be great if version 1.4.194 could be released in order to integrate the patch. (We have corrupted customer databases and cannot use the latest official h2 jar to recover these databases) Thanks -- 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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Recovery tool does not handle LOBs correctly (explanation and fix is proposed)
Hi, I agree, but releasing H2 will take a few weeks I'm afraid. Regards, Thomas On Thu, Feb 2, 2017 at 12:25 PM, LRichard wrote: > Hi, > > It would be great if version 1.4.194 could be released in order to > integrate the patch. > (We have corrupted customer databases and cannot use the latest official > h2 jar to recover these databases) > > Thanks > > -- > 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 h2-database+unsubscr...@googlegroups.com. > To post to this group, send email to h2-database@googlegroups.com. > 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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Recovery tool does not handle LOBs correctly (explanation and fix is proposed)
Hi Thomas, Iam running into similar issue https://stackoverflow.com/questions/45518411/h2-database-error-while-trying-to-use-runscript Iam trying to recover using 1.4.196 but still getting same error? Is this issue resolved in the latest version? Thanks, Sai On Sunday, February 12, 2017 at 6:58:15 AM UTC-6, Thomas Mueller Graf wrote: > > Hi, > > I agree, but releasing H2 will take a few weeks I'm afraid. > > Regards, > Thomas > > On Thu, Feb 2, 2017 at 12:25 PM, LRichard > wrote: > >> Hi, >> >> It would be great if version 1.4.194 could be released in order to >> integrate the patch. >> (We have corrupted customer databases and cannot use the latest official >> h2 jar to recover these databases) >> >> Thanks >> >> -- >> 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 h2-database...@googlegroups.com . >> To post to this group, send email to h2-da...@googlegroups.com >> . >> 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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Recovery tool does not handle LOBs correctly (explanation and fix is proposed)
Hi Thomas, Sorry to bother, do you have any update on this? On Saturday, August 5, 2017 at 12:23:58 AM UTC-5, Sai K wrote: > > Hi Thomas, > > Iam running into similar issue > > https://stackoverflow.com/questions/45518411/h2-database-error-while-trying-to-use-runscript > Iam trying to recover using 1.4.196 but still getting same error? Is this > issue resolved in the latest version? > > > Thanks, > Sai > > On Sunday, February 12, 2017 at 6:58:15 AM UTC-6, Thomas Mueller Graf > wrote: >> >> Hi, >> >> I agree, but releasing H2 will take a few weeks I'm afraid. >> >> Regards, >> Thomas >> >> On Thu, Feb 2, 2017 at 12:25 PM, LRichard wrote: >> >>> Hi, >>> >>> It would be great if version 1.4.194 could be released in order to >>> integrate the patch. >>> (We have corrupted customer databases and cannot use the latest official >>> h2 jar to recover these databases) >>> >>> Thanks >>> >>> -- >>> 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 h2-database...@googlegroups.com. >>> To post to this group, send email to h2-da...@googlegroups.com. >>> 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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.