[h2] Re: Error reading existing databases with H2 trunk

[Silvio]

Hi Andrei,

Yes, that is what I was expecting. Makes perfect sense of course.

SQL dump/restore broke when going from 1.4.x to 2.x.x and we found no way 
to work around that. We came up with our own dump/restore format that only 
reuses the DDL part of the old database which seemed to be compatible 
accross versions and uses CSV for the data. As long as the DDL matches we 
should be fine there.

We where not planning of jumping onto an unreleased version. Actually it is 
quite the opposite since we are only allowed to use released versions.

But I read some thing about improvements that I was curious about and 
decided to give it a try on a test server. I guess we will just have to 
wait for the next release.

On Friday, 16 June 2023 at 15:49:12 UTC+2 Andrei Tokar wrote:

> Hi Silvio,
> SQL dump / restore is the only supported way to transition from one 
> version to the other. 
> We are making our best trying to preserve compatibility with older db 
> files, but again, it's on a "best effort" basis.
> Forward compatibility is totaly out of question. 
> And that's I've been talking about released versions. Jumping onto 
> unreleased version is totally "leap of faith", should be no expectations 
> about switching back, or compatibility with future released version. SQL 
> dump/restore should work most of the time though.
>
>
> On Tuesday, June 13, 2023 at 5:45:10 AM UTC-4 Silvio wrote:
>
>> Hello all,
>>
>> I created a H2 test build from current trunk. Using this on a bunch of 
>> databases created with 2.1.214 I get the following error on a rather large 
>> part of them:
>>
>> org.h2.mvstore.MVStoreException: File is corrupted - unable to recover a 
>> valid set of chunks [2.2.219/6]
>>
>> When reverting to 2.1.214 the problem disappears.
>>
>> Do databases have to be dumped/restored for this to work properly? Or is 
>> 2.2.219 supposed to be able to use databases created with 2.1.214?
>>
>>
>>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/6fb69c8d-bd3a-4793-be1c-d1246d9434f8n%40googlegroups.com.

[h2] Error reading existing databases with H2 trunk

[Silvio]

Hello all,

I created a H2 test build from current trunk. Using this on a bunch of 
databases created with 2.1.214 I get the following error on a rather large 
part of them:

org.h2.mvstore.MVStoreException: File is corrupted - unable to recover a 
valid set of chunks [2.2.219/6]

When reverting to 2.1.214 the problem disappears.

Do databases have to be dumped/restored for this to work properly? Or is 
2.2.219 supposed to be able to use databases created with 2.1.214?


-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/478c7046-8f04-4563-a961-f18c3bc3d641n%40googlegroups.com.

Re: [h2] Request for advice

[Silvio]

That is actually a very good idea. We never considered using a UNION here. 
Would H2 be able to use a different index for multiple queries inside a 
single UNION? I was under the impression H2 always uses one index for a 
query but that may no longer be the case. If that is true this could 
improve things a lot.

The suggestion about the temp table creation is also valuable. We have to 
account for multiple users running the same type of queries at the same 
time so we would need a separate table for each of them or complicate 
things by adding additional information to the temp table to distinguish 
the records for separate queries.

On Wednesday, 26 April 2023 at 11:12:19 UTC+2 Noel Grandin wrote:

>
>
> On 4/26/2023 11:03 AM, Silvio wrote:
> > We have some heavy queries that involve selecting records from a base 
> cached table A (~100K records) that satisfy a 
> > quite a number of conditions expressed as
> > 
> > A.PK [NOT] IN (...)
> > 
>
> You could also express those as
>
> A.PK NOT IN ( SubNotCondition1 UNION SubNotCondition2 ... )
> AND A.PK IN ( SubCondition1 UNION SubCondition2 ... )
>
> which should result in less scanning of the second table.
>
> > 
> > We are thinking of using a temporary memory table C that holds primary 
> keys of table A, evaluating the subqueries on B 
> > seperately inserting or removing keys into table C as needed and finally 
> having a single subquery
> > 
>
> That might help, and I have done similar things (on other databases).
>
> You probably want a session-local temporary table, and you probably want 
> to do
>
> CREATE IF NOT EXISTS TEMP1
> TRUNCATE TEMP1
>
> for each query, rather than creating and dropping it, because CREATE/DROP 
> is quite expensive in H2.
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a5d488ea-efc1-4154-b9c1-128ed08408e5n%40googlegroups.com.

Re: [h2] Request for advice

[Silvio]

Unfortunately we are talking about a production situation and using a 
h2-version that is not officially released (for whatever that means) is 
something we are not allowed to do. I would be very eager to check what 
happens with any new upcoming release.

A join would naturally work but I would also expect the subquery to result 
in a similar evaluation path. If there is any reason to expect otherwise we 
would happily use the join.

We initially did a multi-join instead of the subqueries we have now. That 
was beyond slow. We never saw any of those queries come to completion.

On Wednesday, 26 April 2023 at 11:09:58 UTC+2 Andreas Reichel wrote:

> Greetings.
>
> As far as I understand it, Evgenji just committed a change regarding index 
> use in `IN()` clause.
> Maybe try the very lastest GIT Master first.
>
> Also I wonder, why you would not use a JOIN instead of a IN() when you 
> have a list of FK_A from C:
>
> select *
> from a
> inner join c
> on a.pk = c.fk_a
>
> Why use (uncorrelated?) sub-queries?
>
> Best regards
> Andreas
>
> On Wed, 2023-04-26 at 02:03 -0700, Silvio wrote:
>
> We have some heavy queries that involve selecting records from a base 
> cached table A (~100K records) that satisfy a quite a number of conditions 
> expressed as
>
> A.PK [NOT] IN (...)
>
> on a secondary cached table B (~10M) records. Although the subqueries use 
> indexed columns the overall query is very slow.
>
> We are thinking of using a temporary memory table C that holds primary 
> keys of table A, evaluating the subqueries on B seperately inserting or 
> removing keys into table C as needed and finally having a single subquery
>
> A.PK NOT IN (SELECT FK_A FROM C)
>
> Has anoyone ever tried such an approach in H2? Is there any reason to 
> expect an improvement in performance in comparison to the single large 
> query we have now?
>
> -- 
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/h2-database/6e7570af-a74c-4e83-9560-a85cfad1e8d4n%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/h2-database/6e7570af-a74c-4e83-9560-a85cfad1e8d4n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/570f46fc-c14f-4242-8ac8-c8b3e3da9d6bn%40googlegroups.com.

[h2] Request for advice

[Silvio]

We have some heavy queries that involve selecting records from a base 
cached table A (~100K records) that satisfy a quite a number of conditions 
expressed as

A.PK [NOT] IN (...)

on a secondary cached table B (~10M) records. Although the subqueries use 
indexed columns the overall query is very slow.

We are thinking of using a temporary memory table C that holds primary keys 
of table A, evaluating the subqueries on B seperately inserting or removing 
keys into table C as needed and finally having a single subquery

A.PK NOT IN (SELECT FK_A FROM C)

Has anoyone ever tried such an approach in H2? Is there any reason to 
expect an improvement in performance in comparison to the single large 
query we have now?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/6e7570af-a74c-4e83-9560-a85cfad1e8d4n%40googlegroups.com.

[h2] Re: Error converting value

[Silvio]

Thanks Evgenij,

That is clear enough. Intuitively I would have expected a conversion to the 
column type because apart from only having one conversion that would have 
been the only way to use an index on the column. But that is just me.

Lacking standard behavior rejecting the comparison sounds plausible. At 
least that would have failed consistently while this error only occurred 
after non-numerical content ended up in that column and brought down a 
system that had been running for three years.

I guess we will have to explicitly check the column type and do the 
parameter conversion upfront.

Cheers, Silvio

On Friday, 3 February 2023 at 16:06:11 UTC+1 Evgenij Ryazanov wrote:

> Hello!
>
> In the SQL Standard all character strings with universal character set (H2 
> doesn't have any other character sets) are comparable with each other and 
> all numbers are comparable with each other. Comparison operations between 
> different groups of data types aren't described, so there is no required 
> behavior and you cannot assume anything about them, for example, database 
> may reject them all.
>
> De-facto database systems usually allow this comparison and convert 
> character strings to numbers, so there is nothing special in behavior of 
> H2. This behavior is actually useful for the most of cases.
>
> In your case you need to use '12345' instead of 12345 to avoid failures 
> and to allow usage of index (if this column has an index or a primary key 
> or unique constraint).
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/ba39277c-f293-4ae3-b6b3-35040c107f99n%40googlegroups.com.

[h2] Error converting value

[Silvio]

I have a database table CUSTOMERS with a column CUSTOMER_CODE VARCHAR(20). 
This column used to be filled with integral codes. Executing

SELECT COUNT(*) FROM CUSTOMERS WHERE CUSTOMER_CODE = 12345

always used to work fine on the table. Now for some reason a CUSTOMER_CODE 
value 'C' has been inserted in the table. Since then, the same SELECT 
statements generates Error converting 'C' or something similar.

This occurs with both versions 1.4.200 and 2.1.214

It may be that this is conform SQL standards but it seems counter-intuitive 
to me. Why is the INTEGER literal 12345 not converted to VARCHAR but is the 
VARCHAR value in the column converted to INTEGER to evaluate the condition?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/0aa24072-9e02-4d8c-a5bf-0a9299e368afn%40googlegroups.com.

[h2] Re: Error on NaN literal in SQL statement

[Silvio]

Hello Evgenij,

Thanks for the swift response. That is just the information I was looking 
for. Thank you very much.

Kind regards,

Silvio

On Thursday, 26 January 2023 at 15:25:14 UTC+1 Evgenij Ryazanov wrote:

> Hello!
>
> NaN is not a literal, it's just an identifier.
>
> In this case you can pass it as a character string literal:
> INSERT INTO "TABLE"(ID, DOUBLE_COLUMN) VALUES (10, 'NaN');
>
> In more complex cases where data type cannot be determined automatically a 
> cast is needed:
> CAST('NaN' AS DOUBLE PRECISION)
>
> There are two other special values: CAST('Infinity' AS DOUBLE PRECISION) and 
> CAST('-Infinity' AS DOUBLE PRECISION).
>
> REAL and DECFLOAT data types also have these three special values in H2, 
> but all other numeric data types (TINYINT, SMALLINT, INTEGER, BIGINT, and 
> NUMERIC) don't support them.
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/ffdac278-11ed-4b56-89bb-36036db470b5n%40googlegroups.com.

[h2] Error on NaN literal in SQL statement

[Silvio]

Hello,

Inserting Nan values in a DOUBLE column seems to work when the insert/merge 
statement holds a ? placeholder and the NaN value is bound to the 
parameter, like:

INSERT INTO TABLE(ID,DOUBLE_COLUMN) values (?,?)

setParameter 1 => 10
setParameter 2 => Nan

(at least, that seems to have been the case at some time because I have 
tables with Nan values in them that where inserted in the time our 
application used parameters for all values), but 

INSERT INTO TABLE(ID,DOUBLE_COLUMN) values (10,NaN)

throws an error. The application currently uses literals for (amongst 
others) DOUBLE typed values and is now no longer able to copy records from 
one table to the other if the source value is a Nan.

Is there a way to do this or do we have to revert to using a parameter in 
this case?

Cheers

Silvio


-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/f98d82cf-f769-4c51-af64-da3714600f67n%40googlegroups.com.

Re: [h2] Question about JDBC prepared statements in H2

[Silvio]

Thanks Noel. String values are exactly what we where planning to keep as 
parameters.

So in many cases one or more parameters will remain (for string values) but 
most will be replaced by literals since in our case by far most column 
types are numeric, timestamp or UUID.

I was hoping for perhaps some speedup if the query planner could make a 
more optimal query plan based on known values in the SQL string as opposed 
to a more generic query plan that would have to take all possible values 
for one or more parameters into account. If that would never be the case in 
H2 then there is little to no advantage for us in changing anything.

On Monday, 7 November 2022 at 13:28:07 UTC+1 Noel Grandin wrote:

> I prefer PreparedStatement purely so I can avoid thinking about escaping 
> string values.
>
> But if that doesn't worry you, then there is no down side to using 
> Statement for execute only once.
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/1bc0addc-df72-42be-bef4-125a817c20d8n%40googlegroups.com.

[h2] Question about JDBC prepared statements in H2

[Silvio]

Apart from SQL-injection considerations Is there any advantage or 
disadvantage in using PreparedStatement parameters versus literal values 
when the statement will only be executed once?

Our generic database handling code currently uses statement parameters for 
all situations and column types. We are considering changing this to 
literal values in the SQL string for numerical types, timestamp types etc. 
and only use parameters for string values when the statement will be 
executed only once (which we know upfront).

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/f428ad36-1bc4-4e8b-8f2b-c73ac77af742n%40googlegroups.com.

[h2] Re: JDBC getIndexInfo for large table is very slow

[Silvio]

Right! Thanks Evgenij, I will change that immediately.

Thanks again guys.

On Friday, 30 September 2022 at 12:57:50 UTC+2 Evgenij Ryazanov wrote:

> Hello!
>
> You need to pass true as the last argument (approximate). It allows to 
> return a fast approximation instead of exact number of rows in the index.
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/687ad393-014d-4ef4-b507-cf5c02643600n%40googlegroups.com.

Re: [h2] JDBC getIndexInfo for large table is very slow

[Silvio]

BTW: there are usually about 30 tables. Some approaching 100K rows, the big 
one being the only that goes into the millions.

On Friday, 30 September 2022 at 12:52:49 UTC+2 Silvio wrote:

> Thanks Noel,
>
> Also see my other remark. I will look into that table. Actually, we did 
> some stuff directly on the INFORMATION_SCHEMA tables before but the H2 
> versions above 200 had some modified layout/content in there so we switched 
> to the JDBC call for this one.
>
> On Friday, 30 September 2022 at 12:47:12 UTC+2 Noel Grandin wrote:
>
>> How many tables do you have, that should return in milliseconds.
>>
>> You could try rinning the built in profiler (see the docs) and see what 
>> it is doing.
>>
>> You could also directly query the metadata table 
>> INFORMATION_SCHEMA.INDEXES
>>
>>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a98164da-90f2-42af-94c4-c031c23e1e3dn%40googlegroups.com.

Re: [h2] JDBC getIndexInfo for large table is very slow

[Silvio]

Thanks Noel,

Also see my other remark. I will look into that table. Actually, we did 
some stuff directly on the INFORMATION_SCHEMA tables before but the H2 
versions above 200 had some modified layout/content in there so we switched 
to the JDBC call for this one.

On Friday, 30 September 2022 at 12:47:12 UTC+2 Noel Grandin wrote:

> How many tables do you have, that should return in milliseconds.
>
> You could try rinning the built in profiler (see the docs) and see what it 
> is doing.
>
> You could also directly query the metadata table 
> INFORMATION_SCHEMA.INDEXES
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/ee8adec7-182d-4ec8-8eef-b0086e747240n%40googlegroups.com.

[h2] Re: JDBC getIndexInfo for large table is very slow

[Silvio]

I read up on the method and now realize the call returns a number of 
statistics (like CARDINALITY) that is based on the rows in the index. That 
explains the slowness. The problem is that I only need to know which 
indexes there are on each table and do not care about the statistics.

The tables are created dynamically based on meta data extracted from 
application user definitions (surveys). The application offers an admin 
interface that displays existing indexes and allows users to add/remove 
indexes if their use of the datasets (reporting dashboards) requires it.

On Friday, 30 September 2022 at 12:40:06 UTC+2 Silvio wrote:

> I have a largisch database (~6G) which I use in H2 2.1.214 embedded mode 
> on an NVME SSD. I use the JDBC call
>
> rsIdx = con.getMetaData.getIndexInfo(catalog,schema,table,false,false)
>
> to get the indexes for a single table (which is by far the largest in the 
> whole database) that contains about 3.6 million rows. The call takes about 
> 14 seconds to return the index es/segments for the table. On a not-so-fast 
> older SSD the same call takes ~30 seconds.
>
> Is there a reason that this takes so long? Is there a more efficient way 
> to determine which indexes are present on a table?
>
> Thanks,
>
> Silvio
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/ef987e32-ac36-41b0-9cda-1f1c407b972en%40googlegroups.com.

[h2] JDBC getIndexInfo for large table is very slow

[Silvio]

I have a largisch database (~6G) which I use in H2 2.1.214 embedded mode on 
an NVME SSD. I use the JDBC call

rsIdx = con.getMetaData.getIndexInfo(catalog,schema,table,false,false)

to get the indexes for a single table (which is by far the largest in the 
whole database) that contains about 3.6 million rows. The call takes about 
14 seconds to return the index es/segments for the table. On a not-so-fast 
older SSD the same call takes ~30 seconds.

Is there a reason that this takes so long? Is there a more efficient way to 
determine which indexes are present on a table?

Thanks,

Silvio

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/db841c68-feca-465b-9382-ba054c379b97n%40googlegroups.com.

[h2] Re: Question about async: filesystem

[Silvio]

That sounds clear enough. Since I can not guarantee my databases will not 
be accessed from threads that may be interrupted (by the servlet container) 
we have to use async. Thanks for clearing that up.

On Thursday, 28 July 2022 at 14:11:58 UTC+2 Evgenij Ryazanov wrote:

> Hello!
>
> file: and nio: have no special meaning in modern versions of H2, 
> jdbc:h2:file:SOME_PATH, jdbc:h2:nio:SOME_PATH, and plain jdbc:h2:SOME_PATH 
> have exactly the same meaning.
> Unfortunately, invocation of Thread.interrupt() during disk I/O closes the 
> underlying file channel, so it isn't safe to interrupt a thread that 
> executes some database command in embedded persistent database. If you use 
> a separate server process you can interrupt client threads safely.
>
> async: file system uses asynchronous I/O on Windows and it may work a 
> little bit faster, but better performance is not guaranteed for all cases.
> On POSIX systems in simply performs I/O in separate threads, so 
> performance may be slightly reduced. These systems, unlike Windows, don't 
> need any special optimizations for multi-threaded access to the same file.
> In both cases case Thread.interrupt() on thread that works with database 
> doesn't perform disk I/O by itself, so if you cannot prevent interrupts and 
> cannot use a separate server, you need to use this file system abstraction 
> layer.
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/7a57232e-a045-4aff-8b84-fcc1a61fe751n%40googlegroups.com.

[h2] Re: Question about async: filesystem

[Silvio]

And in addition to the above: are there any disadvantages in using async: 
vs file: ?

On Thursday, 28 July 2022 at 13:01:47 UTC+2 Silvio wrote:

> This is listed as experimental but I read some posts where this was 
> advertised as a way to prevent database corruption.
>
> What is the risk in using this instead of file: in a production system?
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/40c96029-1564-4f83-8dad-3a2b0a8390fbn%40googlegroups.com.

[h2] Question about async: filesystem

[Silvio]

This is listed as experimental but I read some posts where this was 
advertised as a way to prevent database corruption.

What is the risk in using this instead of file: in a production system?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/be3beefa-a0b5-4111-aa9d-7e96f0c52e98n%40googlegroups.com.

[h2] Re: Revisiting manipulating CURRENT_TIMESTAMP

[Silvio]

Sorry for the late response, I was down with Covid.

That makes sense, thanks! I would have to look into user defined functions 
but as long as they can make use of connection-specific state (the normal 
CURRENT_TIMESTAMP would have to be used simultaneously on all connections 
except the one performing the redo) this should work nicely.

I agree that this should be handled in the application layer but I have 
quite some legacy code that lacks the abstraction layers of the current 
code base that allows me to handle this there. This trick can help me add 
transparent log/redo functionality to the older parts of our application.

On Thursday, 21 July 2022 at 15:56:26 UTC+2 Evgenij Ryazanov wrote:

> Hello!
>
> You can only add ;BUILTIN_ALIAS_OVERRIDE=TRUE to JDBC URL and create an 
> own user-defined function with that name (CREATE ALIAS 
> "CURRENT_TIMESTAMP" …):
> https://h2database.com/html/commands.html#create_alias
>
> But usually it is better to move that logic from database level to Java 
> persistence level or application level.
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/20d3c247-1fa2-45e5-a3f3-38a97d2aab77n%40googlegroups.com.

[h2] Revisiting manipulating CURRENT_TIMESTAMP

[Silvio]

In a previous post which a later closed as solved I had a side question 
that I would like answered:

Is there a way to tell H2 to resolve CURRENT_TIMESTAMP to some specific 
value when doing insert/merge/update statements with columns that default 
to CURRENT_TIMESTAMP or are set to CURRENT_TIMESTAMP on update?

I would like to change that value for each statement while redoing 
application level database logging.

Kind regards,

Silvio

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/8a2c656d-46ff-4f78-b923-fdd0e43b0387n%40googlegroups.com.

Re: [h2] Question about recover tool

[Silvio]

I expected as much. Thanks.
On Monday, 18 July 2022 at 10:44:28 UTC+2 Noel Grandin wrote:

> On 2022/07/15 2:35 pm, Silvio wrote:
> >
> > 
> > Can the recover tool be manipulated to open a database in some form of 
> "safe mode"? Or is there any other way to read a 
> > database file with the sole purpose of dumping the content of its tables?
> > 
> >
>
> Not at the moment.
>
> The recover tool passes down a flag to make certain parts of the lower 
> level code more tolerant of problems.
>
> Certainly that could be extended to cover more situations.
>
> Patches are welcome :-)
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/e90365ca-d7ba-4e5b-9ae6-f7069cb1e019n%40googlegroups.com.

[h2] Question about recover tool

[Silvio]

Up until now we have been using H2-1.4.200 in production with multiple 
thousands of (mostly smallish) databases. Every now and then we get 
database corruptions. In all but one case, corrupted databases throw an 
exception during opening of the database. Since the recover-tool seems to 
open a database the same way as our application does recovering a corrupted 
database never worked for us since it throws the exact same exceptions...

EXCEPT!!! In all but a few cases opening the corrupted databases worked 
flawlessly using H2-1.4.196. I do not know why that is and just discovered 
it by accident. Therefore I was mostly able to recover/recreate the 
corrupted databases with H2-1.4.196 and from then on use them with 
H2-1.4.200 as if nothing ever happened. Problem solved!

We are now using H2-2.1.214 in our development and test environments. It 
has improved a lot in comparison to H2-1.4.200 and we would like to migrate 
more databases to this version.
However, also with the new version we have run into some database 
corruptions (probably mostly due to how we abuse Java processes during 
development). Just as before with H2-1.4.200 the recover tool would not 
open the corrupted databases so the data was lost indefinitely.

Can the recover tool be manipulated to open a database in some form of 
"safe mode"? Or is there any other way to read a database file with the 
sole purpose of dumping the content of its tables?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/fbe6b35b-7b14-4bd5-abc3-96c1def6e8e8n%40googlegroups.com.

[h2] Re: Question about MERGE statement

[Silvio]

Hi all. Sorry for the noise. I just found the WHEN MATCHED and WHEN NOT 
MATCHED clauses. These will solve my problem nicely. Thanks again.

On Friday, 8 July 2022 at 16:18:46 UTC+2 Silvio wrote:

> Hi all,
>
> This question may be a generic SQL one but since I am using H2 and some 
> special feature may be available I will try my luck here.
>
> I am trying to perform a sequence of MERGE-statements as part of an 
> attempt to repopulate database tables from some form of custom 
> database-logging.
>
> The tables all contain a column
>
> _INSERTED DEFAULT CURRENT_TIMESTAMP
>
> and a column
>
> _UPDATED ON UPDATE  CURRENT_TIMESTAMP
>
> which works great for INSERT, UPDATE and MERGE statements alike when 
> performed in actual time.
>
> My logging contains a timestamp T that tells me when DELETE, INSERT, 
> UPDATE or MERGE statements have executed. When redoing the statements I 
> need to replace the CURRENT_TIMESTAMP values with that specific timestamp. 
> Since I do not think I can temporarily manipulate the value returned by 
> CURRENT_TIMESTAMP I am doing this the hard way:
>
> When doing an INSERT I simply include the _INSERTED column and use T as 
> the value. The same goes for UPDATE statements and the _UPDATED column. 
> This all works fine.
>
> But I get stuck with MERGE statements. Since I can not tell up front if 
> the MERGE will result in an INSERT or and UPDATE I do not know if I need to 
> supply the _INSERTED+_UPDATED (INSERT case) or just the _UPDATED (UPDATE 
> case) columns along with their T value.
>
> Is there any way I can do a MERGE and supply an extra column+value only 
> for the case that an INSERT will take place? Or is there a way I can tell 
> H2 to resolve CURRENT_TIMESTAMP as T and simply leave out both columns?
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/13b39cfc-0430-4201-a3a7-6ae9ec5a4ec6n%40googlegroups.com.

[h2] Question about MERGE statement

[Silvio]

Hi all,

This question may be a generic SQL one but since I am using H2 and some 
special feature may be available I will try my luck here.

I am trying to perform a sequence of MERGE-statements as part of an attempt 
to repopulate database tables from some form of custom database-logging.

The tables all contain a column

_INSERTED DEFAULT CURRENT_TIMESTAMP

and a column

_UPDATED ON UPDATE  CURRENT_TIMESTAMP

which works great for INSERT, UPDATE and MERGE statements alike when 
performed in actual time.

My logging contains a timestamp T that tells me when DELETE, INSERT, UPDATE 
or MERGE statements have executed. When redoing the statements I need to 
replace the CURRENT_TIMESTAMP values with that specific timestamp. Since I 
do not think I can temporarily manipulate the value returned by 
CURRENT_TIMESTAMP I am doing this the hard way:

When doing an INSERT I simply include the _INSERTED column and use T as the 
value. The same goes for UPDATE statements and the _UPDATED column. This 
all works fine.

But I get stuck with MERGE statements. Since I can not tell up front if the 
MERGE will result in an INSERT or and UPDATE I do not know if I need to 
supply the _INSERTED+_UPDATED (INSERT case) or just the _UPDATED (UPDATE 
case) columns along with their T value.

Is there any way I can do a MERGE and supply an extra column+value only for 
the case that an INSERT will take place? Or is there a way I can tell H2 to 
resolve CURRENT_TIMESTAMP as T and simply leave out both columns?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/cc7b0bed-de8c-42c6-93c7-c9fd5f553946n%40googlegroups.com.

[h2] Re: H2 2.1.210 SQL output of SCRIPT not backward compatible with 1.4.200

[Silvio]

Thank you for the info. I think I will resort to just dumping the database 
structure and then writing all table contents in separate CSV files.

On Friday, 28 January 2022 at 03:06:19 UTC+1 Evgenij Ryazanov wrote:

> > did the same for the TIMESTAMP prefix
> You don't need that. H2 1.4.200 is able to read all datetime literals 
> properly.
>
> > U&'Co-effici\00ebnt'
> > which looks like an obscure escape syntax
> H2 always quotes exported character string literals with non-printable on 
> non-ASCII characters, I don't know why.
> H2 1.x uses an own function not compatible with anything, it means script 
> from 1.4 cannot be imported into any other database system.
> H2 2.x uses more compact and more portable standard-compliant syntax, it 
> is also parsed faster. Unfortunately, old unsupported versions of H2 can't 
> read it.
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/5b91ae2c-10b0-44ad-8864-aa0955347730n%40googlegroups.com.

[h2] Re: H2 2.1.210 SQL output of SCRIPT not backward compatible with 1.4.200

[Silvio]

I found the escape syntax in the SQL reference but I did not find anything 
in the change log mentioning this behavior. Am I correct in assuming that 
this was added after H2-1.4.200?

On Thursday, 27 January 2022 at 15:59:31 UTC+1 Silvio wrote:

> Well, that unfortunately does not solve the issue. After pulling out the 
> UUID prefix for UUID-literals (did the same for the TIMESTAMP prefix) the 
> generated SQL contains the following fragment:
>
> U&'Co-effici\00ebnt'
>
> which looks like an obscure escape syntax for an extended character. 
> Trying to import this with H2-1.4.200 fails.
>
> But since I use
>
> SCRIPT TO 'xxx' CHARSET 'UTF-8' 
>
> I would expect no such escaping to be present in the file.  Is there 
> anything I can do to get rid of these escapes?
> On Thursday, 20 January 2022 at 10:52:15 UTC+1 Silvio wrote:
>
>> Thank you. I already expected that. I did not seek full backward 
>> compatibility but since we use the database as a very plain table storage 
>> without foreign keys, complex joins etc we can live with compatibility for 
>> basic CREATE TABLE and INSERT statements only. So far we never had version 
>> differences manifest themselves in the SQL and I was hoping there was some 
>> kind of workaround.
>>
>>
>> On Thursday, 20 January 2022 at 06:14:42 UTC+1 Evgenij Ryazanov wrote:
>>
>>> Hello!
>>>
>>> H2 doesn't support and never supported downgrades. The oldest version 
>>> that can open database files or SQL scripts from 2.1.210 is H2 2.0.202.
>>>
>>> You need to edit the exported SQL by yourself. For example, you can use 
>>> a stream editor:
>>>
>>> sed "s/UUID '/'/" source.sql > target.sql
>>>
>>> But due to huge number of differences you may need to also edit many 
>>> other declarations.
>>>
>>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/3a8f3deb-c147-45df-8503-065edaa3eb6bn%40googlegroups.com.

[h2] Re: H2 2.1.210 SQL output of SCRIPT not backward compatible with 1.4.200

[Silvio]

Well, that unfortunately does not solve the issue. After pulling out the 
UUID prefix for UUID-literals (did the same for the TIMESTAMP prefix) the 
generated SQL contains the following fragment:

U&'Co-effici\00ebnt'

which looks like an obscure escape syntax for an extended character. Trying 
to import this with H2-1.4.200 fails.

But since I use

SCRIPT TO 'xxx' CHARSET 'UTF-8' 

I would expect no such escaping to be present in the file.  Is there 
anything I can do to get rid of these escapes?
On Thursday, 20 January 2022 at 10:52:15 UTC+1 Silvio wrote:

> Thank you. I already expected that. I did not seek full backward 
> compatibility but since we use the database as a very plain table storage 
> without foreign keys, complex joins etc we can live with compatibility for 
> basic CREATE TABLE and INSERT statements only. So far we never had version 
> differences manifest themselves in the SQL and I was hoping there was some 
> kind of workaround.
>
>
> On Thursday, 20 January 2022 at 06:14:42 UTC+1 Evgenij Ryazanov wrote:
>
>> Hello!
>>
>> H2 doesn't support and never supported downgrades. The oldest version 
>> that can open database files or SQL scripts from 2.1.210 is H2 2.0.202.
>>
>> You need to edit the exported SQL by yourself. For example, you can use a 
>> stream editor:
>>
>> sed "s/UUID '/'/" source.sql > target.sql
>>
>> But due to huge number of differences you may need to also edit many 
>> other declarations.
>>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/2ca36dcb-95af-4b61-837a-3e538391e8f2n%40googlegroups.com.

[h2] Re: H2 2.1.210 SQL output of SCRIPT not backward compatible with 1.4.200

[Silvio]

Thank you. I already expected that. I did not seek full backward 
compatibility but since we use the database as a very plain table storage 
without foreign keys, complex joins etc we can live with compatibility for 
basic CREATE TABLE and INSERT statements only. So far we never had version 
differences manifest themselves in the SQL and I was hoping there was some 
kind of workaround.


On Thursday, 20 January 2022 at 06:14:42 UTC+1 Evgenij Ryazanov wrote:

> Hello!
>
> H2 doesn't support and never supported downgrades. The oldest version that 
> can open database files or SQL scripts from 2.1.210 is H2 2.0.202.
>
> You need to edit the exported SQL by yourself. For example, you can use a 
> stream editor:
>
> sed "s/UUID '/'/" source.sql > target.sql
>
> But due to huge number of differences you may need to also edit many other 
> declarations.
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/87efcf43-6429-438c-9009-c5b1bf1dfa06n%40googlegroups.com.

[h2] H2 2.1.210 SQL output of SCRIPT not backward compatible with 1.4.200

[Silvio]

Hello all,

Executing SCRIPT TO command from H2 2.1.210 generates an SQL file that can 
not be processed by an application using 1.4.200. The culprit seems to be 
that in INSERT statements UUID values are exported in their hex-string 
format prefix by the UUID keyword. So it looks like

UUID '----'

instead of plain

'----'

which breaks 1.4.200.

Although most of our conversions are going the other way (which does work) 
we can currently not prevent having to move a database from a server 
running 210 to one that still runs 200. Is there a flag or something that 
can make 210 generate the legacy syntax?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/e97ad691-660e-4694-b825-649b3707ddbbn%40googlegroups.com.

[h2] Re: H2DB upgrade from 1.4.195 to 2.0.206 - Breaks - Version mismatch, driver version is "0" but server version is "16"

[Silvio]

Thanks. That is just what we need.

On Tuesday, 11 January 2022 at 14:19:29 UTC+1 Evgenij Ryazanov wrote:

> On Tuesday, 11 January 2022 at 20:37:40 UTC+8 Silvio wrote:
>
>> Can you elaborate on that? As I described in another recent post we plan 
>> to use at least two H2 versions (1.4.200 for legacy databases and 2.0.20x 
>> for all new ones) from the same application by using 2.0.20x in the 
>> application itself (embedded mode) and spawn a separate H2 TCP-server 
>> process that uses 1.4.200.
>
> Client with H2 2.0.206 should be able to connect to servers with H2 
> 1.4.197 and newer versions.
>
> It cannot be used with older servers (1.4.196 and below).
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/450e48c8-b255-4c49-87ca-4b95c2bef3bdn%40googlegroups.com.

[h2] Re: H2DB upgrade from 1.4.195 to 2.0.206 - Breaks - Version mismatch, driver version is "0" but server version is "16"

[Silvio]

Can you elaborate on that? As I described in another recent post we plan to 
use at least two H2 versions (1.4.200 for legacy databases and 2.0.20x for 
all new ones) from the same application by using 2.0.20x in the application 
itself (embedded mode) and spawn a separate H2 TCP-server process that uses 
1.4.200.

That means we will use 2.0.20x as the client for a 1.4.200 server. Is that 
not going to work? Or is backward compatibility from a newer H2 client to a 
(somewhat) older H2 version server likely?


On Monday, 10 January 2022 at 10:03:49 UTC+1 Evgenij Ryazanov wrote:

> Hello!
>
> You still have 1.4.195 somewhere.
>
> TCP protocol in H2 2.0.206 provides backward compatibility for 1.4.197 and 
> newer versions only.
>
> Client with 1.4.195 cannot connect to server with 2.0.206 and client with 
> 2.0.206 cannot connect to server with 1.4.195; these versions are too 
> different from each other.
>
> Normally you should have the same version of H2 on both client and server 
> side (or in all applications that use auto-server mode).
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/98af5ba8-379f-4e07-8849-e7e694c8629en%40googlegroups.com.

[h2] Re: Corrupted databases with 1.4.199

[Silvio]

Download H2 1.4.196 and try to do a record of the database with that by 
running

java -cp h2-1.4.196.jar org.h2.tools.Recover

from the same directory as a copy of the database file. For me this works 
in 90% of all cases. If it does not and an exception is thrown then you are 
out of luck. Otherwise do

mv database.mv.db database.mv.db.backup
java -cp h2-1.4.196.jar org.h2.tools.RunScript -url jdbc:h2:./database 
-script database.h2.sql

This should give you a readable version of the database.

Assuming you are on Linux or similar. Otherwise do some Windows-equivalent 
of this.

The reason this usually works is a bit unclear to me. It seems like 197 and 
later do some extra validation or throw an exception on an error state that 
was ignored before. Therefore the same process will usually not work with 
newer H2 versions. I have never had issues using the resulting 196-created 
database with 199 or 200.

Silvio

On Monday, 3 January 2022 at 11:07:22 UTC+1 kanchan@gmail.com wrote:

> Hi,
>
>  
>
> We are using Atlassian bamboo tool for build management and using embedded 
> database. 
>
> We are getting below error while running the bamboo, looks like the 
> database is corrupted. 
>
>  
>
> *Logs:*
>
> IllegalStateException: Reading from nio:/srv/bamboo-home/database/h2.mv.db 
> failed; file length 255352832 read length 2048 at 266538251 [1.4.199/1]
>
>  
>
>  
>
> Can you please help here to recover it. 
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/3dd75a0d-7cea-467d-b523-fb08ba83be82n%40googlegroups.com.

[h2] Question about database file versions vs H2 versions

[Silvio]

Hello all,

I am designing a H2 database version migration mechanism for our 
application. Since it is a multi-tenant server that runs scripts on 
multiple host machines covering ~25K database files in total we need to 
incorporate an incremental (possible lazy) database conversion scheme. 
Until now we have used a single embedded H2 for database access.

The current plan is to use a H2 server process to access old databases and 
have an embedded H2 that is the latest version for the already converted 
databases. Conversions will be done by doing SQL dumps from the TCP server 
and recreating the database from these dumps using embedded H2.

Can we keep it at one TCP server for the old (H2 1.4.200) databases and 
have the latest H2 2.0.2xx for the embedded mode? Or will database file 
versions vary so often that we will have to have multiple TCP servers for 
managing various older-than-current 2.0.2xx versions as well?

I guess what I am asking is if we can expect database file 
incompatibilities for all future releases or will you incorporate that 
somehow into the release schedule? Something like 2.0.xx versions will have 
compatible file layouts while 2.1.xx will use a new layout or something 
like that.

Thanks for any feedback.

Kind regards,

Silvio

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/d505977e-8c34-4631-9115-2292b363d5afn%40googlegroups.com.

[h2] Question about live backups

[Silvio]

Hello all,

We use H2 (1.4.200) extensively in production environments. Our backup 
mechanism consists of:

- stopping the process
- backing up a directory tree including hundreds/thousands of H2 databases
- starting the process

Bringing the process down is because of the fact that the H2 databases can 
not be backed up on the file system level when they are still open.

For some of the environments the down-time is becoming an issue and we are 
thinking of using the H2 "BACKUP TO xxx" to push out all backups from 
inside the process itself and subsequently backing up the rest of the files.

Is there any risk in using the BACKUP command with databases being in 
(heavy) use? Any increased chance of database corruption? Will it 
dramatically slow down database  access?

If any of the H2 experts could reflect on this that would be great. And if 
anyone can share any experience in this area that would be great also.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/b4e6a5c8-1696-43e1-a384-74fc2af22b3fn%40googlegroups.com.

Re: [h2] Re: H2 DB Corruption: java.lang.IllegalStateException: Chunk 1936 not found [1.4.200/9]

[Silvio]

 the abstraction layer so we do not use any of its compatibility modes. 
And apart from HSQLDB we do not actively use any of the other currently 
supported systems (PostgreSQL, SQLServer, MySQL) any more. I never bothered 
to include Oracle. I used that quite heavily in the 8.x/9.x days and that 
was enough for me to never touch it again.

Thanks again for sharing your thoughts. I will certainly look into your 
conversion tool and will report back here with whatever we come up with.

And I am still hoping to hear something soon about what we can expect the 
coming months...

Kind regards,

Silvio

 

>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/2a846755-b32a-40de-9f13-d03d83f3119cn%40googlegroups.com.

Re: [h2] Re: H2 DB Corruption: java.lang.IllegalStateException: Chunk 1936 not found [1.4.200/9]

[Silvio]

Andreas,

Thank you for sharing your experience. First of all let me stress that I 
also believe the HS developers do a great job. It is a great tool that is 
invaluable to us. I only wish we could have a bit shorter and more 
predictable release cycle, especially in situations where the current 
release version contains non-trivial bugs.

Unfortunately using the development branch is not an option for us. Escrow 
clauses in some of our license agreements compel us to maintain an up to 
date repository of our software binaries and all required libraries and 
run-times. Additional clauses prohibit the use of any pre-release versions 
or outdated versions of libraries or run-times. Apart from that our 
software is a multi-tenant server system that is used in 24/7 production 
environments making upgrades tricky.

Our use-case is quite specific. Users create projects on the fly and each 
of those is a script + data-repository that is a clone of one of the 
project templates. Each template (and each copy thereof) is a 
self-containing system that has its own user interfaces and resources 
including an embedded H2 database. Since a couple of thousand of such 
projects are "active" at the same time the core server application instance 
dynamically loads/unloads them on-demand. We use a multi-database/url 
connection pool that does something similar with database connections, 
resulting in an average of ~50-200 concurrent connections to ~10-50 
databases.
Although some of these database are several Gb in size most of them are 
quite small. A manual dump/restore is already possible but since it is 
in-process the same H2 version is used for the SQL dump and the recreation 
of the database. Perhaps if there would be an easy trick to use 1.4.200 for 
dumping and then 2.0.201 for recreating we could build this into the 
connection pool and do this on demand. It would be great if we could 
somehow use both versions simultaneously inside the same process or if 
there would be sufficient backward compatibility built-in for doing an 
SQL-dump of an older database from inside a newer H2.

On the upside I have managed to steer us clear of Oracle and SQLServer. 
Postgres served us well in days long gone by but for our 
copy-and-open-unlimited-databases-simultaneously use case there is no 
better match than embedded H2. To be fair HSQLDB also works fine but it is 
a lot slower than H2 in my experience.

Cheers,

Silvio


On Tuesday, 23 February 2021 at 14:33:02 UTC+1 wuu...@gmail.com wrote:

> Don't hesitate to use H2 not only for < 100k records. It can handle far 
> more. But upgrade may produce issues, so be careful.
>
> wtorek, 23 lutego 2021 o 02:47:55 UTC+1 and...@manticore-projects.com 
> napisał(a):
>
>> Silvio,
>>
>> we are in a very similar situation. Banking software, financial 
>> accounting, not really the place where you want to experiment a lot. Our 
>> large customers all run Oracle (which deserves its own place in hell) but 
>> for smaller banks less than 100k accounts H2 can provide a nice alternative.
>> I am actually happy to hear from you because sometimes it looks like not 
>> many other people/companies run H2 in a similar scenario.
>> We also have to put a lot of effort in versioning and upgrading our own 
>> VBox software accross various customers and so are aware of the burden, 
>> which any kind of versioning, backward compatibility and migration actually 
>> is.
>>
>> So with all understanding for the developpers and the scarce resources, 
>> from an end-user's point of view the current versioning schema is not 
>> optimal and we were close to abandon H2 once simply because the maintenance 
>> and upgrading procedure became too cumbersome.
>>
>> Eventually we have decided for ourselves, that there is no actual 
>> reliable version schema: We have experienced the current 2.0.201+ 
>> development branch as much more robust and stable and correct than 1.4.200 
>> and also there were many "breaking changes" in between (e. g. new reserved 
>> keywords, NEXTVAL vs. NEXT VALUE FOR etc.) that for us it works better to 
>> be as close as possible to the upstream development. We see H2 now as an 
>> very agile, rolling realise software.
>>
>> I believe, the H2 developpers doe a fantastic job regarding the software 
>> itself but seem to lack experience or interest in practically running/using 
>> H2 in a corporate environment as a replacement for Postgres or Oracle. 
>> Maybe this is just out of scope.
>>  
>> My advise: If you stick with 1.4.200 longer, you will likely face a big 
>> migration effort in your own software/Schema Definition when switching to 
>> 2.0.201 eventually.
>> Consider using 2.0.201 and establish a weekly procedure of exporting to 
>

Re: [h2] Re: H2 DB Corruption: java.lang.IllegalStateException: Chunk 1936 not found [1.4.200/9]

[Silvio]

We use H2 in production heavily and would love to upgrade but as long as 
there is no actual version released we can only use 2.0.x for testing 
purposes. If only we could get some ball park estimate about when 2.0.201 
will be released with some highlights of improvements we can expect and 
preferably some information about compatibility (or lack thereof) with 
1.4.200 that would be great. We are planning the roll-out of a major new 
version of our software which will involve major customer data migrations. 
If a production release of 2.0.201 is imminent we could adjust our release 
schedule to include the upgrade. If it will take another 6-12 months we 
cannot. But as it is we can only guess which, to be honest, really sucks.

On Monday, 22 February 2021 at 06:37:13 UTC+1 and...@manticore-projects.com 
wrote:

> Good Morning.
>
> On Sun, 2021-02-21 at 21:32 -0800, ciphe...@gmail.com wrote:
>
> rying a development version of H2 from Github?
>
>
>
> While I am not a H2 Developper and can only speak for my own experience:
>
> Running several large H2 databases we have had similar corruption issues 
> with 1.4.200+ and never again since we switched to 2.0.201+ (until 
> Feb/March 2020).
> In my opinion, the current 2.0.201+ snapshot is the most robust and 
> correct H2 database and I prefer this "unstable" develepmoent snapshot over 
> the actually released versions.
>
> Best regards
> Andreas 
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/00a5d74e-6a53-4ebd-a9a5-0b9af8fecfc3n%40googlegroups.com.

[h2] Re: Release Schedule

[Silvio]

Hi Andrei,

I am more than happy with H2 not having a set release schedule. But having 
no schedule or even rule of thumb at all is the other extreme.

For us users it would be great if any commitment was expressed to try and 
make meaningful improvements available by releasing a new version, say, 
twice a year. If that is too much any other order of magnitude estimate 
would be fine.

What would be even more welcome would be a couple of hints about what 
things people are working on towards the coming release or the next one. To 
be honest the road map on the site does not really fulfill this function 
since it is not frequently updated and contains many items that have been 
on there for years.

Everybody understands H2 is free software and that people are working on it 
in their spare time. But having almost no information at all is a bit 
unfortunate.

I myself (and I am sure many others) would be more than willing to make a 
build now and then and test specific features if that helps you guys 
getting things production ready.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/dc371bbf-9dc7-4354-b324-112e7c1f79d0%40googlegroups.com.

[h2] Escaping reserved characters in database URL

[Silvio]

I am running into issues with database files that are created with file 
paths based on user input. One example has a path with a directory in it 
that has a ; in it's name. Connecting to the database fails for obvious 
reasons. Is there a way to encode such paths to prevent issues like this?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/25cc491a-c55b-4bb4-bf80-852690eb5be8%40googlegroups.com.

Re: [h2] Question about database performance over time

[Silvio]

That was what I was thinking also. I was just wondering if there are some 
usual suspects that might help me predict if and when this could arise. In 
the past I read that LOBs where a problem area. They are used in this 
database but only in a couple of places. 

Another thing that might be of influence is that this application manages 
many (in this instance potentially ~2000) databases at the same time via a 
multi-database connection pool.

We used to have quite frequent database corruptions and increasing the 
connection linger period in the pool, resulting in less close/reopen 
actions, as you advised earlier did improve that. After moving from 198 to 
200 these corruptions all but disappeared but perhaps some issues are still 
around.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/c98a8e7c-5c99-4569-8ab6-8e30ef92a78e%40googlegroups.com.

[h2] Question about database performance over time

[Silvio]

One of our H2 databases (MVStore cached tables, h2-1.4.200, embedded mode, 
JDK13, dedicated Linux server with 8G RAM and 4 CPUs) is about 1.8G in 
size. The database is both queried and updated quite frequently by many 
application users at the same time. The database does grow but at a very 
slow pace of perhaps 20% annually.

The last couple of months the performance of the application decreased 
significantly. During high load CPU would increase to ~400% with load 
values (top) of up to 20. But even during almost no load (at night when 
nobody is using the application) CPU would be at 200%.

I was able to eliminate all application background processes as the 
possible cause so I decided to do a complete SQL dump/recreate of the 
database. The result was stunning: CPU during high load averages around 
100% and it will drop to 0% when things quit down.

Can you explain this? Is it a general rule that database performance will 
degrade over time regardless of growth factor and that it is advisable to 
dump/restore periodically? If so, are there any metrics that can identify 
if/when such a step would be called for?

Thanks for any insights.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/bc18e6c3-e023-47a7-a415-1f9766e09b44%40googlegroups.com.

[h2] Re: Any advantage in using LocalDateTime binding

[Silvio]

Thanks for confirming that. Internally our application uses java.time 
almost exclusively but is converted where 3rd party APIs require so. The 
JDBC binding for timestamp is currently one of these cases.


On Tuesday, 21 January 2020 14:46:43 UTC+1, Evgenij Ryazanov wrote:
>
> Hello.
>
> No, they don't use any wrappers in latest versions of H2, they all are 
> handled by own implementations in H2.
>
> Actually legacy java.util.* and java.sql.* data types have different bugs 
> in Java; don't use them if you can.
>
> Documentation of H2 also recommends JSR-310 data types:
> https://h2database.com/html/datatypes.html#timestamp_type
>
>> java.time.LocalDateTime is also supported and recommended on Java 8 and 
>> later versions.
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a7b060bc-2a21-4273-93ba-575399aa5735%40googlegroups.com.

[h2] Any advantage in using LocalDateTime binding

[Silvio]

H2 supports preparedStatement.setObject(n,localDateTimeValue) and 
resultSet.getObject(n,LocalDateTime.class). I would like to know if there 
is any performance advantage to be gained by using this (i.e. H2 using 
java.time internally instead of Date/Calendar) or is it a simple wrapper 
around the same internal date handling functions?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/e18f11a6-e699-4f4b-9ad1-df006d7c2519%40googlegroups.com.

Re: [h2] Re: Corrupt Database Chunk Wont Recover

[Silvio]

197...

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/8f3857ce-782f-4e2f-8133-b57638108c64%40googlegroups.com.

Re: [h2] Re: Corrupt Database Chunk Wont Recover

[Silvio]

107 is on the download page. That one should work also. I still have 196 from 
before. Perhaps the H2 guys can help you with that.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/195ceade-a1ec-4e15-b8ef-be7c876225e5%40googlegroups.com.

[h2] Re: Corrupt Database Chunk Wont Recover

[Silvio]

Try to run the Recover tool from H2 version 196 or 197. I regularly deal 
with corrupted databases since moving to 199 and in 9 out of 10 cases the 
older Recover tool works where the one in 199 will not. This does not only 
hold for the Recover tool since 196/197 usually open my "corrupted" 
databases without issues.

To make sure you do not run into compatibility issues between versions also 
use the RunScript from the older version to create a new database. Until 
now I had no issues in opening these with 199.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/1cb933a1-da69-44fb-b217-43a092e6b8a4%40googlegroups.com.

[h2] Corrupted databases - observation

[Silvio]

I have been closely monitoring H2 databases being used on my servers that, 
as I described earlier, each maintain tens to a couple hundred concurrent 
connections to up to a couple thousand H2 databases. Some of these now run 
on h2-1.4.196.jar while most use h2-1.4.199.jar. I already reported many 
database corruptions on servers using 199 and hardly a one on those running 
196.

BUT:

A couple of weeks ago we have gradually started upgrading servers from 
OpenJDK11 to OpenJDK12. Coincident or not; corruptions stopped occurring in 
the JDK12 servers while they continued on the JDK11/h2-199 servers. Eight 
days ago we upgraded the last server and since then we had only one 
corruption: the PageStore error I reported a couple of days ago. All the 
other corruptions where MVStore ones and they have not occurred on a JDK12 
server yet.

This may be purely coincidental but I wonder if anyone more involved in the 
H2 internals can think of any reason why the problem might occur on 
JDK11/h2-199 but might not occur on JDK12 (and perhaps also not on older 
JDK versions used by others) or other H2 versions.

I am just guessing here and I could be too hasty with my conclusion that 
problems occur less/not on JDK12. But any reactions on this would be highly 
appreciated.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a7400cb4-0331-4a8a-a7ce-30a0f9c6ae05%40googlegroups.com.

[h2] Re: Corrupted database - new scenario

[Silvio]

Thanks Evgenij, that also makes sense. The database was indeed created 
using an older version of H2.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/47240da0-1931-4f3c-8993-5a23ebe55383%40googlegroups.com.

[h2] Re: Corrupted database - new scenario

[Silvio]

Thanks Evgenij,

That sounds plausible. It is just that I would have expected the Recover 
tool to be updated as well. Now the Recovery tool in 199 generates SQL that 
199 can not process. A minor detail but that threw me off a bit. 
Fortunately I was able to recover the database so little harm was done.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/b48c01f4-802d-4593-8b7c-32612b8cc530%40googlegroups.com.

[h2] Corrupted database - new scenario

[Silvio]

I have another database corruption case. But this time it is on a PageStore 
DB and the exception contained a different message:

Row not found when trying to delete from index ".I18: ( /* key:9812 */ 
'ea5d1a40-2af9-4d88-8694-bac2c989f28d', 
'83e2f118-2201-475d-9746-a60a97155cb7', 4, 4, 
'490c65e0-dc34-46d2-94a0-6b481581ac65', TIMESTAMP '2018-09-27 
09:48:55.191', TIMESTAMP '2019-05-27 17:31:34.966', 6, NULL, NULL, 
TIMESTAMP '2018-09-27 10:20:52.713', NULL, NULL, NULL, NULL, NULL, NULL, 
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, TIMESTAMP '2019-05-27 
17:31:34.957', NULL, NULL, 1, NULL, NULL, 2, NULL, NULL, NULL, NULL, NULL)" 
[90112-199]

I tried to use the recover tool on the database. Where my previous attempts 
to recover corrupted databases would only succeed using version 197 or 
older this time 199 has no problems creating a perfectly plausible SQL file 
of about twice the size of the database.
However, trying to re-create the database using the RunScript tool with 199 
gives the following exception:

Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax 
error in SQL statement "
CREATE CACHED TABLE IF NOT EXISTS INFORMATION_SCHEMA.LOBS(
ID BIGINT NOT NULL,
BYTE_COUNT BIGINT,
TABLE[*] INT
)
HIDDEN"; expected "identifier"; SQL statement:

CREATE CACHED TABLE IF NOT EXISTS INFORMATION_SCHEMA.LOBS(
ID BIGINT NOT NULL,
BYTE_COUNT BIGINT,
TABLE INT
)
HIDDEN [42001-199]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:451)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)
at org.h2.message.DbException.getSyntaxError(DbException.java:243)
at org.h2.command.Parser.readColumnIdentifier(Parser.java:4530)
at org.h2.command.Parser.parseTableColumnDefinition(Parser.java:7927)
at org.h2.command.Parser.parseCreateTable(Parser.java:7832)
at org.h2.command.Parser.parseCreate(Parser.java:5818)
at org.h2.command.Parser.parsePrepared(Parser.java:846)
at org.h2.command.Parser.parse(Parser.java:788)
at org.h2.command.Parser.parse(Parser.java:760)
at org.h2.command.Parser.prepareCommand(Parser.java:683)
at org.h2.engine.Session.prepareLocal(Session.java:627)
at org.h2.engine.Session.prepareCommand(Session.java:565)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1292)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:217)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205)
at org.h2.tools.RunScript.process(RunScript.java:261)
at org.h2.tools.RunScript.process(RunScript.java:192)
at org.h2.tools.RunScript.process(RunScript.java:328)
at org.h2.tools.RunScript.runTool(RunScript.java:143)
at org.h2.tools.RunScript.main(RunScript.java:70)

Using the RunScript tool with 196 to recreate the database works without 
any issues.

What could be wrong here?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/08df66c1-c02b-4458-b069-4758022bd904%40googlegroups.com.

[h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

It is getting monotonic but we had another database corruption. It happened 
on the same database I have been doing these tests on but since this is one 
of the bigger databases we have that may not be remarkable.
Today we used a different recovery approach: instead of restoring a backup 
(which lags behind up to 24 hours) we did a 
recover-with-196/restore-with-199 cycle. Luckily that worked.

We can no longer risk any more downtime or worse: losing more client data 
so we have decided to revert to 196 for now. Nonetheless will I be working 
on this and if I can do anything to help resolve this issue I will be more 
than happy to.

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/d7ad1a2d-9d0a-4aec-8653-6e03d0bcf5f4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

Hi Andrei,

Did you get anywhere from here using my feedback? Or do you need more 
information? I would be very interested to hear more about this problem.

We just had a very peculiar database corruption: the corrupted database 
(~5Mb) can be accessed from 199 but it appears to be as good as empty. 
Using 199 to dump the database to an SQL script results in a script with a 
single CREATE TABLE statement. I used 197 to dump it and got an SQL file of 
~17Mb. Importing that got me a working database again, both with 197 and 
199.

As you can probably sense I am getting a bit nervous about all this. I am 
still considering reverting to 196 but since that would require me to do a 
full dump-with-199/import-with-196 cycle for all my databases (somewhere 
around 10K by now) this would be quite an endeavor.

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/05ee049c-8f3e-4a0f-80bf-599580232c8e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

Hi Andrei,

Thank you again for delving into this. I built from master branch and first 
ran the recover tool. You are definitely on to something here because where 
199 trips over the database with an exception during the generation of 
database.mv.txt master generates a similarly sized database.mv.txt as 197 
does, somewhere around 443Mb (database.mv.db is 1.1Gb) without exceptions. 
But where 197 subsequently generates a 2.5Gb database.mv.sql master 
generates one of 750 bytes containing:

* START (not part of file)
-- MVStore
CREATE ALIAS IF NOT EXISTS READ_BLOB FOR "org.h2.tools.Recover.readBlob";
CREATE ALIAS IF NOT EXISTS READ_CLOB FOR "org.h2.tools.Recover.readClob";
CREATE ALIAS IF NOT EXISTS READ_BLOB_DB FOR 
"org.h2.tools.Recover.readBlobDb";
CREATE ALIAS IF NOT EXISTS READ_CLOB_DB FOR 
"org.h2.tools.Recover.readClobDb";
CREATE ALIAS IF NOT EXISTS READ_BLOB_MAP FOR 
"org.h2.tools.Recover.readBlobMap";
CREATE ALIAS IF NOT EXISTS READ_CLOB_MAP FOR 
"org.h2.tools.Recover.readClobMap";
-- Meta
-- Tables
 Schema SET 
 Table Data 
 Schema 
DROP ALIAS READ_BLOB;
DROP ALIAS READ_CLOB;
DROP ALIAS READ_BLOB_DB;
DROP ALIAS READ_CLOB_DB;
DROP ALIAS READ_BLOB_MAP;
DROP ALIAS READ_CLOB_MAP;
DROP TABLE IF EXISTS INFORMATION_SCHEMA.LOB_BLOCKS;
* END (not part of file)

I then tried to open the database with the H2 console and that fails with 
this message:

General error: "java.lang.IllegalStateException: Unsupported type 17 
[1.4.199/3]" [5-199] HY000/5 (Help)


-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/571f3497-d0a0-4fc4-b833-eb5ea80716d3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

I managed to build h2 with openjdk8. The recover tool fails in the same way 
with that version as it does with current 199. I tried with 198 and that 
fails also. Then I tried 197 and that succeeds, just like 196.

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/8a6b7a24-017a-4103-9990-c16fd0337e4c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

I just tried to build but I am running openjdk13 (openjdk12 on the servers) 
and the build fails (package com.sun.javadoc does not exist). I used to be 
able to build H2 earlier when I was running openjdk11.

I will try building with an older JDK tomorrow. Any preference for one?

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/133b602f-69ce-4239-933a-9402119d98ba%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

Unfortunately not. The data belongs to a customer. I would be happy to do 
any kind of testing for you though.

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/1c10d3d2-a035-4c97-a28d-393956c83187%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

For what it's worth: using 196 directly on the original corrupted database 
(so not to run the recovery tool but with our application) shows no signs 
of the database being corrupted. I know this does not mean anything but 
nothing I have tried showed any sign of errors or malfunction. Consider me 
baffled.

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/b97195f8-4cc5-4b4f-b15e-f6b51224cf71%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

My first inspection of the recovered database reveals that the database 
seems to be fully intact. I compared it with a backup from the day before 
the corruption happened. The exact same set of tables is there and the 
record counts in corresponding tables is the same or the differences are 
small enough to be explained by the time difference. I can not say anything 
about the contents of individual cells but all queries I did returned 
expected results.

So this makes me come back to my previous question: does it make sense that 
199 considers a database corrupt when 196 does not? And if so: which of 
both is right?

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/9aacffc8-59b2-4932-93ca-10d861f10e2c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

Today I discovered something very interesting. I took a corrupted database 
that 199 tripped over and tried running the recovery tool. This resulted in 
a similar exception our application logged at the time the database got 
corrupted:

jambo@jambo8:~$ java -cp h2-1.4.199.jar org.h2.tools.Recover
Exception in thread "main" java.lang.IllegalStateException: Reading from 
nio:/home/jambo/database.mv.db failed; file length 1127067648 read length 
4096 at 1152263673 [1.4.199/1]
at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:883)
at org.h2.mvstore.DataUtils.readFully(DataUtils.java:420)
at org.h2.mvstore.FileStore.readFully(FileStore.java:98)
at org.h2.mvstore.MVStore.readBufferForPage(MVStore.java:1048)
at org.h2.mvstore.MVStore.readPage(MVStore.java:2186)
at org.h2.mvstore.MVMap.readPage(MVMap.java:554)
at org.h2.mvstore.Page$NonLeaf.getChildPage(Page.java:1086)
at org.h2.mvstore.Cursor.hasNext(Cursor.java:53)
at org.h2.mvstore.MVStore.readStoreHeader(MVStore.java:793)
at org.h2.mvstore.MVStore.(MVStore.java:401)
at org.h2.mvstore.MVStore$Builder.open(MVStore.java:3343)
at org.h2.mvstore.MVStoreTool.info(MVStoreTool.java:347)
at org.h2.tools.Recover.process(Recover.java:344)
at org.h2.tools.Recover.runTool(Recover.java:195)
at org.h2.tools.Recover.main(Recover.java:158)
Caused by: java.io.EOFException
at org.h2.mvstore.DataUtils.readFully(DataUtils.java:408)
... 13 more

So I decided to try the same thing using 196:

jambo@jambo8:~$ java -cp h2-1.4.196.jar org.h2.tools.Recover

what gives? NO ERROR! Version 196 succesfully created a SQL script and I 
was able to create a new database from it using:

jambo@jambo8:~$ java -cp h2*.jar org.h2.tools.RunScript -url 
jdbc:h2:./database -user sa -script database.h2.sql

I have yet to determine if there is anything missing from this recovered 
database, which will not be easy. But from where I stand 199 trips over a 
database file that 196 can process. Of course I understand that this does 
not mean all would be fine using the database in 196 but does this make any 
sense to you? Could it be that 199 is more likely to throw an exception on 
the same database? Or could this just be an example of 196 functioning 
improperly on a database that has been written to by 199 without throwing 
an exception at all?

I will start evaluating the recovered database to see if it is a complete 
recovery and report back here. In the meantime any insights into this 
matter would be very appreciated.

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/796fc36d-ad70-4040-85d9-8c172205e4b0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

Thanks. I will take that into account.

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/0fdf6fb8-fa33-4627-a88b-10370aa59300%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

Yesterday evening at about 20:00 another database on one of our servers got 
corrupted (at least that is when errors started appearing in the logging). 
There was limited server load at the time. In this case it was a database 
of about 850Mb.

To determine our options I would like to raise my earlier question again: 
is there a binary difference in MVStore/PageStore formats between version 
199 and 196? In other words: would it be safe to revert to 196 without 
doing dump/recreate cycles for all our databases?

The current rate of failures is seriously hurting customer confidence so we 
may have to revert to 196 for the short term and do more testing on 
non-critical installations with the newer versions.

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a2da646b-e0fd-4f61-88cf-71b4935d3b34%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

Thank you, that is an excellent suggestion. The times are very short now 
but there is no pressing reason for that other than hoping it would reduce 
resource consumption. I will change that ASAP.

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/7cc53131-caa0-4b70-9a70-e2521d3247ec%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

Additionally, large write queues are very common in our system. At least 
one of the most recent corruptions occurred when a user performed an action 
that caused a new table to be created and ~200K records to be inserted into 
it (using separate connect/insert/disconnect cycles, pooled of course).

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/6f68f682-81bd-4620-ad28-974e5c2aeab5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

The database URLs currently look like:

jdbc:h2:/path/to/file;IGNORECASE=TRUE;MVCC=TRUE;LOCK_TIMEOUT=1

I have experimented with different FILE_LOCK settings but since the 
databases are accessed from one single process this is unlikely to have any 
influence. I could probably do without MVCC=TRUE for 199 but was not sure.

We do not call Thread.interrupt at all.

All database-actions are performed as 
fetch-connection/perform/release-connection cycles (only in auto-commit 
mode, otherwise the same connection is reused). Sometimes a thread may call 
nested actions that have their own fetch/perform/release cycle which 
results in multiple connections being used inside the same thread (again in 
auto-commit mode only). An example would be using one connection to cycle 
through a result-set and using another one to do selects/updates/inserts 
for each record fetched.

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/ebbe5d94-dac8-474f-bea4-3c2572a8cc79%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

Sorry for spamming but I am just thinking out loud here:

Suppose we do leak connections across threads and multiple writes and/or 
closes are performed on the same connections. Could that cause database 
corruption? I know it is a hard call but does it sound likely or even 
possible that changes since 196 might render H2 more sensitive for such 
scenario's?

More info on this would be very helpful for pinpointing any wrongdoing on 
our side.

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/cd997cd0-cf0b-4e9a-817c-00b3327cc636%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

Unfortunately we currently have another case of database corruption. The 
exception stack trace is identical to the one I posted above. What strikes 
me is that the messages reads "database: flush" and subsequently the stack 
trace seems to imply the database is being opened. That sounds 
contradictory to me.

The only common denominator is that all databases are MVStore ones. 
Connections are not shared among threads but multiple threads do access the 
same database via separate connections at the same time. Could something be 
off there? We do exactly the same thing with the PageStore databases we 
have left in operations but none of these have gone bad during these last 
weeks running on 199.

I am considering reverting to version 196. Is that even an option after 
databases have been written (and possibly even created) using 199? 

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/5af4c8a8-0de5-4ac1-bc82-819dc5f63f39%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Corrupted database (again) with h2-1.4.199

[Silvio]

Yes, databases are shutdown and reopened constantly since the connection 
pool will only keep unused connections around for a short period of time 
before closing them. As a result databases are closed by H2 automatically 
once the last connection is closed. This particular type of issue appears 
to result in database corruptions that usually do not show up until the 
database is closed and reopened.

I realize that changes are necessary and appreciate the performance 
improvements which are more than noticeable. I am merely trying to find out 
if some things may have changed that make my usage pattern (accessing many 
embedded databases from the same process with a connection pool that 
opens/closes connections at a quite high rate) possibly more fragile. If 
so, I would be more than willing to make adjustments or do specific testing.

As a software developer I understand how hard it can be to find issues when 
they can not be reproduced predictably. The problem occurs intermittently 
so might be related to specific timing or synchronization scenarios.

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/4c45cd76-2291-48c6-a3a4-026bc9ce9f6a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[h2] Corrupted database (again) with h2-1.4.199

[Silvio]

Today we experienced another database corruption. The difference with 
earlier cases is that this time it happened during normal production use of 
the system while the previous cases seemed to occur during nightly system 
restarts.

The system is a Ubuntu 19.04 server running a singular standalone Java 
server application with OpenJDK 12. The server application accesses a 
couple of hundred H2 file-based (MVStore) databases in embedded mode. The 
connection pool takes care of limiting the number of concurrently open 
connections to a maximum of 99.

Since we have been running this setup about 20-fold during multiple years 
without ever experiencing this until we upgraded from h2-1.4.196 to 
h2-1.4.199 I am convinced something has changed for the worse between these 
versions.

This is the stack trace:

2019-05-17 15:35:43 database: flush
org.h2.message.DbException: General error: 
"java.lang.IllegalStateException: File corrupted in chunk 16664, expected 
page length 4..1536, got 1768842341 [1.4.199/6]" [5-199]
at org.h2.message.DbException.get(DbException.java:194)
at org.h2.message.DbException.convert(DbException.java:347)
at 
org.h2.mvstore.db.MVTableEngine$1.uncaughtException(MVTableEngine.java:90)
at org.h2.mvstore.MVStore.handleException(MVStore.java:2787)
at org.h2.mvstore.MVStore.panic(MVStore.java:441)
at org.h2.mvstore.MVStore.(MVStore.java:404)
at org.h2.mvstore.MVStore$Builder.open(MVStore.java:3343)
at org.h2.mvstore.db.MVTableEngine$Store.open(MVTableEngine.java:162)
at org.h2.mvstore.db.MVTableEngine.init(MVTableEngine.java:95)
at org.h2.engine.Database.getPageStore(Database.java:2739)
at org.h2.engine.Database.open(Database.java:769)
at org.h2.engine.Database.openDatabase(Database.java:319)
at org.h2.engine.Database.(Database.java:313)
at org.h2.engine.Engine.openSession(Engine.java:69)
at org.h2.engine.Engine.openSession(Engine.java:201)
at org.h2.engine.Engine.createSessionAndValidate(Engine.java:178)
at org.h2.engine.Engine.createSession(Engine.java:161)
at org.h2.engine.Engine.createSession(Engine.java:31)
at 
org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:336)
at org.h2.jdbc.JdbcConnection.(JdbcConnection.java:169)
at org.h2.jdbc.JdbcConnection.(JdbcConnection.java:148)
at org.h2.Driver.connect(Driver.java:69)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)

-- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/c0b4841a-ec22-478e-9568-55ed71e77e25%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Corrupted databases revisited

[Silvio]

Thank you for the response Evgenij,

On Thursday, 25 April 2019 15:46:30 UTC+2, Evgenij Ryazanov wrote:
>
> Hello. No, you can't copy a database file if database was not closed 
> properly, but you can use a SHUTDOWN command to close it.
>

Ok, so that means that closing all connections is not enough but a SHUTDOWN 
is required. Is it allowed/safe to do a SHUTDOWN while other connections 
are still active?
 

>
> However, if you need a backup copy it can be created with BACKUP TO 
> 'filename.zip' command, there is no reason to close other connections for 
> it.
>

Yes, that is the only clean way to do it and we plan to move to that 
mechanism for backups. Since our application manages hundreds of separate 
databases we currently use a deep copy of the root directory after closing 
all connections and shutting down the application.


> Usually it's safe to send a SIGTERM to a process, but it depends on your 
> database settings, there is an option that prevents H2 from automatic clean 
> shutdown on process termination.
>

Could you share with us which option that is please?
 

>
> If you can create a standalone test case that reproduces a database 
> corruption, please share it with us.
>

If only I could reproduce this at will but the behavior is intermittent. 
The process I described above is used every night and we have had about 
9-10 databases corrupted out of ~2000 during ~25 restarts. It is a rare 
event but very intrusive when it happens.

-- 
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] Corrupted databases revisited

[Silvio]

Hello all,

Since our upgrade to h2-1.4.199 we are experiencing frequent database 
corruptions. I reported this earlier and have seen at least one similar 
report from someone else in this group showing an almost identical error. 
There has been no response since.

Is there any reason to assume 1.99 could be more susceptible to some kinds 
of database corruptions than, say, 196 which is the version we where using 
before the upgrade? If so, is this something someone is looking in to? Can 
we take special measures to prevent corruptions or at least reduce the 
chance they occur?

More specifically: is it still safe to simply close all connections to a 
DB, wait a couple of seconds and then copy/backup the database files at the 
file system level? Is it still safe to shutdown a (Linux) process that may 
have active database connections and may even be writing to that database 
(not SIGKILL but a plain SIGTERM)? If not: what can we do to securely copy 
a database at file system level or shut-down/restart a running application?

Our problems are making us consider reverting to 196 but that seems 
backward. Please advice.

-- 
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: Corrupted databases with 1.4.199

[Silvio]

Hmm, sorry for the confusion. This was a result of sloppy log copying.

This is what happens now:

2019-04-12 09:24:17 database: flush
org.h2.message.DbException: General error: 
"java.lang.IllegalStateException: Reading from nio:/path/to/database.mv.db 
failed; file length 488419328 read length 4096 at 495344958 [1.4.199/1]" 
[5-199]
at org.h2.message.DbException.get(DbException.java:194)
at org.h2.message.DbException.convert(DbException.java:347)
at 
org.h2.mvstore.db.MVTableEngine$1.uncaughtException(MVTableEngine.java:90)
at org.h2.mvstore.MVStore.handleException(MVStore.java:2787)
at org.h2.mvstore.MVStore.panic(MVStore.java:441)
at org.h2.mvstore.MVStore.(MVStore.java:404)
at org.h2.mvstore.MVStore$Builder.open(MVStore.java:3343)
at org.h2.mvstore.db.MVTableEngine$Store.open(MVTableEngine.java:162)
at org.h2.mvstore.db.MVTableEngine.init(MVTableEngine.java:95)
at org.h2.engine.Database.getPageStore(Database.java:2739)
at org.h2.engine.Database.open(Database.java:769)
at org.h2.engine.Database.openDatabase(Database.java:319)
at org.h2.engine.Database.(Database.java:313)
at org.h2.engine.Engine.openSession(Engine.java:69)
at org.h2.engine.Engine.openSession(Engine.java:201)
at org.h2.engine.Engine.createSessionAndValidate(Engine.java:178)
at org.h2.engine.Engine.createSession(Engine.java:161)
at org.h2.engine.Engine.createSession(Engine.java:31)
at 
org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:336)
at org.h2.jdbc.JdbcConnection.(JdbcConnection.java:169)
at org.h2.jdbc.JdbcConnection.(JdbcConnection.java:148)
at org.h2.Driver.connect(Driver.java:69)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
...
Caused by: org.h2.jdbc.JdbcSQLNonTransientException: General error: 
"java.lang.IllegalStateException: Reading from nio:/path/to/database.mv.db 
failed; file length 488419328 read length 4096 at 495344958 [1.4.199/1]" 
[5-199]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:502)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)
... 298 more
Caused by: java.lang.IllegalStateException: Reading from 
nio:/path/to/database.mv.db failed; file length 488419328 read length 4096 
at 495344958 [1.4.199/1]
at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:883)
at org.h2.mvstore.DataUtils.readFully(DataUtils.java:420)
at org.h2.mvstore.FileStore.readFully(FileStore.java:98)
at org.h2.mvstore.MVStore.readBufferForPage(MVStore.java:1048)
at org.h2.mvstore.MVStore.readPage(MVStore.java:2186)
at org.h2.mvstore.MVMap.readPage(MVMap.java:554)
at org.h2.mvstore.Page$NonLeaf.getChildPage(Page.java:1086)
at org.h2.mvstore.Cursor.hasNext(Cursor.java:53)
at org.h2.mvstore.MVStore.readStoreHeader(MVStore.java:793)
at org.h2.mvstore.MVStore.(MVStore.java:401)
... 292 more
Caused by: java.io.EOFException
at org.h2.mvstore.DataUtils.readFully(DataUtils.java:408)
... 300 more
h2
at org.h2.message.DbException.get(DbException.java:194)
at 
org.h2.mvstore.db.MVTableEngine$Store.convertIllegalStateException(MVTableEngine.java:197)
at org.h2.mvstore.db.MVTableEngine$Store.open(MVTableEngine.java:173)
at org.h2.mvstore.db.MVTableEngine.init(MVTableEngine.java:95)
at org.h2.engine.Database.getPageStore(Database.java:2739)
at org.h2.engine.Database.open(Database.java:769)
at org.h2.engine.Database.openDatabase(Database.java:319)
at org.h2.engine.Database.(Database.java:313)
at org.h2.engine.Engine.openSession(Engine.java:69)
at org.h2.engine.Engine.openSession(Engine.java:201)
at org.h2.engine.Engine.createSessionAndValidate(Engine.java:178)
at org.h2.engine.Engine.createSession(Engine.java:161)
at org.h2.engine.Engine.createSession(Engine.java:31)
at 
org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:336)
at org.h2.jdbc.JdbcConnection.(JdbcConnection.java:169)
at org.h2.jdbc.JdbcConnection.(JdbcConnection.java:148)
at org.h2.Driver.connect(Driver.java:69)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)

-- 
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] Corrupted databases with 1.4.199

[Silvio]

Hello all,

We have experienced repeated database corruption issues after upgrading 
from 1.4.196 tot 1.4.199. The scenario is always the same:

- the application uses an embedded file-based database (MVStore, close to 
500Mb)
- the application is stopped (kill on linux, normal shutdown, no kill -9)
- the database is backed up
- the application is restarted and reconnects to the database
- the database is corrupted (not ever time but about once in 10 times)

org.h2.jdbc.JdbcSQLException: General error: 
"java.lang.IllegalStateException: Reading from nio:/path/database.mv.db 
failed; file length -1 read length 384 at 250535725 [1.4.196/1]";

This process has worked without issues for years now, with various versions 
of H2. Only after a recent upgrade to 1.4.199 have we experienced multiple 
database corruptions. What strikes me is peculiar is that H2 version 
mentioned in the error message. The database was indeed created with 
1.4.196.

Is there something that has changed in this area? Should we recreate the 
database?

-- 
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: Explanation about LAZY_QUERY_EXECUTION

[Silvio]

Thank you Evgenij, that clears up things a lot. 

-- 
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] Explanation about LAZY_QUERY_EXECUTION

[Silvio]

In several threads the connection option LAZY_QUERY_EXECUTION=1 has been 
mentioned, mostly as an option to improve performance with large result 
sets.

I have been looking into the documentation but can not find any more 
information about this option. I do understand what the option is about 
globally but I would like some more details about:

- how does this affect query speed? Any scenario's where this option may be 
much quicker/slower than the default mode?
- how does this affect memory use?
- does this have any positive/negative influence on concurrency?
- are there restrictions to using this option? For example: can I have 
hundreds of connections at the same time using this option?
- can this be considered stable and fit for production? The release notes 
say it is no longer "experimental" but can I interpret that as it being as 
stable as the default mode?

Thanks in advance for any information.

-- 
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] Errors when dumping database to SQL

[Silvio]

Ok, I was merely doing this because an export of a database in normal mode 
often results in considerable growth of the database file. One use case is 
converting the database from PageStore to MVStore and then this is fine. 
But the same code is used for exporting production units to test 
environments and for periodically pushing backups to backup-servers and 
then growing the database is not a good thing.

I would love to experiment with the LAZY_QUERY_EXECUTION=1 option. I read 
about it but was under the impression that it was still under development 
and had use-at-own-risk status. If it is safe enough to use I would be 
happy to give it a go. Would it be useful to try the combination with 
read-only or should I consider read-only a no-go for the moment?

-- 
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] Errors when dumping database to SQL

[Silvio]

Well, I did manage to get this working. I was using ACCESS_MODE_DATA=r to 
open the database in read-only mode thinking that would sooner help than 
hurt. Turns out the opposite is true. I removed this from the connection 
string and it worked.

Additionally, I did the same test on the successfully converted database 
(an MVStore one, that is). Same results: OOM when trying this in read-only 
mode and successful without that option.

I must say that feels very counter-intuitive to me.

-- 
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] Errors when dumping database to SQL

[Silvio]

I just tried MAX_MEMORY_ROWS=1000, same result.

-- 
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] Errors when dumping database to SQL

[Silvio]

I just ran my old code against the latest sources from Git. Sorry to say 
that the result is identical: the process throws an OOM exception.

-- 
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] Errors when dumping database to SQL

[Silvio]

Thank you for pointing add the compression and encryption options, it eases 
the pain a bit.

I understand that an OutputStream could not be used with an SQL command but 
was referring to some internal code I could call directly with an 
OutputStream since I am talking about embedded mode.  The "SCRIPT" with 
ResultSet version makes enough sense which is why I initially decided to 
use that. If this could be made to work in a streaming fashion then that 
would be great.
Thanks again for filing the issue. I can work with this solution for now 
but would go for a more elegant one when possible.

-- 
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] Errors when dumping database to SQL

[Silvio]

Well, that does work. It has the disadvantage of writing unencrypted data 
to the file system and it temporarily takes up quite some space (I was 
streaming the data via a ZipOutputStream over HTTP to a different server). 
It would be great if there was some way to call the same process with an 
OutputStream as parameter, but I guess that is probably a bit of a 
stretch...

-- 
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] Errors when dumping database to SQL

[Silvio]

Hello Noel,

Thanks for the suggestion. Using the command line is a no-go for my 
server-application but writing to a temp-file could work. I will give that 
a go.

Kind regards,

Silvio

-- 
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] Errors when dumping database to SQL

[Silvio]

Hello all,

I have tried this with both h2-1.4.196 and a build of the current sources 
from Git with similar results.

Databases are file-based and H2 runs in embedded mode.

I am trying to dump a largish (1.3Gb) PageStore database as SQL to a file 
by executing "SCRIPT" on a single read-only connection with autocommit, 
MVCC and MULTI_THREADED set to FALSE ad then writing all rows from the 
ResultSet to a file. This works fine for all smaller databases but with 
this database I get either an Out of memory exception 
or org.h2.jdbc.JdbcSQLNonTransientConnectionException: The database has 
been closed [90098-197]. This runs on my local machine with no other access 
to the database. The process heap is already at 2.0G

Is there anything I can do to make this work? Or is there a better way to 
dump a database as an SQL script to an OutputStream? The main function of 
this code is converting old PageStore databases to MVStore ones.

Any ideas?

Kind regards,

Silvio

-- 
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] Re: Error: Unpexpected code path

[Silvio]

Thank you for this. that will solve my problem with the next release. I 
will just have to skip 197.

-- 
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: Error: Unpexpected code path

[Silvio]

I would like to add that most of the applications is working fine and the 
databases can be accessed for the most part. Only specific application 
functions (in this case removing records from a couple of tables) is 
affected.

-- 
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: Error: Unpexpected code path

[Silvio]

Thanks for the response.

Our system supports exporting (and subsequently importing) a "subsystem" 
including its database in either raw or SQL formats. So I did this and that 
does resolve the issue. But I must add that importing a subsystem that was 
exported in SQL format automatically results in a MVStore database. So by 
doing this I silently converted the PageStore database to a MVStore one.

HOWEVER: our production environments consist of tens to hundreds of 
subsystems (users create these from the application by copying template 
definitions) with databases ranging from a couple of MBs totens of GBs. 
That means it is not practically feasible to do conversions across all 
databases. That is why backward compatibility is extremely important to us. 
H2 automagically recognizes and correctly handles old PageStore databases 
while allowing us to use MVStore databases for all new definitions (we 
converted most templates to MVStore).

Swapping out the H2 versions is now no longer possible if it requires 
converting. That will take hours at least. We can not do it lazily since we 
need to provide sub-second responsiveness.

Can this be narrowed down to a more specific subset like affected column 
types or so? Anything that makes a conversion batch more manageable?

Kind regards,

Silvio


On Wednesday, 22 August 2018 13:49:59 UTC+2, Evgenij Ryazanov wrote:
>
> For information:
>
> The only one reliable way to upgrade from 1.4.196 to a more recent version 
> is to run SCRIPT TO 'filename.sql' in this version, then create a new empty 
> database in a new version and finally run RUNSCRIPT FROM 'filename.sql'.
>
> Script tool from 1.4.196 and RunScript from 1.4.197 can be also used, 
> result will be the same:
> http://h2database.com/html/tutorial.html#upgrade_backup_restore
>
> Not all old databases are affected. The common source of problems is ALTER 
> TABLE command that was used in 1.4.196 or in an older version.
>
>
> Your problem, however, may be unrelated.
>

-- 
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: Error: Unpexpected code path

[Silvio]

I added a stack trace from a similar failure. This is in a PageStore 
database.

Relevant part of stack trace:

org.h2.jdbc.JdbcSQLException: General error: "java.lang.RuntimeException: 
Unexpected code path" [5-197]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.message.DbException.convert(DbException.java:307)
at org.h2.message.DbException.toSQLException(DbException.java:280)
at org.h2.message.TraceObject.logAndConvert(TraceObject.java:357)
at 
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:158)
.
 
Caused by: java.lang.RuntimeException: Unexpected code path
at org.h2.message.DbException.throwInternalError(DbException.java:254)
at org.h2.message.DbException.throwInternalError(DbException.java:267)
at org.h2.engine.Session.unlockAll(Session.java:985)
at org.h2.engine.Session.endTransaction(Session.java:760)
at org.h2.engine.Session.commit(Session.java:708)
at org.h2.command.Command.stop(Command.java:157)
at org.h2.command.CommandContainer.stop(CommandContainer.java:122)
at org.h2.command.Command.executeUpdate(Command.java:296)
at 
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:199)
at 
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:153)
...

and

java.lang.RuntimeException: Unexpected code path
at org.h2.message.DbException.throwInternalError(DbException.java:254)
at org.h2.message.DbException.throwInternalError(DbException.java:267)
at org.h2.engine.Session.unlockAll(Session.java:985)
at org.h2.engine.Session.endTransaction(Session.java:760)
at org.h2.engine.Session.commit(Session.java:708)
at org.h2.command.Command.stop(Command.java:157)
at org.h2.command.CommandContainer.stop(CommandContainer.java:122)
at org.h2.command.Command.executeUpdate(Command.java:296)
at 
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:199)
at 
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:153)


-- 
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] Error: Unpexpected code path

[Silvio]

Hello all,

We have an application that uses multiple H2 databases. In our production 
environments we use h2-1.4.196 and that has been working fine for months 
now. In a test environment a have upgraded to version h2-1.4.197 and now 
some parts of our applications fail with "Unexpected code path" errors. The 
tables (and thus SQL the queries) are generated from meta-data that is 
extracted from certain user definitions that determine which columns are 
present and what their types are. So neither the tables nor the queries are 
fixed.

I am pretty sure I have seen this error occur on both PageStore and MVStore 
databases but my most current failures are on a MVStore database. Note that 
these have all been created with 196.

Has something changed that might cause this error to be triggered? It 
sounds like an internal server error to me.

For now we will just stay on 196 but since we like to keep up with new and 
improved versions I would like to resolve this ASAP.

Kind regards,

Silvio

-- 
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: Database size explodes when exporting to SQL

[Silvio]

Thank you for the response Evgenij,

The databases are quite flat. No CLOB/BLOBs, foreign keys or anything fancy 
etc. They do contain a couple of dozens of tables with up to a couple of 
dozen columns. And some of the tables may contain up to 50K rows. In 
PageStore and newly created MVStore terms most databases are no larger then 
400Mb. I have only a couple of databases that are considerably larger.

Query results being buffered into the database sounds plausible although it 
is strange that such buffering seems to take place after the result set has 
been read and the connection is closed. But at least it puts a theoretical 
boundary on its potential size because I can predict what types of queries 
will be used on the databases. It would be good to know how/if this buffer 
space is reclaimed or reused. I know I can shrink the database by 
dump/restore but perhaps there are alternatives to that. I will experiment 
with LAZY_QUERY_EXECUTION.

PageStore has been proven to be very stable. But it's lower concurrency is 
causing some performance issues here and there. Converting to MVStore has 
helped a lot so we would be very interested in converting more databases.

-- 
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: Database size explodes when exporting to SQL

[Silvio]

I made one additional observation which is quite puzzling. 

The increase in database size happens asynchronously. At the moment the SQL 
script has been written (and the connection that was used to issue the 
"SCRIPT" command has been closed) the database is only partially inflated. 
Some background thread is working hard on that though, since it takes only 
15 seconds to get to the inflation factor of ~10.

This issue really concerns me. I have servers where the size of a PageStore 
database approaches 50% of the total disk size (I used to go up to 95% with 
PostgreSQL databases). This has worked flawlessly for years now and no 
strange and unexpected increments in database size have ever happened there.
After positive initial results from tests with some (smaller) databases 
converted from PageStore to MVStore I was considering taking MVStore into 
production. But unless a hard upper limit on the transient inflation factor 
can be guaranteed production use is not an option.

I would really appreciate if one of the developers could shed a light on 
this.

-- 
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: Database size explodes when exporting to SQL

[Silvio]

Besides primay keys that would be zero.

-- 
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: Database size explodes when exporting to SQL

[Silvio]

I tried connecting to the database from the H2 query tool and executing 
"script". Same thing happens there.

-- 
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] Database size explodes when exporting to SQL

[Silvio]

I am experiencing a very strange behaviour of H2 1.4.197 in embedded mode. 
I have an MVStore database that is created by dumping an existing database 
to SQL and then executing that SQL on a newly created database. I repeat 
this process to copy DB1 => DB2, DB2 => DB3, etc.

Each time the last created database is ~300Mb in size. But once I perform 
an additional copy on the new database it grows to ~3Gb while the newly 
created copy again is ~300Mb. This behaviour is consistent. When I only 
dump the database to a plain SQL script and do not create a new copy it 
still blows up to ~3Gb.

No writes are done to the database that is dumped but it is opened in 
read/write mode. Dumps are done by executing the command "SCRIPT" as a 
PreparedStatement and reading the returned SQL statements from the 
ResultSet.

Is there any explanation for this behaviour and can it be prevented?

Kind regards,

Silvio Bierman

-- 
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] Re: DB growing just by reading LOBs

[Silvio]

On Thursday, 7 December 2017 15:13:40 UTC+1, Noel Grandin wrote:
>
>
> Of course. Under most conditions (ie. with setAutoCommit==true), the 
> transaction is complete and closed before the 
> ResultSet is even returned to the client. 
> This is how JDBC works on most databases. 
>

A read-only resultset that does not use a server-side cursor would allow 
closing the transaction and then returning the resultset as a whole (which 
I understood is what H2 does since it has no cursor support).

If a resultset is updatable I am not sure (since I never use those). But I 
expected such a resultset to hold the transaction because of staleness 
issues when updating after the transaction is closed. Then again, I have 
little experience with updatable resultsets.
 

>
> So most people tend to call getBlob() and expect it to work, even though 
> they are now fetching data outside a transaction. 
>
> And they expect it to work even if something else deletes the LOB while 
> they are reading it - outside the transaction :-( 
>
> So fixing this requires some work, and requires a fair amount of care on 
> the part of people writing code to read LOBs 
> from H2. 
>

If my premise of a transaction being open while the resultset is read does 
not hold then I see various issues arise. But then the problem also exists 
for possibly updated non-BLOB values. I expect this to be solved by the 
MV-part of the MV-Store, suspecting some copy-on-write logic. Why are BLOBs 
different then and not allow copy-on-write?

I am just asking because I am interested in how this thing works. Feel free 
to not feel like elaborating.

-- 
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] Re: DB growing just by reading LOBs

[Silvio]

On Thursday, 7 December 2017 12:24:10 UTC+1, Noel Grandin wrote:
>
>
> That is doable, but has nasty performance surprises if you, for example, 
> do SELECT * on a table with a LOB column. If 
> the LOB is big enough, it gets buffered to disk on the client side, and 
> your SELECT performance is suddenly terrible. 
>

Does that mean that you allow reading the resultset AFTER the transaction 
completes? I was assuming the transaction closes when the resultset is 
closed (or at least the records had been fetched), and not before that.

Therefore I was under the impression that the problem was limited to the 
case where someone has already done the getClob and then closes the 
transaction/resultset, while still holding on to the LOB-handle and reading 
it. That is why I considered it such a strange edge-case.

-- 
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] Re: DB growing just by reading LOBs

[Silvio]

On Thursday, 7 December 2017 10:33:36 UTC+1, Noel Grandin wrote:
>
>
> Backwards compatibility is important to us, and the H2 code has worked 
> this way since I started working on it. 
>

I totally agree backward compatibility is important. That is why I opted 
for a (connection/database) setting or, if keeping two modes in the code 
would complicate things too much, a modified behavior which should be 
backward compatible.
When all open LOBs are transferred to the client side of the connection 
when the transaction is closed they can still be read by the application 
afterwards. The only noticeable difference in behavior would be a potential 
performance hit and memory consumption when closing the transaction (which 
could be compensated by faster reading of the LOBs after that). It would 
not have to change application semantics.

But I might be oversimplifying things here. Please correct me if I am wrong.

-- 
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] Re: DB growing just by reading LOBs

[Silvio]

On Wednesday, 6 December 2017 20:27:52 UTC+1, Noel Grandin wrote:
>
> Well, there is also the matter of not having enough time - it would be 
> fairly complex to get all the edge cases right.
>
>
I think we all understand that you have limited time and resources, no 
argument there. I would just like to say that I currently consider MVStore 
unfit for production for these types of problems and therefore still use 
PageStore. Database growth beyond "actual content * some_reasonable_factor" 
is a problem. Database growth caused by reading is unacceptable. So from my 
point of view this is a show stopper with highest priority. 

I wonder how you manage to efficiently facilitate read-only connections 
(which are about 90% in my case) when being forced to write once a LOB is 
encountered.

Why not transfer all LOBs to the client at once during transaction closing 
when it does such a nonsensical thing as reading them after closing the 
transaction they where acquired in?

-- 
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] Re: DB growing just by reading LOBs

[Silvio]

Plase make that a setting. Let's not all suffer for some scarse edge cases 

-- 
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: "h2.storeLocalTime” property behavior

[Silvio]

How do you retrieve the date/time? I mainly use timestamp and 
store/retrieve them as a timestamp, which is a millisecond offset from the 
epoch that holds no timezone info. Same value goes in and out, whatever the 
current timezone is. But if you retrieve dates/times in string format you 
force H2 to do a translation on the timestamp, which means it needs to 
assume a timezone.

So you need to tell us exactly what you are doing.

-- 
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] Re: Bug in H2 (1.4.195 and 1.3.176) concerning column names CHECK or CONSTRAINT

[Silvio]

I was expecting as much. Fortunately these are a couple of isolated cases 
and the new version should prevent this problem from reoccurring.

Thanks again for the fix!

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