Re: [h2] Recovery tool does not handle LOBs correctly (explanation and fix is proposed)

2017-08-09 Thread Sai K 
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.

Re: [h2] Recovery tool does not handle LOBs correctly (explanation and fix is proposed)

2017-08-04 Thread Sai K 
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)

2017-02-12 Thread Thomas Mueller Graf 
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)

2017-02-02 Thread LRichard 
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)

2016-10-16 Thread Vitali 

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

Re: [h2] Recovery tool does not handle LOBs correctly (explanation and fix is proposed)

2016-07-19 Thread Thomas Mueller Graf 
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);
>