[Geoserver-users] H2 JAR version upgrade

GeoServer GeoServer User
1

hi all,

i recently upgraded from GS 2.2.5 to 2.8.3 (w/ JDK 8) and everything
works fine. now i'd like to start using 'h2gis' [1] and its GS
data-store [2].

the (potential) problem i'm facing is the version of the 'H2'. in
the standard GS 2.8.3 release the H2 JAR is at 1.1.119 and is present
in the webapp's lib. 'h2gis' uses a newer version of H2 (1.4.189) which
has an incompatible (disk format) w/ the one used in 1.1. H2 also
provides a utility to 'upgrade database from 1.1...' [3].

this thread [4] (which i believe is still relevant) suggests that H2 is
only used in specific use-cases...

QUOTE
...once a data directory
is converted to use it (either because of superoverlays or gwc
metastore) you cannot go back.
UNQUOTE

in my case, after upgrading to GS 2.8.3 (re-using a copy of the
data-dir previously used by GS 2.2.5, which in turn was a copy of the
same data-dir used by GS 1.5.4) i tried:

* removing the h2-1.1.119 JAR, and separately
* replacing it w/ a newer version (1.4.191)

everything continues to work fine in both instances. this, empirically
at least, tells me that my data-dir is not using any 'feature' that
would otherwise cause an H2 database to be generated.

my questions are --and thanks for reading so far :slight_smile:

* is there a reliable check to apply _before_ upgrading to ensure a
  data-dir will continue to be operational after the upgrade?

  i guess if an H2 database when created/used is always in a specific
  location w/in the data-dir then checking for the presence of such
  file system object should be enough. is this the case?

* has any body attempted migrating to a newer version of H2, had to use
  the (H2) upgrade utility to 'fix' a GS data-dir, and would like to
  share their experience?

[1] http://www.h2gis.org/
[2] https://github.com/orbisgis/h2gis-gs
[3] http://h2database.com/html/download.html
[4]
https://sourceforge.net/p/geoserver/mailman/geoserver-devel/thread/4C2070F7.4000507%40opengeo.org/#msg25582876

TIA + cheers;
rsn

2

Hi Raif,
thorny question. In an ideal world we could:

  1. Go down in GeoTools and replace gt-jdbc-h2 with h2gis
  2. Provide an upgrade path for those using jt-jdbc-h2 to h2gis
  3. Find a way to upgrade the GWC disk quota database with the newer H2
  4. Find a way to upgrade the KML superoverlay database with newer H2
  5. Same as 2-3 for any other H2 usage I don’t remember now

In the reality, 1) is not possible because H2Gis decided on a license that’s
incompatible with GeoTools (only compatible with GeoServer).

  1. would thus be real problem because gt-jdbc-h2 is the only store that
    actually runs tests by default in the build, if we remove it, we’d have
    no test coverage left for those modules (by default at least).
    So… I believe we’d have to try and upgrade gt-jdbc-h2 to v 2.x, not
    sure how hard it would be.

  2. is hard to justify because I believe not very many people use gt-jdbc-h2
    as the storage, but all of our database integration tests in GeoServer
    and some in geotools do, so … uff… we’d have to do some work here
    anyways (basicaly back to 1), assuming it’s ok to drop the axe on actual gt-jdbc-h2 production
    users, hopefully there are few/none)

3/4/5 is annoying because H2 seems to have provided no upgrade path
from 1.x to 2.x… for disk quota and superoverlay I guess we could
just change the location/name of files, drop the old ones, and
have the system rebuild the database from scratch (might take
hours the first startup, especially if one has a large tile cache on disk
with disk quota enabled, the system would have to crawl each and
every tile).

All in all it’s not impossible but looks like a fair amount of work with
some upgrade hiccups for the user base.

Going back to your other question, do I know of production usage of h2gis
with GeoServer? Not personally, sorry :slight_smile:

Cheers
Andrea

···

On Fri, Apr 8, 2016 at 4:37 AM, Raif S. Naffah <raif@anonymised.com> wrote:

hi all,

i recently upgraded from GS 2.2.5 to 2.8.3 (w/ JDK 8) and everything
works fine. now i’d like to start using ‘h2gis’ [1] and its GS
data-store [2].

the (potential) problem i’m facing is the version of the ‘H2’. in
the standard GS 2.8.3 release the H2 JAR is at 1.1.119 and is present
in the webapp’s lib. ‘h2gis’ uses a newer version of H2 (1.4.189) which
has an incompatible (disk format) w/ the one used in 1.1. H2 also
provides a utility to ‘upgrade database from 1.1…’ [3].

this thread [4] (which i believe is still relevant) suggests that H2 is
only used in specific use-cases…

QUOTE
…once a data directory
is converted to use it (either because of superoverlays or gwc
metastore) you cannot go back.
UNQUOTE

in my case, after upgrading to GS 2.8.3 (re-using a copy of the
data-dir previously used by GS 2.2.5, which in turn was a copy of the
same data-dir used by GS 1.5.4) i tried:

  • removing the h2-1.1.119 JAR, and separately
  • replacing it w/ a newer version (1.4.191)

everything continues to work fine in both instances. this, empirically
at least, tells me that my data-dir is not using any ‘feature’ that
would otherwise cause an H2 database to be generated.

my questions are --and thanks for reading so far :slight_smile:

  • is there a reliable check to apply before upgrading to ensure a
    data-dir will continue to be operational after the upgrade?

i guess if an H2 database when created/used is always in a specific
location w/in the data-dir then checking for the presence of such
file system object should be enough. is this the case?

  • has any body attempted migrating to a newer version of H2, had to use
    the (H2) upgrade utility to ‘fix’ a GS data-dir, and would like to
    share their experience?

[1] http://www.h2gis.org/
[2] https://github.com/orbisgis/h2gis-gs
[3] http://h2database.com/html/download.html
[4]
https://sourceforge.net/p/geoserver/mailman/geoserver-devel/thread/4C2070F7.4000507%40opengeo.org/#msg25582876

TIA + cheers;
rsn

Geoserver-users mailing list
Geoserver-users@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

–

==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.

Ing. Andrea Aime

@geowolf
Technical Lead

GeoSolutions S.A.S.
Via di Montramito 3/A
55054 Massarosa (LU)
phone: +39 0584 962313

fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

3

On Fri, Apr 8, 2016 at 9:47 AM, Andrea Aime <andrea.aime@anonymised.com>
wrote:

1) would thus be real problem because gt-jdbc-h2 is the only store that
actually runs tests by default in the build,

I mean, the only one of the gt-jdbc-* family running tests by default, 90%
of the code there is shared,
we have separate dialects and sql encoders for each db to handle the
differences in spatial support

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via di Montramito 3/A
55054 Massarosa (LU)
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

4

hi Andrea,

comments in-line.

On Fri, 8 Apr 2016 09:47:53 +0200, Andrea Aime wrote:

Hi Raif,
thorny question. In an ideal world we could:
1) Go down in GeoTools and replace gt-jdbc-h2 with h2gis
2) Provide an upgrade path for those using jt-jdbc-h2 to h2gis
3) Find a way to upgrade the GWC disk quota database with the newer H2
4) Find a way to upgrade the KML superoverlay database with newer H2
5) Same as 2-3 for any other H2 usage I don't remember now

In the reality, 1) is not possible because H2Gis decided on a license
that's incompatible with GeoTools (only compatible with GeoServer).
1) would thus be real problem because gt-jdbc-h2 is the only store
that actually runs tests by default in the build, if we remove it,
we'd have no test coverage left for those modules (by default at
least). So... I believe we'd have to try and upgrade gt-jdbc-h2 to v
2.x, not sure how hard it would be.

i take it you meant version 1.2 or later referring to H2.

over the weekend i downloaded the sources for Justin's geodb [5]
(tagged version 0.8) and GT 14.3. i was able to build locally a geodb
0.9-SNAPSHOT using the latest stable H2 (1.3.176) and hatbox [6]
(1.0.b9) (the library used by geodb for spatial indexing) w/o problems.

i then used this version to build gt-jdbc-h2. i was able to do that w/
few modifications to the SQL used in 3 H2xxxTestSetup classes [7].

w/ this all 325 tests pass. this to me means it's possible w/ little
effort.

2) is hard to justify because I believe not very many people use
gt-jdbc-h2 as the storage, but all of our database integration tests
in GeoServer and some in geotools do, so ... uff... we'd have to do
some work here anyways (basicaly back to 1), assuming it's ok to
drop the axe on actual gt-jdbc-h2 production
users, hopefully there are few/none)

i think gt-jdbc-h2 should stay as is relying on geodb. in addition to
what you've already mentioned it is an example implementation for adding
spatial capability to Derby/JavaDB if needed using hatbox --a Google
Summer of Code project may be?

3/4/5 is annoying because H2 seems to have provided no upgrade path
from 1.x to 2.x...

i take it you don't consider H2's 'Database Upgrade Helper' tool
appropriate. w/ this tool upgrading existent databases sounds analog
to upgrading PostgreSQL + PostGIS tables: the upgrade is done
separately from GS.

... for disk quota and superoverlay I guess we could
just change the location/name of files, drop the old ones, and
have the system rebuild the database from scratch (might take
hours the first startup, especially if one has a large tile cache on
disk with disk quota enabled, the system would have to crawl each and
every tile).

all valid issues for the developers to consider. for me as a GS user
having the standard distribution contain the latest stable H2 is a step
forward.

All in all it's not impossible but looks like a fair amount of work
with some upgrade hiccups for the user base.

Going back to your other question, do I know of production usage of
h2gis with GeoServer? Not personally, sorry :slight_smile:

On Fri, Apr 8, 2016 at 4:37 AM, Raif S. Naffah <raif@anonymised.com>
wrote:

> hi all,
>
> i recently upgraded from GS 2.2.5 to 2.8.3 (w/ JDK 8) and everything
> works fine. now i'd like to start using 'h2gis' [1] and its GS
> data-store [2].
>
> the (potential) problem i'm facing is the version of the 'H2'. in
> the standard GS 2.8.3 release the H2 JAR is at 1.1.119 and is
> present in the webapp's lib. 'h2gis' uses a newer version of H2
> (1.4.189) which has an incompatible (disk format) w/ the one used
> in 1.1. H2 also provides a utility to 'upgrade database from
> 1.1...' [3].
>
> this thread [4] (which i believe is still relevant) suggests that
> H2 is only used in specific use-cases...
>
> QUOTE
> ...once a data directory
> is converted to use it (either because of superoverlays or gwc
> metastore) you cannot go back.
> UNQUOTE
>
> in my case, after upgrading to GS 2.8.3 (re-using a copy of the
> data-dir previously used by GS 2.2.5, which in turn was a copy of
> the same data-dir used by GS 1.5.4) i tried:
>
> * removing the h2-1.1.119 JAR, and separately
> * replacing it w/ a newer version (1.4.191)
>
> everything continues to work fine in both instances. this,
> empirically at least, tells me that my data-dir is not using any
> 'feature' that would otherwise cause an H2 database to be generated.
>
> my questions are --and thanks for reading so far :slight_smile:
>
> * is there a reliable check to apply _before_ upgrading to ensure a
> data-dir will continue to be operational after the upgrade?
>
> i guess if an H2 database when created/used is always in a
> specific location w/in the data-dir then checking for the presence
> of such file system object should be enough. is this the case?
>
> * has any body attempted migrating to a newer version of H2, had to
> use the (H2) upgrade utility to 'fix' a GS data-dir, and would like
> to share their experience?
>
>
> [1] http://www.h2gis.org/
> [2] https://github.com/orbisgis/h2gis-gs
> [3] http://h2database.com/html/download.html
> [4]
>
> https://sourceforge.net/p/geoserver/mailman/geoserver-devel/thread/4C2070F7.4000507%40opengeo.org/#msg25582876

[5] https://github.com/jdeolive/geodb
[6] http://hatbox.sourceforge.net/
[7] replace GEOMETRY in the 'CREATE TABLE ...' w/ either BLOB or a
concrete geometry class; i.e. POINT and POLYGON.

--
cheers;
rsn

5

On Mon, Apr 11, 2016 at 2:35 AM, Raif S. Naffah <raif@anonymised.com> wrote:

> least). So... I believe we'd have to try and upgrade gt-jdbc-h2 to v
> 2.x, not sure how hard it would be.

i take it you meant version 1.2 or later referring to H2.

Yes, sorry.

over the weekend i downloaded the sources for Justin's geodb [5]
(tagged version 0.8) and GT 14.3. i was able to build locally a geodb
0.9-SNAPSHOT using the latest stable H2 (1.3.176) and hatbox [6]
(1.0.b9) (the library used by geodb for spatial indexing) w/o problems.

i then used this version to build gt-jdbc-h2. i was able to do that w/
few modifications to the SQL used in 3 H2xxxTestSetup classes [7].

w/ this all 325 tests pass. this to me means it's possible w/ little
effort.

That's good news. Maybe make a pull request out of it?

The geometry usage is worrysome, it would mean a play "dump and restore"
upgrade would not work for H2 databases due to the type name conflict.
As said, maybe not such a bit deal, hopefully not too many people use it
in production.

> 2) is hard to justify because I believe not very many people use
> gt-jdbc-h2 as the storage, but all of our database integration tests
> in GeoServer and some in geotools do, so ... uff... we'd have to do
> some work here anyways (basicaly back to 1), assuming it's ok to
> drop the axe on actual gt-jdbc-h2 production
> users, hopefully there are few/none)

i think gt-jdbc-h2 should stay as is relying on geodb. in addition to
what you've already mentioned it is an example implementation for adding
spatial capability to Derby/JavaDB if needed using hatbox --a Google
Summer of Code project may be?

Looks like a good idea, if we can find a mentor (we haven't had any
available
for some time now).

> 3/4/5 is annoying because H2 seems to have provided no upgrade path
> from 1.x to 2.x...

i take it you don't consider H2's 'Database Upgrade Helper' tool
appropriate. w/ this tool upgrading existent databases sounds analog
to upgrading PostgreSQL + PostGIS tables: the upgrade is done
separately from GS.

Having to perform some manual changes against the data directory
in order to upgrade is something that we haven't required... probably never?
Not even going from 1.x to 2.x, the upgrade path was fully automated,
even if the two data dirs look nothing alike.

That said, maybe it's possible to use that tool from code, in such
case, one could probably automate all the database migrations.

>... for disk quota and superoverlay I guess we could
> just change the location/name of files, drop the old ones, and
> have the system rebuild the database from scratch (might take
> hours the first startup, especially if one has a large tile cache on
> disk with disk quota enabled, the system would have to crawl each and
> every tile).

all valid issues for the developers to consider. for me as a GS user
having the standard distribution contain the latest stable H2 is a step
forward.

Eh, we've been discussing this upgrade for a long time... hopefully we'll
find
a good samaritan down the road that will either take on the work to do
it in its spare time, or find a way to sponsor it

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via di Montramito 3/A
55054 Massarosa (LU)
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------