HSQL update problem
|
|
coloradoflyer
|
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> |
Kevin Sutter
|
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 <[hidden email]>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. > |
||||||||||||||||
|
coloradoflyer
|
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
|
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 <[hidden email]>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 > > <[hidden email]>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. > |
||||||||||||||||
|
coloradoflyer
|
In reply to this post
by coloradoflyer
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
|
||||||||||||||||
|
Kevin Sutter
|
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 <[hidden email]>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. > |
||||||||||||||||
| Free Embeddable Forum Powered by Nabble | Help |
