[h2] To which H2 version should we update in productive environments

2016-01-07 Thread Reinhold Bihler 
Hello,

we are successfully using H2 in one of our products for more then 3 years 
now. As customers are using our product with more load and parallel 
requests to the H2 db we experienced the "H2 LOB bug" reported here 
. We are 
currently using H2 Version 1.3.164. For testing we upgraded to version 
1.4.187 and do no longer experience the "H2 LOB bug". Just now we saw that 
1.4 is still marked as beta.

So, to what H2 version should we update?

Note: We do have several customers with existing H2 1.3.164 databases which 
we need to update as seamless as possible.

Thanks in advance for your response and great work!

Reinhold

-- 
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.

[h2] Re: To which H2 version should we update in productive environments

2016-01-11 Thread Reinhold Bihler 
Thanks for your feedback.

We decided to keep 1.4.187 with ";mv_store=false" and will do heavy load 
tests for another week. Up to now no more H2 related issues did occur.

Are there important fixes in 1.4.190 compared to 1.4.87 that would justify 
a update in the late testing phase of a product release?


-- 
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.

[h2] Other way to throw away garbage of not closed transactions?

2016-07-19 Thread Reinhold Bihler 
Hello,

our application code did contain programming errors that prevented 
committing of some Hibernate transactions and the closing of Hibernate 
sessions. With H2 Version 1.4.191 (MV_STORE=FALSE) this caused a rapid 
growth in database file size. We did not see this growth issue using H2 
Version 1.3.164.

Using the SCRIPT and RUNSCRIPT commands we can bring the DB size back to 
normal. Is there another way to tell H2 to throw away the garbage of the 
transactions that have not been closed?

Reinhold

-- 
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.

[h2] Data stored in the INFORMATION_SCHEMA.LOBS and INFORMATION_SCHEMA.LOB_MAP tables seems to blow up our database

2016-11-17 Thread Reinhold Bihler 
Hello,

we are using H2 version 1.4.192. Database URL is jdbc:h2:file:;CIPHER=AES;MV_STORE=false.

The size of the database file is growing dramatically in some environments 
and we do not understand why. We checked our code for errors that prevented 
committing of Hibernate transactions and the closing of Hibernate sessions. 
Indeed we did find some issues and corrected them. However, the database 
file size is still growing dramatically.

We can reduce the file size by using the SCRIPT and RUNSCRIPT commands. The 
reduced database works fine except that it starts growing again.

We run the recovery tool on a database file that has grown big and on a 
database file that was reduced. Comparing the recovery tool output of the 
two database files we see that the output is a lot bigger for the grown 
database.
95% of the lines in the output are of the form

INSERT INTO O_6 VALUES(5154, 0, 0, 441076005, 1865);
..
INSERT INTO O_2 VALUES(5416, 821, 79);
..

Both outputs do contain the following lines

..
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;
..

So it seems that entries in the INFORMATION_SCHEMA.LOBS and 
INFORMATION_SCHEMA.LOB_MAP cause the growing of the database. What data is 
stored in this tables?

I attached excerpts of the output of the recovery tool.

Regards,
Reinhold

-- 
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.
Lot's of entries of the following kind:

..
INSERT INTO O_2 VALUES(151187297, 821, 79);
INSERT INTO O_2 VALUES(151187300, 821, 79);
INSERT INTO O_2 VALUES(151187309, 821, 79);
INSERT INTO O_2 VALUES(151187312, 821, 79);
INSERT INTO O_2 VALUES(151187321, 821, 79);
INSERT INTO O_2 VALUES(151187324, 821, 79);
INSERT INTO O_2 VALUES(151187333, 821, 79);
INSERT INTO O_2 VALUES(151187336, 821, 79);
INSERT INTO O_2 VALUES(151187345, 821, 79);
INSERT INTO O_2 VALUES(151187348, 821, 79);
INSERT INTO O_2 VALUES(151187357, 821, 79);
INSERT INTO O_2 VALUES(151187360, 821, 79);
INSERT INTO O_2 VALUES(151187369, 821, 79);
INSERT INTO O_2 VALUES(151187372, 821, 79);
INSERT INTO O_2 VALUES(151187381, 821, 79);
-- page 354325: b-tree leaf (last) parent: 344185 index: 9 entries: 19
-- page 354326: data leaf (last) parent: 354356 table: 6 entries: 20 columns: 5
INSERT INTO O_6 VALUES(151187321, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187324, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187333, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187336, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187345, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187348, 0, 0, -1683029071, 5997);
INSERT INTO O_6 VALUES(151187357, 0, 0, -1683029071, 5997);
INSERT INTO O_6 VALUES(151187360, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187369, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187372, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187381, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187384, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187393, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187396, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187405, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187408, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187417, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187420, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187429, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187432, 0, 0, -1596552293, 5995);
-- page 354327: b-tree leaf (last) parent: 346064 index: 9 entries: 6
-- page 354328: data leaf (last) parent: 353976 table: 2 entries: 4 columns: 3
INSERT INTO O_2 VALUES(151053475, 821, 79);
INSERT INTO O_2 VALUES(151053484, 821, 79);
INSERT INTO O_2 VALUES(151053487, 821, 79);
INSERT INTO O_2 VALUES(151053495, 821, 79);
-- page 354329: data leaf (last) parent: 354356 table: 6 entries: 4 columns: 5
INSERT INTO O_6 VALUES(151053475, 0, 0, 289245169, 5983);
INSERT INTO O_6 VALUES(151053484, 0, 0, 289245169, 5983);
INSERT INTO O_6 VALUES(151053487, 0, 0, 923649667, 5984);
INSERT INTO O_6 VALUES(151053495, 0, 0, 923649667, 5984);
-- page 354330: b-tree leaf (last) parent: 356289 index: 7 entries: 25
-- page 354331: b-tree leaf (last) parent: 356289 index: 7 entries: 33
-- page 354332: data leaf (last) parent: 355336 table: 2 entries: 20 columns: 3
INSERT INTO O_2 VALUES(151187384, 821, 79);
INSERT INTO O_2 VALUES(151187393, 821, 79);
..

DELETE FROM INFORMATION_SCHEMA.LOBS;
INSERT INTO INFORMATION_SCHEMA.LOBS SELECT * FR

Re: [h2] Data stored in the INFORMATION_SCHEMA.LOBS and INFORMATION_SCHEMA.LOB_MAP tables seems to blow up our database

2016-11-21 Thread Reinhold Bihler 
Thanks Noel for you answer. We will try to track down the code / SQL 
statement that causes the growth. What is in your opinion the best approach 
to do so?

I was thinking to write some code that monitors the database file size and 
logs any size change. I would also log any SQL statement sent to the 
database. Doing so I hope we can see a relation between a SQL statement and 
the file growth.

-- 
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] Data stored in the INFORMATION_SCHEMA.LOBS and INFORMATION_SCHEMA.LOB_MAP tables seems to blow up our database

2016-11-24 Thread Reinhold Bihler 
I did write some code that monitors the database file size. But there seems 
to be no clear relationship between insert and delete queries and the size 
of the database file. Database file size changes seem to happen very rarely.

Is this because we use hibernate or is it a general behavior of H2? Is the 
approach to monitor the file size promising?

Another approach would be to monitor the count of entries in the 
INFORMATION_SCHEMA.LOBS and INFORMATION_SCHEMA.LOB_MAP. What do you think? 
Does this sound more promising?

-- 
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.