You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by coloradoflyer <op...@parallelsw.com> on 2009/10/22 06:28:28 UTC
HSQL update problem
Hi all,
I'm writing an application that currently runs in both HSQL and PostgresQL,
really it should run in any db, but those 2 are ones I have currently
tested.
I'm using openejb (Embedded), and openjpa-2.1. The problem is, when I run
against HSQL and I create a new entity (seems like any row for any entity),
then shutdown my application shortly (within a second or 2) after creating
the row, that row never gets persisted or written out to the log or script
file.
The application is a swing app, and I set the main window closing to exit
the app, I'm assuming this should cause a standard shutdown.
I've looked in the hsql configuration docs and do not seen anything that
seems to be obvious (to me). This seems to be a basic flush problem, but
I'm not sure what to change to get things to flush.
I did create a shutdown hook and did a shutdown of openejb, but that did not
fix things. Any help is greatly appreciated
-chris
my openejb config file has the following DataSources created:
<Resource id="myDatabase" type="DataSource">
JdbcDriver org.hsqldb.jdbcDriver
JdbcUrl jdbc:hsqldb:file:db/mydb
JtaManaged true
</Resource>
<Resource id="myDatabaseUnmanaged" type="DataSource">
JdbcDriver org.hsqldb.jdbcDriver
JdbcUrl jdbc:hsqldb:file:db/mydb
JtaManaged false
</Resource>
and in my persistence.xml file I have:
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">
<persistence-unit name="mydb-unit" transaction-type="JTA">
<jta-data-source>myDatabase</jta-data-source>
<property name="openjpa.Log" value="DefaultLevel=ERROR" />
<property name="openjpa.jdbc.SchemaFactory"
value="native(ForeignKeys=true)"/>
<property name="openjpa.AutoDetach" value="commit"/>
<property name="openjpa.RetainState" value="false"/>
<property name="openjpa.DetachState" value="fetch-groups"/>
</properties>
</persistence-unit>
</persistence>
--
View this message in context: http://n2.nabble.com/HSQL-update-problem-tp3870187p3870187.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: HSQL update problem
Posted by Kevin Sutter <kw...@gmail.com>.
Hi Chris,
Sounds like you are hitting a known situation with HSQLDB... I found this
entry on the Hibernate site...
https://forum.hibernate.org/viewtopic.php?f=1&t=965915
Unfortunately, I don't know if the suggested workaround would work in
OpenJPA or not. It's suggested to pass in a "SET WRITE_DELAY 1" command via
the NativeQuery interface. OpenJPA supports the calling of stored
procedures via this mechanism, but I don't know if the "SET" command will be
processed correctly. You could try it and let us know.
Another possibility is whether something similar to this command could be
passed through the HSQLDB dictionary when it gets initialized. Or, like you
mentioned, via the url for the database.
(The other item mentioned in this article is to ensure that the EM and EMF
are properly closed. I'm assuming that you have already ensured that via
your shutdown processing.)
This seems to be a (strange) quirk with HSQLDB. If, through your testing,
you come up with something that could or should be changed with OpenJPA's
interaction with HSQLDB, let us know.
Good luck,
Kevin
On Thu, Oct 22, 2009 at 3:07 PM, coloradoflyer <op...@parallelsw.com>wrote:
>
> Hi Kevin,
>
> Thanks for the reply. Yes, it does work with Postgresql, the caching
> problems don't seem to be an issue in there. I did notice there is a
> "WRITE_WAIT" time that is setup. I see it go across when the server starts
> up, basically I think it only 'writes' (I would really call it a flush) to
> the files once every 10 seconds. I tried to figure out how to adjust that
> record to not wait and do the writes immediately, but those seem to be only
> posted via SQL in HSQL 1.8. HSQL 1.9 is supposed to support adjustments of
> those values on the jdbcURL line, but when I tried 1.9RC6 there were lots
> of
> errors that came across on a byte[] column... I added a shutdownhook to my
> app that does a Thread.sleep(10 * 1000) (10 seconds), and the data always
> gets flushed out. This is a hack I really can't leave in there for very
> long... It really seems like there should be a good reliable way for me to
> shutdown the db when the app shuts down but I have not found it yet.
>
> There are in fact 2 different db instances (schema's? not sure what to
> call
> it in HSQL) that I'm using, one is read-only and the other one is the one
> I'm having issues with, I wonder if this problem is related to having
> multiple db's open. The tables in the 2 db's are not the same.
>
> -chris
>
>
> Kevin Sutter wrote:
> >
> > Hi coloradoflyer,
> > I'm not an expert with hsql, but I don't see anything "wrong" with your
> > configuration. From your description, your application works just fine
> > with
> > PostgreSQL? But, just changing the database configuration to HSQL, then
> > it
> > fails? And, there are no errors getting posted? If no errors are being
> > logged, then my guess is that OpenJPA is (accidentally) writing to a
> > different database than what you think is configured.
> >
> > Have you tried turning on TRACE for openjpa.log? That would show what
> > database and what tables are being used. This would also show the SQL
> > that
> > is being pushed out to the database, along with any errors that might
> have
> > occurred. I'd start with that for debugging.
> >
> > Good luck,
> > Kevin
> >
> > On Wed, Oct 21, 2009 at 11:28 PM, coloradoflyer
> > <op...@parallelsw.com>wrote:
> >
> >>
> >> Hi all,
> >>
> >> I'm writing an application that currently runs in both HSQL and
> >> PostgresQL,
> >> really it should run in any db, but those 2 are ones I have currently
> >> tested.
> >>
> >> I'm using openejb (Embedded), and openjpa-2.1. The problem is, when I
> >> run
> >> against HSQL and I create a new entity (seems like any row for any
> >> entity),
> >> then shutdown my application shortly (within a second or 2) after
> >> creating
> >> the row, that row never gets persisted or written out to the log or
> >> script
> >> file.
> >>
> >> The application is a swing app, and I set the main window closing to
> exit
> >> the app, I'm assuming this should cause a standard shutdown.
> >>
> >> I've looked in the hsql configuration docs and do not seen anything that
> >> seems to be obvious (to me). This seems to be a basic flush problem,
> but
> >> I'm not sure what to change to get things to flush.
> >>
> >> I did create a shutdown hook and did a shutdown of openejb, but that did
> >> not
> >> fix things. Any help is greatly appreciated
> >>
> >> -chris
> >>
> >> my openejb config file has the following DataSources created:
> >>
> >> <Resource id="myDatabase" type="DataSource">
> >> JdbcDriver org.hsqldb.jdbcDriver
> >> JdbcUrl jdbc:hsqldb:file:db/mydb
> >> JtaManaged true
> >> </Resource>
> >> <Resource id="myDatabaseUnmanaged" type="DataSource">
> >> JdbcDriver org.hsqldb.jdbcDriver
> >> JdbcUrl jdbc:hsqldb:file:db/mydb
> >> JtaManaged false
> >> </Resource>
> >>
> >> and in my persistence.xml file I have:
> >> <persistence xmlns="http://java.sun.com/xml/ns/persistence"
> >> version="1.0">
> >> <persistence-unit name="mydb-unit" transaction-type="JTA">
> >> <jta-data-source>myDatabase</jta-data-source>
> >> <property name="openjpa.Log" value="DefaultLevel=ERROR" />
> >> <property name="openjpa.jdbc.SchemaFactory"
> >> value="native(ForeignKeys=true)"/>
> >> <property name="openjpa.AutoDetach" value="commit"/>
> >> <property name="openjpa.RetainState" value="false"/>
> >> <property name="openjpa.DetachState" value="fetch-groups"/>
> >> </properties>
> >> </persistence-unit>
> >> </persistence>
> >>
> >> --
> >> View this message in context:
> >> http://n2.nabble.com/HSQL-update-problem-tp3870187p3870187.html
> >> Sent from the OpenJPA Users mailing list archive at Nabble.com.
> >>
> >
> >
>
> --
> View this message in context:
> http://n2.nabble.com/HSQL-update-problem-tp3870187p3874719.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
Re: HSQL update problem
Posted by coloradoflyer <op...@parallelsw.com>.
Hi Kevin,
Thanks for the reply. Yes, it does work with Postgresql, the caching
problems don't seem to be an issue in there. I did notice there is a
"WRITE_WAIT" time that is setup. I see it go across when the server starts
up, basically I think it only 'writes' (I would really call it a flush) to
the files once every 10 seconds. I tried to figure out how to adjust that
record to not wait and do the writes immediately, but those seem to be only
posted via SQL in HSQL 1.8. HSQL 1.9 is supposed to support adjustments of
those values on the jdbcURL line, but when I tried 1.9RC6 there were lots of
errors that came across on a byte[] column... I added a shutdownhook to my
app that does a Thread.sleep(10 * 1000) (10 seconds), and the data always
gets flushed out. This is a hack I really can't leave in there for very
long... It really seems like there should be a good reliable way for me to
shutdown the db when the app shuts down but I have not found it yet.
There are in fact 2 different db instances (schema's? not sure what to call
it in HSQL) that I'm using, one is read-only and the other one is the one
I'm having issues with, I wonder if this problem is related to having
multiple db's open. The tables in the 2 db's are not the same.
-chris
Kevin Sutter wrote:
>
> Hi coloradoflyer,
> I'm not an expert with hsql, but I don't see anything "wrong" with your
> configuration. From your description, your application works just fine
> with
> PostgreSQL? But, just changing the database configuration to HSQL, then
> it
> fails? And, there are no errors getting posted? If no errors are being
> logged, then my guess is that OpenJPA is (accidentally) writing to a
> different database than what you think is configured.
>
> Have you tried turning on TRACE for openjpa.log? That would show what
> database and what tables are being used. This would also show the SQL
> that
> is being pushed out to the database, along with any errors that might have
> occurred. I'd start with that for debugging.
>
> Good luck,
> Kevin
>
> On Wed, Oct 21, 2009 at 11:28 PM, coloradoflyer
> <op...@parallelsw.com>wrote:
>
>>
>> Hi all,
>>
>> I'm writing an application that currently runs in both HSQL and
>> PostgresQL,
>> really it should run in any db, but those 2 are ones I have currently
>> tested.
>>
>> I'm using openejb (Embedded), and openjpa-2.1. The problem is, when I
>> run
>> against HSQL and I create a new entity (seems like any row for any
>> entity),
>> then shutdown my application shortly (within a second or 2) after
>> creating
>> the row, that row never gets persisted or written out to the log or
>> script
>> file.
>>
>> The application is a swing app, and I set the main window closing to exit
>> the app, I'm assuming this should cause a standard shutdown.
>>
>> I've looked in the hsql configuration docs and do not seen anything that
>> seems to be obvious (to me). This seems to be a basic flush problem, but
>> I'm not sure what to change to get things to flush.
>>
>> I did create a shutdown hook and did a shutdown of openejb, but that did
>> not
>> fix things. Any help is greatly appreciated
>>
>> -chris
>>
>> my openejb config file has the following DataSources created:
>>
>> <Resource id="myDatabase" type="DataSource">
>> JdbcDriver org.hsqldb.jdbcDriver
>> JdbcUrl jdbc:hsqldb:file:db/mydb
>> JtaManaged true
>> </Resource>
>> <Resource id="myDatabaseUnmanaged" type="DataSource">
>> JdbcDriver org.hsqldb.jdbcDriver
>> JdbcUrl jdbc:hsqldb:file:db/mydb
>> JtaManaged false
>> </Resource>
>>
>> and in my persistence.xml file I have:
>> <persistence xmlns="http://java.sun.com/xml/ns/persistence"
>> version="1.0">
>> <persistence-unit name="mydb-unit" transaction-type="JTA">
>> <jta-data-source>myDatabase</jta-data-source>
>> <property name="openjpa.Log" value="DefaultLevel=ERROR" />
>> <property name="openjpa.jdbc.SchemaFactory"
>> value="native(ForeignKeys=true)"/>
>> <property name="openjpa.AutoDetach" value="commit"/>
>> <property name="openjpa.RetainState" value="false"/>
>> <property name="openjpa.DetachState" value="fetch-groups"/>
>> </properties>
>> </persistence-unit>
>> </persistence>
>>
>> --
>> View this message in context:
>> http://n2.nabble.com/HSQL-update-problem-tp3870187p3870187.html
>> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>>
>
>
--
View this message in context: http://n2.nabble.com/HSQL-update-problem-tp3870187p3874719.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: HSQL update problem
Posted by Kevin Sutter <kw...@gmail.com>.
Hi coloradoflyer,
I'm not an expert with hsql, but I don't see anything "wrong" with your
configuration. From your description, your application works just fine with
PostgreSQL? But, just changing the database configuration to HSQL, then it
fails? And, there are no errors getting posted? If no errors are being
logged, then my guess is that OpenJPA is (accidentally) writing to a
different database than what you think is configured.
Have you tried turning on TRACE for openjpa.log? That would show what
database and what tables are being used. This would also show the SQL that
is being pushed out to the database, along with any errors that might have
occurred. I'd start with that for debugging.
Good luck,
Kevin
On Wed, Oct 21, 2009 at 11:28 PM, coloradoflyer <op...@parallelsw.com>wrote:
>
> Hi all,
>
> I'm writing an application that currently runs in both HSQL and PostgresQL,
> really it should run in any db, but those 2 are ones I have currently
> tested.
>
> I'm using openejb (Embedded), and openjpa-2.1. The problem is, when I run
> against HSQL and I create a new entity (seems like any row for any entity),
> then shutdown my application shortly (within a second or 2) after creating
> the row, that row never gets persisted or written out to the log or script
> file.
>
> The application is a swing app, and I set the main window closing to exit
> the app, I'm assuming this should cause a standard shutdown.
>
> I've looked in the hsql configuration docs and do not seen anything that
> seems to be obvious (to me). This seems to be a basic flush problem, but
> I'm not sure what to change to get things to flush.
>
> I did create a shutdown hook and did a shutdown of openejb, but that did
> not
> fix things. Any help is greatly appreciated
>
> -chris
>
> my openejb config file has the following DataSources created:
>
> <Resource id="myDatabase" type="DataSource">
> JdbcDriver org.hsqldb.jdbcDriver
> JdbcUrl jdbc:hsqldb:file:db/mydb
> JtaManaged true
> </Resource>
> <Resource id="myDatabaseUnmanaged" type="DataSource">
> JdbcDriver org.hsqldb.jdbcDriver
> JdbcUrl jdbc:hsqldb:file:db/mydb
> JtaManaged false
> </Resource>
>
> and in my persistence.xml file I have:
> <persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">
> <persistence-unit name="mydb-unit" transaction-type="JTA">
> <jta-data-source>myDatabase</jta-data-source>
> <property name="openjpa.Log" value="DefaultLevel=ERROR" />
> <property name="openjpa.jdbc.SchemaFactory"
> value="native(ForeignKeys=true)"/>
> <property name="openjpa.AutoDetach" value="commit"/>
> <property name="openjpa.RetainState" value="false"/>
> <property name="openjpa.DetachState" value="fetch-groups"/>
> </properties>
> </persistence-unit>
> </persistence>
>
> --
> View this message in context:
> http://n2.nabble.com/HSQL-update-problem-tp3870187p3870187.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
Re: HSQL update problem
Posted by Kevin Sutter <kw...@gmail.com>.
Sounds good, Chris. Thanks for the update. Let us know when you find out a
better solution with 1.9...
Kevin
On Fri, Oct 23, 2009 at 4:33 PM, coloradoflyer <op...@parallelsw.com>wrote:
>
> Hi all,
>
> Yes, this turned out to be somewhat of a known issue, I did not want to add
> code to my application to specifically do the "SET WRITE_DELAY 1"... This
> would be something in the code I would need to change when I switch out the
> db's. What I did end up doing as a work around is, after the db was
> created, I added the command to the db.script file. This way anytime I'm
> using HSQL the WRITE_DELAY will get set for my app and I dont have anything
> db specific in my code.
>
> Once there is an HSQL v1.9 out and I get some time, I will add the command
> do the url and see if that handles it.
>
> Thanks for the suggestion and help out. I will post again once I get
> things
> moved to 1.9 to see about that workaround.
>
> -chris
>
>
> coloradoflyer wrote:
> >
> > Hi all,
> >
> > I'm writing an application that currently runs in both HSQL and
> > PostgresQL, really it should run in any db, but those 2 are ones I have
> > currently tested.
> >
> > I'm using openejb (Embedded), and openjpa-2.1. The problem is, when I
> run
> > against HSQL and I create a new entity (seems like any row for any
> > entity), then shutdown my application shortly (within a second or 2)
> after
> > creating the row, that row never gets persisted or written out to the log
> > or script file.
> >
> > The application is a swing app, and I set the main window closing to exit
> > the app, I'm assuming this should cause a standard shutdown.
> >
> > I've looked in the hsql configuration docs and do not seen anything that
> > seems to be obvious (to me). This seems to be a basic flush problem, but
> > I'm not sure what to change to get things to flush.
> >
> > I did create a shutdown hook and did a shutdown of openejb, but that did
> > not fix things. Any help is greatly appreciated
> >
> > -chris
> >
> > my openejb config file has the following DataSources created:
> >
> > <Resource id="myDatabase" type="DataSource">
> > JdbcDriver org.hsqldb.jdbcDriver
> > JdbcUrl jdbc:hsqldb:file:db/mydb
> > JtaManaged true
> > </Resource>
> > <Resource id="myDatabaseUnmanaged" type="DataSource">
> > JdbcDriver org.hsqldb.jdbcDriver
> > JdbcUrl jdbc:hsqldb:file:db/mydb
> > JtaManaged false
> > </Resource>
> >
> > and in my persistence.xml file I have:
> > <persistence xmlns="http://java.sun.com/xml/ns/persistence"
> version="1.0">
> > <persistence-unit name="mydb-unit" transaction-type="JTA">
> > <jta-data-source>myDatabase</jta-data-source>
> > <property name="openjpa.Log" value="DefaultLevel=ERROR" />
> > <property name="openjpa.jdbc.SchemaFactory"
> > value="native(ForeignKeys=true)"/>
> > <property name="openjpa.AutoDetach" value="commit"/>
> > <property name="openjpa.RetainState" value="false"/>
> > <property name="openjpa.DetachState" value="fetch-groups"/>
> > </properties>
> > </persistence-unit>
> > </persistence>
> >
> >
>
> --
> View this message in context:
> http://n2.nabble.com/HSQL-update-problem-tp3870187p3881417.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
Re: HSQL update problem
Posted by coloradoflyer <op...@parallelsw.com>.
Hi all,
Yes, this turned out to be somewhat of a known issue, I did not want to add
code to my application to specifically do the "SET WRITE_DELAY 1"... This
would be something in the code I would need to change when I switch out the
db's. What I did end up doing as a work around is, after the db was
created, I added the command to the db.script file. This way anytime I'm
using HSQL the WRITE_DELAY will get set for my app and I dont have anything
db specific in my code.
Once there is an HSQL v1.9 out and I get some time, I will add the command
do the url and see if that handles it.
Thanks for the suggestion and help out. I will post again once I get things
moved to 1.9 to see about that workaround.
-chris
coloradoflyer wrote:
>
> Hi all,
>
> I'm writing an application that currently runs in both HSQL and
> PostgresQL, really it should run in any db, but those 2 are ones I have
> currently tested.
>
> I'm using openejb (Embedded), and openjpa-2.1. The problem is, when I run
> against HSQL and I create a new entity (seems like any row for any
> entity), then shutdown my application shortly (within a second or 2) after
> creating the row, that row never gets persisted or written out to the log
> or script file.
>
> The application is a swing app, and I set the main window closing to exit
> the app, I'm assuming this should cause a standard shutdown.
>
> I've looked in the hsql configuration docs and do not seen anything that
> seems to be obvious (to me). This seems to be a basic flush problem, but
> I'm not sure what to change to get things to flush.
>
> I did create a shutdown hook and did a shutdown of openejb, but that did
> not fix things. Any help is greatly appreciated
>
> -chris
>
> my openejb config file has the following DataSources created:
>
> <Resource id="myDatabase" type="DataSource">
> JdbcDriver org.hsqldb.jdbcDriver
> JdbcUrl jdbc:hsqldb:file:db/mydb
> JtaManaged true
> </Resource>
> <Resource id="myDatabaseUnmanaged" type="DataSource">
> JdbcDriver org.hsqldb.jdbcDriver
> JdbcUrl jdbc:hsqldb:file:db/mydb
> JtaManaged false
> </Resource>
>
> and in my persistence.xml file I have:
> <persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">
> <persistence-unit name="mydb-unit" transaction-type="JTA">
> <jta-data-source>myDatabase</jta-data-source>
> <property name="openjpa.Log" value="DefaultLevel=ERROR" />
> <property name="openjpa.jdbc.SchemaFactory"
> value="native(ForeignKeys=true)"/>
> <property name="openjpa.AutoDetach" value="commit"/>
> <property name="openjpa.RetainState" value="false"/>
> <property name="openjpa.DetachState" value="fetch-groups"/>
> </properties>
> </persistence-unit>
> </persistence>
>
>
--
View this message in context: http://n2.nabble.com/HSQL-update-problem-tp3870187p3881417.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.