dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

Knut

OK in the cold light of day it seems I was quite wrong about how I
understood the auto_server to work.  The tcp stuff is in fact a
distraction and apparently not related to auto_server at all - the h2
driver uses file locking to manage concurrent connections directly to
the db file.  So in fact what works is NOT to use tcp and to make sure
that each connecting client appends ;AUTO_SERVER=TRUE to the url.  tcp
will also work but then we are in fact using vanilla server mode.

So I have currently in dhis2 hibernate.properties
jdbc:h2:./database/newh2;AUTO_SERVER=TRUE.

And I can connect to the same database using the h2 client and through
openoffice using jdbc:h2:~/dhis2-live/database/newh2;AUTO_SERVER=TRUE

Try that.  It seems things are simpler than I initially imagined.  And
so we learn ....

Bob

BTW I just imported a 121M h2 database off a postgres sql dump.  Not
completely trouble free but but nearly.  There are a few
incompatibilities which prevented me from importing the report tables.

On 11 April 2010 13:02, Knut Staring <knutst@xxxxxxxxx> wrote:
> Not quite untangling this yet. Since it is working for you on Linux, I tried
> that now.
>
> I first start DHIS Live with the default URL:
>
> Then I start the H2 console. The following URL does not work, as expected:
> jdbc:h2:../../dhis-live/database/dhis3
>
> But this one does:
> jdbc:h2:../../dhis-live/database/dhis3;AUTO_SERVER=TRUE
>
> However, this one does not work (with or without ;AUTO_SERVER=TRUE)
> jdbc:h2:tcp://localhost/~/Desktop/dhis-live/database/dhis3
>
> Curiously, after the ~ gets substituted with /home/knutst/, the H2 console
> does not seem to find anything, and connects me with an empty database:
> jdbc:h2:tcp://localhost/home/knutst/Desktop/dhis-live/database/dhis3
>
> This is increasingly baffling...
>
> On Sat, Apr 10, 2010 at 11:44 PM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>
> wrote:
>>
>> On 10 April 2010 22:41, Knut Staring <knutst@xxxxxxxxx> wrote:
>> > Well, if I start DHIS Live with the following URL in
>> > hibernate.properites
>> >
>> > jdbc:h2:tcp://localhost/V:/dhis/dhis2-live/database/kenya7;AUTO_SERVER=TRUE
>> > I am then able to connect from H2 console with:
>> > jdbc:h2:tcp://localhost/V:/dhis/dhis2-live/database/kenya7
>> > This at least works for my needs - do you see a problem with doing it
>> > that
>> > way?
>>
>> Yes.  The dhis2 connection will be considerably slower using tcp than
>> accessing the db file directly in embedded mode.  Get rid of the tcp
>> stuff in dhis2 and go back to the way you had it.  Trust me it will
>> work :-)
>>
>> > On Sat, Apr 10, 2010 at 11:35 PM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>
>> > wrote:
>> >>
>> >> On 10 April 2010 22:26, Knut Staring <knutst@xxxxxxxxx> wrote:
>> >> > Ok, it works now - the trick was to use the same URL also in
>> >> > hibernate.properties, i.e.
>> >> >
>> >> >
>> >> > jdbc:h2:tcp://localhost/V:/dhis/dhis2-live/database/kenya7;AUTO_SERVER=TRUE
>> >>
>> >> No that's not the trick!!
>> >>
>> >> In hibernate properties you want:
>> >> jdbc:h2:./database/kenya7;AUTO_SERVER=TRUE
>> >>
>> >> And for subsequent connections (eg console) you want:
>> >> jdbc:h2:tcp://localhost/V:/dhis/dhis2-live/database/kenya7
>> >>
>> >> Note the lack of AUTO_SERVER=TRUE.  You only want one process (dhis2)
>> >> to act as server.  The tcp connection is just for clients.
>> >>
>> >>
>> >> >
>> >> > On Sat, Apr 10, 2010 at 11:23 PM, Knut Staring <knutst@xxxxxxxxx>
>> >> > wrote:
>> >> >>
>> >> >> Almost...maybe it is subtly different on Windows?
>> >> >> If I start the H2 console first like below, I can then proceed to
>> >> >> also
>> >> >> start DHIS Live, and I see changes I make in DHIS show up in the
>> >> >> console:
>> >> >>
>> >> >>
>> >> >>
>> >> >> jdbc:h2:tcp://localhost/V:/dhis/dhis2-live/database/kenya7;AUTO_SERVER=TRUE
>> >> >>
>> >> >> However, if I start DHIS2 Live first, I am not allowed to connect in
>> >> >> the
>> >> >> console.
>> >> >> I can start in the console, then DHIS, log out of the console and
>> >> >> log
>> >> >> in
>> >> >> again (i.e. access both at the same time). I just need to do the
>> >> >> initial
>> >> >> startup with the console.
>> >> >> 2010/4/10 Bob Jolliffe <bobjolliffe@xxxxxxxxx>
>> >> >>>
>> >> >>> 2010/4/10 Bob Jolliffe <bobjolliffe@xxxxxxxxx>:
>> >> >>> > I've also had this problem but I doubt if the bindaddress has
>> >> >>> > anything
>> >> >>> > to do with it.  By default the server socket will be bound to
>> >> >>> > INADDR_ANY.  (This is the same as the tomcat security issue I
>> >> >>> > referred
>> >> >>> > to some time back).  Will investigate ...
>> >> >>> >
>> >> >>> > Bob
>> >> >>> >
>> >> >>> >
>> >> >>> > 2010/4/10 Lars Helge Øverland <larshelge@xxxxxxxxx>:
>> >> >>> >> I have had the same problem. Why don't you test it, you can set
>> >> >>> >> props
>> >> >>> >> from the command line when starting live..
>> >> >>> >>
>> >> >>> >> 2010/4/10, Knut Staring <knutst@xxxxxxxxx>:
>> >> >>> >>> I am unable to connect to the H2 database through the H2
>> >> >>> >>> console
>> >> >>> >>> while DHIS2
>> >> >>> >>> is running, despite AUTO_SERVER being indicated in
>> >> >>> >>> hibernate.properties
>> >> >>> >>> (default for DHIS Live).
>> >> >>> >>>
>> >> >>> >>> >From the following message, it seems that perhaps we need to
>> >> >>> >>> set
>> >> >>> >>>  System.setProperty("h2.bindAddress", ...) before loading the
>> >> >>> >>> org.h2.Driver class (and before starting the server)
>> >> >>> >>>
>> >> >>>
>> >> >>> OK. Here goes ...
>> >> >>>
>> >> >>> I saw that there were a few hits on google about this "problem"
>> >> >>> including the peculiar bindaddress workaround but I wasn't entirely
>> >> >>> convinced.  So I started poking around with the source code and
>> >> >>> looking at the latest releases thinking that if this was a problem
>> >> >>> before, then it would probably have been solved by now.  In the
>> >> >>> process discovered that:
>> >> >>>
>> >> >>> (i)  This really is a fast moving project.  Release cycle seems to
>> >> >>> average a week or two!
>> >> >>> (ii)  We are using v1.1.119 in DHIS2.  Current version is v1.2.132.
>> >> >>> (iii) there are issues of version compatibility with the h2 file
>> >> >>> format.
>> >> >>>
>> >> >>> So thinking that this could be the problem (my external h2 client
>> >> >>> was
>> >> >>> 1.1.114) I decided to update my dhis2 and local client to the
>> >> >>> latest
>> >> >>> and greatest v1.2.132.  To my great disappointment (my dhis-web
>> >> >>> compile is very slow) this made no difference at all other than a
>> >> >>> more
>> >> >>> helpful exception being thrown and reported on.  So ..
>> >> >>>
>> >> >>> Well sometimes I am stupid and sometimes I am very stupid :-)
>> >> >>>
>> >> >>> My url in hibernate.properties is:
>> >> >>> jdbc:h2:./database/testdb;AUTO_SERVER=TRUE
>> >> >>>
>> >> >>> Then in my client (h2 console or openoffice jdbc) I used:
>> >> >>> jdbc:h2:./database/testdb
>> >> >>>
>> >> >>> and of course it doesn't connect.  The dhis2 connection opens the
>> >> >>> file
>> >> >>> in embedded mode which means other clients can't do the same thing
>> >> >>> (file locking prevents this).   Other clients have to connect using
>> >> >>> tcp to get access in server mode.  So if instead I use:
>> >> >>>
>> >> >>> jdbc:h2:tcp://localhost/home/bobj/dhis2-live/database/testdb
>> >> >>>
>> >> >>> everything works like a charm!  My guess is I'm not the only one
>> >> >>> who
>> >> >>> was doing this.  And I doubt if this has much to do with my h2
>> >> >>> version
>> >> >>> upgrade.  Knut, try using a tcp url like this with your setup and
>> >> >>> see
>> >> >>> if it works.  If so problem soved.
>> >> >>>
>> >> >>> There is of course a downside.  If I configure this url in
>> >> >>> openoffice
>> >> >>> (my preferred way of accessing h2 db) I can only open it if h2 is
>> >> >>> running in server mode (eg my dhis2-live is running) which is a bit
>> >> >>> of
>> >> >>> a pain.  I need to actually have two db connections configured -
>> >> >>> one
>> >> >>> for server mode and one to open the db directly.  But I guess you
>> >> >>> can't have your cake and eat it.
>> >> >>>
>> >> >>> Final thought on version upgrade.  It is very tempting to look at
>> >> >>> bumping up our h2 version in dhis2 with one big pro and one big
>> >> >>> con.
>> >> >>> The con being that there will likely be incompatibility with
>> >> >>> existing
>> >> >>> h2 file format (not a major issue as I don't think there's much h2
>> >> >>> production use but it might mess with our sample db).  The pro
>> >> >>> being
>> >> >>> that each new release seems be improving postgres compatibility.
>> >> >>>  If
>> >> >>> we can freely exchange db dumps between h2 and postgres that would
>> >> >>> be
>> >> >>> more than cool.  Given that exchange between postgres versions is
>> >> >>> troublesome anyway I'm not holding my breath too much, but its
>> >> >>> worth
>> >> >>> investigating.
>> >> >>>
>> >> >>> Cheers
>> >> >>> Bob
>> >> >>>
>> >> >>> >>> http://groups.google.com/group/h2-database/msg/7526244fde1a0e58
>> >> >>> >>>
>> >> >>> >>> --
>> >> >>> >>> Cheers,
>> >> >>> >>> Knut Staring
>> >> >>> >>>
>> >> >>> >>
>> >> >>> >> --
>> >> >>> >> Sendt fra min mobile enhet
>> >> >>> >>
>> >> >>> >> _______________________________________________
>> >> >>> >> Mailing list: https://launchpad.net/~dhis2-devs
>> >> >>> >> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> >> >>> >> Unsubscribe : https://launchpad.net/~dhis2-devs
>> >> >>> >> More help   : https://help.launchpad.net/ListHelp
>> >> >>> >>
>> >> >>> >
>> >> >>
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Cheers,
>> >> >> Knut Staring
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Cheers,
>> >> > Knut Staring
>> >> >
>> >
>> >
>> >
>> > --
>> > Cheers,
>> > Knut Staring
>> >
>
>
>
> --
> Cheers,
> Knut Staring
>