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 
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
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
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
My url in hibernate.properties is:
jdbc:h2:./database/testdb;AUTO_SERVER=TRUEThen in my client (h2 console or openoffice jdbc) I used:
jdbc:h2:./database/testdband 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
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
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=TRUENo that's not the trick!!
In hibernate properties you want:
jdbc:h2:./database/kenya7;AUTO_SERVER=TRUEAnd for subsequent connections (eg console) you want:
jdbc:h2:tcp://localhost/V:/dhis/dhis2-live/database/kenya7Note 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
>>>
>>> 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
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
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/dhis3But this one does:
jdbc:h2:../../dhis-live/database/dhis3;AUTO_SERVER=TRUEHowever, this one does not work (with or without ;AUTO_SERVER=TRUE)
jdbc:h2:tcp://localhost/~/Desktop/dhis-live/database/dhis3Curiously, 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/dhis3This 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> 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
>> >>>
>> >>> 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 
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
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
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:
FeaturesCould 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
BobLars