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 statement where not compliant with the standard. In case of H2 compared to Oracle, we do not need any particular adjustments any more (since 2.0.201), while MS SQL Server has basically its own DDL/query definition file. Running the same software and SQL on Oracle (on large servers) and H2 (on a 2 core 4GB virtual machine) is unparalleled and priceless. All the best 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/6d1b924151a6f80962900eea85f184fba335a1cc.camel%40manticore-projects.com.
