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

[Andreas Reichel]

Good Morning.

On Tue, 2021-02-23 at 05:33 -0800, Wojciech Marciniak wrote:
> Don't hesitate to use H2 not only for < 100k records.

I wrote "accounts" (not records) , and each account with average 1'000
balance records and maybe 10'000 postings will give quite some data
volume.
I have experimented with H2 databases up to 20 GByte large and they
perform surprisingly well as long as you avoid large DML with indexes
or constraints.
There is an open ticket on "INSERT INTO ... SELECT .. FROM ...".

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/c21193be4f101038e58e21cd2335312089453614.camel%40manticore-projects.com.

Re: [h2] How can I repair database with error "#Row {1} not found in primary index "PUBLIC.SYS_DATA: 1570" [90143-196]"?

[Andreas Reichel]

On Tue, 2021-02-23 at 10:09 -0800, Thomas Frick wrote:
> Can anyone here please give me a hint on how to repair the DB and
> rescue all the data in it (if it's possible)?

Good Morning Thomas.

http://www.h2database.com/javadoc/org/h2/tools/Recover.html

(Although this has never worked for me.)

Please note: You should avoid opening a H2 database (e.g. 1.4.199) with
any different H2 driver (e.g. 1.4.200).
Instead you are supposed to extract to SQL script and create the DB
afresh from script.

http://www.h2database.com/javadoc/org/h2/tools/Script.html
http://www.h2database.com/javadoc/org/h2/tools/RunScript.html

Good luck and 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/0a640cc6b199a40dabc5431027b2501bca868da1.camel%40manticore-projects.com.

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

[Andreas Reichel]

Good Morning Silvio.

On Tue, 2021-02-23 at 13:18 -0800, Silvio wrote:
> 
> 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.

Spot on, I second this.

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

I do understand your legal requirements, however in my opinion it is an
illusion that sticking with 1.4.200 was compliant with these
requirements: The H2 developpers stated clearly that each version is
just a snapshot of the development branch (in numerical order) and that
there was no support or bug-fixing for older releases.

In fact, all errors we found and reported were affecting 1.4.200 but
have been fixed in the development branch 2.0.201 (only).
If you found the reason for the mentioned corruption and published a
patch, then I doubt they would release another 1.4 version.
You could fork your own H2 repository though and just release H4-1.0
though in order to be escrow compliant. (In fact we have been
discussing that internally already because of the "release schedule").

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

At this part you could expect some effort on your end when switching to
2.0.201: they introduced some more Preserved Keywords (which broke our
age-old Schema Definition) and also (re-)moved some Oracle
compatibility from the H2 mode into the Oracle Mode strictly (NEXT
VALUE FOR vs. NEXTVAL). We had to rewrite a few statements and also
change our code around Sequences. Maybe some other stuff I do not
recall.

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

We have been facing similar challenges and so have built our own
migration tool: https://github.com/manticore-projects/H2MigrationTool
There is a Command Line mode and an UI. If you want to integrate it
into the connection pool, you can strip the UI and use the static
method:

MigrationTool.migrate(
  String versionFrom,
  String versionTo,
  String databaseFileName,
  String user,
  String password,
  String scriptFileName,
  String compression,
  String upgradeOptions,
  boolean overwrite,
  boolean force)
  throws Exception

It supports migration of DB folder, which can contain may H2 DBs.

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

Have a look into the code, it shows how to manage the different H2
Drivers and load/unload them dynamically.

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

So true. Although I must say that only H2 2.0.201 provides the
important features neded for a Oracle replacement: working table links
and WITH clauses. 
And I am still missing the SQL Tuner and a proper execution plan (I
know, I am dreaming).

>  To be fair HSQLDB also works fine but it is a lot slower than H2 in
> my experience.

For us, even more important is the "Oracle compatibility" in H2. While
our product is RDBMS agnostic and works with all major DBs, each
product will need its own handling or SQL 

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 
>> SQL script and re-importing into the next version, followed by a large 
>> batch processing test including reports. This works for us at least and has 
>> reduce the headache.
>>
>> Best regards
>> Andreas
>>
>>
>>
>> On Mon, 2021-02-22 at 16:24 -0800, 

[h2] How can I repair database with error "#Row {1} not found in primary index "PUBLIC.SYS_DATA: 1570" [90143-196]"?

[Thomas Frick]

Hi there,
after trying to open an existing but unmounted/inactive database within 
Squirrel SQL Client, i got this error message:
[...]
class org.h2.jdbc.JdbcSQLException: #Row {1} not found in primary index 
"PUBLIC.SYS_DATA: 1570" [90143-196]
[...]
*urgs*
Each further try to open the DB in Squirrel or in it's owning application 
gives the same error.
Now it seems that this database (or the mentioned index) got corrupted and 
needs to be repaired - but how?
Can anyone here please give me a hint on how to repair the DB and rescue 
all the data in it (if it's possible)?

Thank you so far,
Thomas

-- 
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/39d399e4-4361-4556-9631-020fdad45173n%40googlegroups.com.

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

[Wojciech Marciniak]

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 
> SQL script and re-importing into the next version, followed by a large 
> batch processing test including reports. This works for us at least and has 
> reduce the headache.
>
> Best regards
> Andreas
>
>
>
> On Mon, 2021-02-22 at 16:24 -0800, Silvio wrote:
>
> 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...@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
>  
> 
> .
>
>
>

-- 
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/fcefe30f-55c0-4ce1-940f-e0dacf7ad392n%40googlegroups.com.