Multiple connections to H2

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)

http://groups.google.com/group/h2-database/msg/7526244fde1a0e58

···


Cheers,
Knut Staring

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@gmail.com>:

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)

http://groups.google.com/group/h2-database/msg/7526244fde1a0e58

--
Cheers,
Knut Staring

--
Sendt fra min mobile enhet

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@gmail.com>:

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@gmail.com>:

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)

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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

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

I have had the same problem. Why don't you test it, you can set props
from the command line when starting live..

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 :slight_smile:

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

···

2010/4/10 Bob Jolliffe <bobjolliffe@gmail.com>:

2010/4/10 Lars Helge Øverland <larshelge@gmail.com>:

2010/4/10, Knut Staring <knutst@gmail.com>:

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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

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@gmail.com

2010/4/10 Bob Jolliffe bobjolliffe@gmail.com:

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@gmail.com:

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@gmail.com:

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 :slight_smile:

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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Cheers,
Knut Staring

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

···

On Sat, Apr 10, 2010 at 11:23 PM, Knut Staring knutst@gmail.com 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@gmail.com

2010/4/10 Bob Jolliffe bobjolliffe@gmail.com:

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@gmail.com:

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@gmail.com:

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 :slight_smile:

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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Cheers,
Knut Staring


Cheers,
Knut Staring

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 10 April 2010 22:26, Knut Staring <knutst@gmail.com> wrote:

On Sat, Apr 10, 2010 at 11:23 PM, Knut Staring <knutst@gmail.com> 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@gmail.com>

2010/4/10 Bob Jolliffe <bobjolliffe@gmail.com>:
> 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@gmail.com>:
>> 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@gmail.com>:
>>> 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 :slight_smile:

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@lists.launchpad.net
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help : https://help.launchpad.net/ListHelp
>>
>

--
Cheers,
Knut Staring

--
Cheers,
Knut Staring

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?

···

On Sat, Apr 10, 2010 at 11:35 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

On 10 April 2010 22:26, Knut Staring knutst@gmail.com 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@gmail.com 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@gmail.com

2010/4/10 Bob Jolliffe bobjolliffe@gmail.com:

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@gmail.com:

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@gmail.com:

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 :slight_smile:

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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Cheers,

Knut Staring

Cheers,

Knut Staring


Cheers,
Knut Staring

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 :slight_smile:

···

On 10 April 2010 22:41, Knut Staring <knutst@gmail.com> wrote:

On Sat, Apr 10, 2010 at 11:35 PM, Bob Jolliffe <bobjolliffe@gmail.com> > wrote:

On 10 April 2010 22:26, Knut Staring <knutst@gmail.com> 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@gmail.com> 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@gmail.com>
>>>
>>> 2010/4/10 Bob Jolliffe <bobjolliffe@gmail.com>:
>>> > 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@gmail.com>:
>>> >> 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@gmail.com>:
>>> >>> 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 :slight_smile:
>>>
>>> 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@lists.launchpad.net
>>> >> Unsubscribe : https://launchpad.net/~dhis2-devs
>>> >> More help : https://help.launchpad.net/ListHelp
>>> >>
>>> >
>>
>>
>>
>> --
>> Cheers,
>> Knut Staring
>
>
>
> --
> Cheers,
> Knut Staring
>

--
Cheers,
Knut Staring

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@gmail.com wrote:

On 10 April 2010 22:41, Knut Staring knutst@gmail.com 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 :slight_smile:

On Sat, Apr 10, 2010 at 11:35 PM, Bob Jolliffe bobjolliffe@gmail.com > > > wrote:

On 10 April 2010 22:26, Knut Staring knutst@gmail.com 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@gmail.com 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@gmail.com

2010/4/10 Bob Jolliffe bobjolliffe@gmail.com:

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@gmail.com:

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@gmail.com:

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 :slight_smile:

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@lists.launchpad.net

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

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@gmail.com> 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@gmail.com> > wrote:

On 10 April 2010 22:41, Knut Staring <knutst@gmail.com> 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 :slight_smile:

> On Sat, Apr 10, 2010 at 11:35 PM, Bob Jolliffe <bobjolliffe@gmail.com> >> > wrote:
>>
>> On 10 April 2010 22:26, Knut Staring <knutst@gmail.com> 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@gmail.com> >> >> > 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@gmail.com>
>> >>>
>> >>> 2010/4/10 Bob Jolliffe <bobjolliffe@gmail.com>:
>> >>> > 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@gmail.com>:
>> >>> >> 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@gmail.com>:
>> >>> >>> 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 :slight_smile:
>> >>>
>> >>> 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@lists.launchpad.net
>> >>> >> 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

Ok, that is exactly as I reported in my last email, so we have agreement :wink:

Cool to know import from Postgres is almost there - especially since we still need to extend the coverage of DXF. And perhaps faster than using DXF?

k

···

On Sun, Apr 11, 2010 at 3:37 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

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@gmail.com 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@gmail.com > > > wrote:

On 10 April 2010 22:41, Knut Staring knutst@gmail.com 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 :slight_smile:

On Sat, Apr 10, 2010 at 11:35 PM, Bob Jolliffe bobjolliffe@gmail.com > > >> > wrote:

On 10 April 2010 22:26, Knut Staring knutst@gmail.com 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@gmail.com > > >> >> > 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@gmail.com

2010/4/10 Bob Jolliffe bobjolliffe@gmail.com:

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@gmail.com:

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@gmail.com:

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 :slight_smile:

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@lists.launchpad.net

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


Cheers,
Knut Staring

I see H2 can be run in “PostgreSQL mode” by appending ;MODE=PostgreSQL to the connection URL:

http://www.h2database.com/html/features.html#compatibility

Could be interesting to try to import the pgdump using this…

Lars

I had a go at this earlier with reasonable success. You have to make
an uncompressed pg dump with insert statements (these are both
optional parametrs to pg_dump). Some very slight adjustment to the
structural metadata - I'll document later if people are interested -
and then tried to import data.

Only one snag - pg has its own peculiar way of handling binary strings
(bytea fields) which h2 doesn't emulate. Can't blame h2 too much -
what pg does is a bid weird.

This affects systemsettings, usersettings and reportsettings.

Other than that I imported 1.5 million datavalues and associated
dataelements, orgunits etc.

The h2 guys are aware of the bytea compatibility problem and have the
issue roadmapped - apparently they will bump it if it something people
request. I guess I should request.

If I was really pushed I could write a script to find all the binary
strings in the pg dump and try to convert them to something h2 is
happy with.

Having said that, I think all the places where we currently use binary
strings are really unnecessary (storing evil java serialized objects)
which I think have a historical rationale. It should be relatively
easy and desirable to change these - particularly as we also want to
represent these things in xml - but we'd have to consider the effect
on compatibility with legacy dhis databases which have the blobs. I
guess we'd need an upgrade script of some sort.

So the prognosis is good but not quite seamless yet.

Cheers
Bob

BTW how did you make the demo db?

···

2010/4/12 Lars Helge Øverland <larshelge@gmail.com>:

I see H2 can be run in "PostgreSQL mode" by appending ;MODE=PostgreSQL to
the connection URL:
http://www.h2database.com/html/features.html#compatibility

Could be interesting to try to import the pgdump using this..
Lars

This is great news. The system settings should def be no show-stopper here. First they could simply be omitted as they probably will be set individually anyway, second we could migrate to string as we have never really used objects as settings.

Lars

···

2010/4/12 Bob Jolliffe bobjolliffe@gmail.com

I had a go at this earlier with reasonable success. You have to make

an uncompressed pg dump with insert statements (these are both

optional parametrs to pg_dump). Some very slight adjustment to the

structural metadata - I’ll document later if people are interested -

and then tried to import data.

Only one snag - pg has its own peculiar way of handling binary strings

(bytea fields) which h2 doesn’t emulate. Can’t blame h2 too much -

what pg does is a bid weird.

This affects systemsettings, usersettings and reportsettings.

Other than that I imported 1.5 million datavalues and associated

dataelements, orgunits etc.

The h2 guys are aware of the bytea compatibility problem and have the

issue roadmapped - apparently they will bump it if it something people

request. I guess I should request.

If I was really pushed I could write a script to find all the binary

strings in the pg dump and try to convert them to something h2 is

happy with.

Having said that, I think all the places where we currently use binary

strings are really unnecessary (storing evil java serialized objects)

which I think have a historical rationale. It should be relatively

easy and desirable to change these - particularly as we also want to

represent these things in xml - but we’d have to consider the effect

on compatibility with legacy dhis databases which have the blobs. I

guess we’d need an upgrade script of some sort.

So the prognosis is good but not quite seamless yet.

Cheers

I had a go at this earlier with reasonable success. You have to make
an uncompressed pg dump with insert statements (these are both
optional parametrs to pg_dump). Some very slight adjustment to the
structural metadata - I'll document later if people are interested -
and then tried to import data.

Only one snag - pg has its own peculiar way of handling binary strings
(bytea fields) which h2 doesn't emulate. Can't blame h2 too much -
what pg does is a bid weird.

This affects systemsettings, usersettings and reportsettings.

Other than that I imported 1.5 million datavalues and associated
dataelements, orgunits etc.

The h2 guys are aware of the bytea compatibility problem and have the
issue roadmapped - apparently they will bump it if it something people
request. I guess I should request.

If I was really pushed I could write a script to find all the binary
strings in the pg dump and try to convert them to something h2 is
happy with.

Having said that, I think all the places where we currently use binary
strings are really unnecessary (storing evil java serialized objects)
which I think have a historical rationale. It should be relatively
easy and desirable to change these - particularly as we also want to
represent these things in xml - but we'd have to consider the effect
on compatibility with legacy dhis databases which have the blobs. I
guess we'd need an upgrade script of some sort.

So the prognosis is good but not quite seamless yet.

Cheers

This is great news. The system settings should def be no show-stopper here.
First they could simply be omitted as they probably will be set individually
anyway, second we could migrate to string as we have never really used
objects as settings.

Hi Lars

True. system settings should be least disruptive. Sorry I meant
reporttable (rather than report setting!). We are using a serialized
java object to represent the dimension type which also seems a bit
strange. How do (non java) db clients like excel deal with these?
I'll have another look tomorrow, but you will know better the
rationale. I'm guessing its related to the whole casting thing that
we are doing with dimensions.

Cheers
Bob

···

2010/4/12 Lars Helge Øverland <larshelge@gmail.com>:

2010/4/12 Bob Jolliffe <bobjolliffe@gmail.com>

Lars

OK we are using an Enum to store the type. Hibernate maps this to a binary field in the db as default but it seems its possible to map it to an int or varchar field. We could do that.

Lars

···

2010/4/13 Bob Jolliffe bobjolliffe@gmail.com

2010/4/12 Lars Helge Øverland larshelge@gmail.com:

2010/4/12 Bob Jolliffe bobjolliffe@gmail.com

I had a go at this earlier with reasonable success. You have to make

an uncompressed pg dump with insert statements (these are both

optional parametrs to pg_dump). Some very slight adjustment to the

structural metadata - I’ll document later if people are interested -

and then tried to import data.

Only one snag - pg has its own peculiar way of handling binary strings

(bytea fields) which h2 doesn’t emulate. Can’t blame h2 too much -

what pg does is a bid weird.

This affects systemsettings, usersettings and reportsettings.

Other than that I imported 1.5 million datavalues and associated

dataelements, orgunits etc.

The h2 guys are aware of the bytea compatibility problem and have the

issue roadmapped - apparently they will bump it if it something people

request. I guess I should request.

If I was really pushed I could write a script to find all the binary

strings in the pg dump and try to convert them to something h2 is

happy with.

Having said that, I think all the places where we currently use binary

strings are really unnecessary (storing evil java serialized objects)

which I think have a historical rationale. It should be relatively

easy and desirable to change these - particularly as we also want to

represent these things in xml - but we’d have to consider the effect

on compatibility with legacy dhis databases which have the blobs. I

guess we’d need an upgrade script of some sort.

So the prognosis is good but not quite seamless yet.

Cheers

This is great news. The system settings should def be no show-stopper here.

First they could simply be omitted as they probably will be set individually

anyway, second we could migrate to string as we have never really used

objects as settings.

Hi Lars

True. system settings should be least disruptive. Sorry I meant

reporttable (rather than report setting!). We are using a serialized

java object to represent the dimension type which also seems a bit

strange. How do (non java) db clients like excel deal with these?

I’ll have another look tomorrow, but you will know better the

rationale. I’m guessing its related to the whole casting thing that

we are doing with dimensions.

Cheers

Bob

I have

- updated to the latest H2 version
- created a new H2 demo database and uploaded to dhis2.org
- changed the report table dimension type property to varchar

Lars

···

2010/4/13 Bob Jolliffe <bobjolliffe@gmail.com>:

2010/4/12 Lars Helge Øverland <larshelge@gmail.com>:

2010/4/12 Bob Jolliffe <bobjolliffe@gmail.com>

I had a go at this earlier with reasonable success. You have to make
an uncompressed pg dump with insert statements (these are both
optional parametrs to pg_dump). Some very slight adjustment to the
structural metadata - I'll document later if people are interested -
and then tried to import data.

Only one snag - pg has its own peculiar way of handling binary strings
(bytea fields) which h2 doesn't emulate. Can't blame h2 too much -
what pg does is a bid weird.

This affects systemsettings, usersettings and reportsettings.

Other than that I imported 1.5 million datavalues and associated
dataelements, orgunits etc.

The h2 guys are aware of the bytea compatibility problem and have the
issue roadmapped - apparently they will bump it if it something people
request. I guess I should request.

If I was really pushed I could write a script to find all the binary
strings in the pg dump and try to convert them to something h2 is
happy with.

Having said that, I think all the places where we currently use binary
strings are really unnecessary (storing evil java serialized objects)
which I think have a historical rationale. It should be relatively
easy and desirable to change these - particularly as we also want to
represent these things in xml - but we'd have to consider the effect
on compatibility with legacy dhis databases which have the blobs. I
guess we'd need an upgrade script of some sort.

So the prognosis is good but not quite seamless yet.

Cheers

This is great news. The system settings should def be no show-stopper here.
First they could simply be omitted as they probably will be set individually
anyway, second we could migrate to string as we have never really used
objects as settings.

Hi Lars

True. system settings should be least disruptive. Sorry I meant
reporttable (rather than report setting!). We are using a serialized
java object to represent the dimension type which also seems a bit
strange. How do (non java) db clients like excel deal with these?
I'll have another look tomorrow, but you will know better the
rationale. I'm guessing its related to the whole casting thing that
we are doing with dimensions.

Cheers
Bob

Lars