Mario, You could use the Derby database that is built-in in Geronimo. Would it help?
Vasily -----Original Message----- From: Mario Rubsam [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 31, 2006 10:06 PM To: [email protected] Subject: Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB Santosh, I did the development mostly on PostgreSQL. It's working there but don't know if the commits come through or if the DB handle it in some way. I can't test Derby atm because I have to setup a separate Derby server to import all the data needed. That needs to much time. Thanks, Mario Santosh Koti wrote: > Mario, > > Can u try the same thing on the Derby ,if it is not cumbersome :) > I think tranQL has better support for Derby. > > PS: Ofcourse it may not solve ur MaxDB problem ,but atleast it says ur code > is safe enough :) > > > Thanks, > Santosh. > "Don't talk about yourself; it will be done when you leave. " > > > -----Original Message----- > From: Santosh Koti [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 31, 2006 11:10 PM > To: [email protected] > Subject: RE: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB > > > Mario, > > I agree that is not a solution , but atleast it points out that the problem > is cornered around TranQL ?? . (That's what I suspect !! If so, then look > smething on transQL config, or may need to chk for next release :) > > Time for the gurus to dive in here !! :) > > Thanks, > Santosh. > "Don't talk about yourself; it will be done when you leave. " > > > -----Original Message----- > From: Mario Rübsam [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 31, 2006 10:55 PM > To: [email protected] > Subject: Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB > > Santosh, > > > Santosh Koti wrote: >> Mario, >> >> Just give another try (of course a wild advise :-)) > > I did ;) > >> After running the transactions , chk ur DB for any updates, then bring the >> server down -> & then chk ur DB, whether commit is happening the moment u >> trigger the server with a shutdown event ?? > > data is committed with shutdown, but that is not a solution > >> If so , some configuration needs to be looked at ? ! > > here is what I did now: > > instead of calling: > > mConnection.commit() > > I did the following: > > Statement tStatement = mConnection.createStatement(); > tStatement.executeUpdate("COMMIT"); > > this is dirty but working, all commits are registered on the MaxDB server > this is bypassing all the commit stuff in the Tranql Connection object > and the driver > > So I find me now debugging the commit stuff in the Connection instance. > > Thanks, > Mario > > >> >> >> Thanks, >> Santosh. >> "Don't talk about yourself; it will be done when you leave. " >> >> >> -----Original Message----- >> From: Mario Rübsam [mailto:[EMAIL PROTECTED] >> Sent: Wednesday, May 31, 2006 9:54 PM >> To: [email protected] >> Subject: Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB >> >> Santosh, >> >> it works, but only in the following situation: >> >> open connection --> call update --> commit >> >> >> if you I do a: >> >> open connection --> call query --> call update --> commit >> >> no commit happen on server, like without commitbeforeautocommit=true >> >> Strange is that CommitBeforeAutocommit is a workaround for >> setAutoCommit(true) >> which I don't use. And "setAutoCommit(true)" also doesn't commit. >> >> Thanks, >> Mario >> >> >> Santosh Koti wrote: >>> Mario, >>> >>> Can u give a try for : >>> commitbeforeautocommit="true" in ur db deployment plan. >>> >>> Not sure, but may work, I had the same problem with delayed EJB's commit. >>> >>> >>> >>> Thanks, >>> Santosh. >>> "Don't talk about yourself; it will be done when you leave. " >>> >>> >>> >>> -----Original Message----- >>> From: Mario Rübsam [mailto:[EMAIL PROTECTED] >>> >>> Sent: Wednesday, May 31, 2006 7:11 PM >>> To: [email protected] >>> Subject: Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB >>> >>> The "setAutoCommit(false)" is required by MaxDB because >>> it's default is "on". >>> >>> I debugged the connections open and close and I can see >>> the connections (db sessions on the MaxDB server) come >>> and go when I do this. The only thing that not happen >>> on the server is the Connection.commit() whe I call it. >>> >>> If I use the direct driver connection I can see the >>> commit in the stats immediately after I call the commit. >>> If I use the connection from the pool no commit happens >>> on the server. >>> >>> Is there any delayed commit mechanism implemented? >>> >>> Thanks, >>> Mario >>> >>> >>> >>> Aaron Mulder wrote: >>>> The one thing that looked suspicious in your code was the >>>> setAutoCommit -- that shouldn't be done in a J2EE environment. Can >>>> you try commenting out that line and see if it makes a difference? >>>> >>>> If that doesn't help, can you put printlns near where you close the >>>> connections and just convince yourself that they're definitely being >>>> closed? >>>> >>>> Thanks, >>>> Aaron >>>> >>>> On 5/31/06, Mario Rübsam <[EMAIL PROTECTED]> wrote: >>>>> Hi, >>>>> >>>>> I testet a bit further and tried the following situations: >>>>> >>>>> I used a direct driver connection without the Geronimo pool --> working >>>>> >>>>> I disabled all write access to the tables were the hang occurs, >>>>> only read access enabled --> working >>>>> >>>>> I checked (with the MaxDB Database Manager) the connections/db >>>>> sessions all >>>>> open and close correct so thats not the problem. >>>>> >>>>> So my conclusion is that there is something different with the >>>>> transactions and Tranql. I debugged the commits and if I use the >>>>> direct driver connection I can see the commits in the Database Manager >>>>> stats. If I commit on the Tranql connection the commit is not >>>>> registered on the MaxDB server. >>>>> >>>>> Is this now a driver or a Tranql issue? >>>>> >>>>> Thanks, >>>>> Mario >>>>> >>>>> >>>>> >>>>> Mario Rübsam wrote: >>>>>> Aaron, >>>>>> >>>>>> here is a stack trace attached, it hangs sometimes when calling the >>>>>> prepareStatement and sometimes when executing the statement and it's >>>>>> not always the same statement. >>>>>> >>>>>> Thanks, >>>>>> Mario >>>>>> >>>>>> >>>>>> >>>>>> Mario Rübsam wrote: >>>>>>> Aaron, >>>>>>> >>>>>>> connections are correctly closed. If they are returned to the pool, >>>>>>> don't know? >>>>>>> >>>>>>> The code that opens/closes connections is a bit widespread and wrapped >>>>>>> into a db abstraction layer. Here are some code fragments collected >>>>>>> together without the try catches. >>>>>>> >>>>>>> >>>>>>> open connection: >>>>>>> >>>>>>> InitialContext tCtx = new InitialContext(); >>>>>>> >>>>>>> mJDBCDataSource = (DataSource)tCtx.lookup( >>>>>>> pDataSource.getString(PDbDataSource.URL)); >>>>>>> >>>>>>> String tUser = pDataSource.getString(PDbDataSource.USER, null); >>>>>>> String tPass = pDataSource.getString(PDbDataSource.PASS, null); >>>>>>> >>>>>>> if (tUser != null) { >>>>>>> mConnection = mJDBCDataSource.getConnection(tUser, tPass); >>>>>>> } else { >>>>>>> mConnection = mJDBCDataSource.getConnection(); >>>>>>> } >>>>>>> >>>>>>> mConnection.setAutoCommit(false); >>>>>>> >>>>>>> >>>>>>> >>>>>>> call statements: >>>>>>> >>>>>>> String tStmtSQL = "SELECT ...."; >>>>>>> PreparedStatement tStmt = mConnection.prepareStatement(tStmtSQL); >>>>>>> >>>>>>> tStmt.setString(1, tName); >>>>>>> ResultSet tRS = tStmt.executeQuery(); >>>>>>> >>>>>>> while (tRS.next()) { >>>>>>> ... >>>>>>> } >>>>>>> >>>>>>> tRS.close(); >>>>>>> tStmt.close(); >>>>>>> >>>>>>> >>>>>>> >>>>>>> close the connection: >>>>>>> >>>>>>> mConnection.close(); >>>>>>> >>>>>>> >>>>>>> >>>>>>> The DataSource lookup happens only once. After that I only >>>>>>> call getConnection() on the DS and close() on the connection. >>>>>>> >>>>>>> Thanks, >>>>>>> Mario >>>>>>> >>>>>>> >>>>>>> >>>>>>> Aaron Mulder wrote: >>>>>>>> It sounds like connections are not being returned to the pool, though >>>>>>>> it's hard to know without a stack trace. Also, can you post the code >>>>>>>> you're using to access the connection pool, execute the prepared >>>>>>>> statements, and close the connection? And what kind of component is >>>>>>>> running these prepared statements? >>>>>>>> >>>>>>>> Thanks, >>>>>>>> Aaron >>>>>>>> >>>>>>>> On 5/30/06, Mario Rübsam <[EMAIL PROTECTED]> wrote: >>>>>>>>> Hi, >>>>>>>>> >>>>>>>>> I have some serious problems when executing prepared statements >>>>>>>>> on MaxDB with pooled connections managed by Tranql in Geronimo 1.0. >>>>>>>>> >>>>>>>>> The problem is, that after calling a lot of these prepared >>>>> statements >>>>>>>>> the connection will hang until I get a timout. It's always >>>>>>>>> a different statement that worked just fine some milliseconds >>>>> before. >>>>>>>>> There is no CPU load on the Geronimo machine and also no load >>>>>>>>> on the DB machine. Just a hang up until timeout. >>>>>>>>> I can run the same app on MySQL 4.x or PostgrSQL 8.x without any >>>>>>>>> Problems. Also no problems with a client app and MaxDB that do >>>>>>>>> a batch import and use exactly the same statements but without db >>>>>>>>> pooling. >>>>>>>>> >>>>>>>>> So I think it must be a db pool problem with the MaxDB or a >>>>>>>>> strange behaviour with that driver and connection pools. >>>>>>>>> >>>>>>>>> Any suggestions where I can start analysing the problem? >>>>>>>>> >>>>>>>>> here my MaxDB plan: >>>>>>>>> >>>>>>>>> <?xml version="1.0" encoding="UTF-8"?> >>>>>>>>> <connector configId="user/database-pool-jdbc/default/1/car" >>>>>>>>> xmlns="http://geronimo.apache.org/xml/ns/j2ee/connector-1.0";> >>>>>>>>> <dep:dependency >>>>>>>>> xmlns:dep="http://geronimo.apache.org/xml/ns/deployment-1.0";> >>>>>>>>> <dep:uri>mysql/sapdbc/7.6/jar</dep:uri> >>>>>>>>> </dep:dependency> >>>>>>>>> <resourceadapter> >>>>>>>>> <outbound-resourceadapter> >>>>>>>>> <connection-definition> >>>>>>>>> >>>>>>>>> >>>>> <connectionfactory-interface>javax.sql.DataSource</connectionfactory-interface> >>>>>>>>> <connectiondefinition-instance> >>>>>>>>> <name>jdbc/default</name> >>>>>>>>> <config-property-setting >>>>>>>>> name="UserName">sse</config-property-setting> >>>>>>>>> <config-property-setting >>>>>>>>> name="Password">sse</config-property-setting> >>>>>>>>> <config-property-setting >>>>>>>>> name="CommitBeforeAutocommit">false</config-property-setting> >>>>>>>>> <config-property-setting >>>>>>>>> >>>>> name="Driver">com.sap.dbtech.jdbc.DriverSapDB</config-property-setting> >>>>>>>>> <config-property-setting >>>>>>>>> >>>>> name="ExceptionSorterClass">org.tranql.connector.AllExceptionsAreFatalSorter</config-property-setting> >>>>>>>>> <config-property-setting >>>>>>>>> >>>>> name="ConnectionURL">jdbc:sapdb://192.168.8.3/service</config-property-setting> >>>>>>>>> <connectionmanager> >>>>>>>>> <local-transaction/> >>>>>>>>> <single-pool> >>>>>>>>> <max-size>100</max-size> >>>>>>>>> <min-size>50</min-size> >>>>>>>>> >>>>>>>>> <blocking-timeout-milliseconds>60000</blocking-timeout-milliseconds> >>>>>>>>> >>>>>>>>> <idle-timeout-minutes>60</idle-timeout-minutes> >>>>>>>>> <match-one/> >>>>>>>>> </single-pool> >>>>>>>>> </connectionmanager> >>>>>>>>> </connectiondefinition-instance> >>>>>>>>> </connection-definition> >>>>>>>>> </outbound-resourceadapter> >>>>>>>>> </resourceadapter> >>>>>>>>> </connector> >>>>>>>>> >>>>>>>>> >>>>>>>>> Thanks, >>>>>>>>> Mario >>>>>>>>> >>>>> ------------------------------------------------------------------------ >>>>>> DEBUG DATE=2006-05-30 TIME=13:42:22:046 CATEGORY=cpa.db >>>>> [EMAIL PROTECTED]:CDbContext.getPreparedStatement() SID=cpa.1-1-1.cpa >>>>> MESSAGE=prepare statement "SELECT >>>>> TbRegistryValues.TcValueText,TbRegistryValues.TcValueLineNo FROM >>>>> TbRegistry,TbRegistryValues WHERE >>>>> ((TbRegistry.TcEntityId=TbRegistryValues.TcEntityId) AND >>>>> (TbRegistry.TcDomain=?)) ORDER BY TbRegistryValues.TcValueLineNo" >>>>>> DEBUG DATE=2006-05-30 TIME=17:38:50:421 CATEGORY=cpa.db >>>>> [EMAIL PROTECTED]:CDbContext.getPreparedStatement() SID=cpa.1-1-1.cpa >>>>> MESSAGE=prepared statement >>>>> "[EMAIL PROTECTED]" >>>>>> 13:42:22,046 WARN [GeronimoConnectionEventListener] >>>>> connectionErrorOccurred called with null >>>>>> com.sap.dbtech.jdbc.exceptions.ConnectionException: [-708] Timeout >>>>>> at >>>>> com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:554) >>>>>> at >>>>> com.sap.dbtech.jdbc.CallableStatementSapDB.sendCommand(CallableStatementSapDB.java:1764) >>>>>> at >>>>> com.sap.dbtech.jdbc.StatementSapDB.sendSQL(StatementSapDB.java:808) >>>>>> at >>>>> com.sap.dbtech.jdbc.CallableStatementSapDB.doParse(CallableStatementSapDB.java:233) >>>>>> at >>>>> com.sap.dbtech.jdbc.CallableStatementSapDB.constructor(CallableStatementSapDB.java:186) >>>>>> at >>>>> com.sap.dbtech.jdbc.CallableStatementSapDB.<init>(CallableStatementSapDB.java:88) >>>>>> at >>>>> com.sap.dbtech.jdbc.ConnectionSapDB.prepareStatement(ConnectionSapDB.java:803) >>>>>> at >>>>> org.tranql.connector.jdbc.ConnectionHandle.prepareStatement(ConnectionHandle.java:231) >>>>>> at >>>>> com.coderesearch.cpa.db.jdbc.CDbContext.getPreparedStatement(CDbContext.java:1131) >>>>>> at >>>>> com.coderesearch.cpa.reg.db.jdbc.CDbBrokerRegistry.lookup(CDbBrokerRegistry.java:129) >>>>>> at >>>>> com.coderesearch.cpa.reg.db.jdbc.CDbBrokerRegistry.query(CDbBrokerRegistry.java:430) >>>>>> at >>>>> com.coderesearch.cpa.naming.CPANamingContextDb.lookup(CPANamingContextDb.java:116) >>>>>> at >>>>> com.coderesearch.cpa.reg.CPARegistryContext.lookup(CPARegistryContext.java:256) >>>>>> at com.coderesearch.cpa.reg.Registry.lookupInt(Registry.java:199) >>>>>> at >>>>> com.coderesearch.abp.index.srv.CRPIndex.countLogin(CRPIndex.java:140) >>>>>> at >>>>> com.coderesearch.abp.index.srv.CRPIndex.process(CRPIndex.java:266) >>>>>> at >>>>> com.coderesearch.abp.found.srv.MainServlet.process(MainServlet.java:482) >>>>>> at >>>>> com.coderesearch.abp.found.srv.MainServlet.doPost(MainServlet.java:610) >>>>>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:615) >>>>>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:688) >>>>>> at >>>>> org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428) >>>>>> at >>>>> org.apache.geronimo.jetty.JettyServletHolder.handle(JettyServletHolder.java:99) >>>>>> at >>>>> org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:830) >>>>>> at >>>>> org.mortbay.jetty.servlet.JSR154Filter.doFilter(JSR154Filter.java:170) >>>>>> at >>>>> org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:821) >>>>>> at >>>>> org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandler.java:471) >>>>>> at >>>>> org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568) >>>>>> at org.mortbay.http.HttpContext.handle(HttpContext.java:1530) >>>>>> at >>>>> org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext.java:633) >>>>>> at org.mortbay.http.HttpContext.handle(HttpContext.java:1482) >>>>>> at org.mortbay.http.HttpServer.service(HttpServer.java:909) >>>>>> at >>>>> org.mortbay.http.HttpConnection.service(HttpConnection.java:816) >>>>>> at >>>>> org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:982) >>>>>> at >>>>> org.mortbay.http.HttpConnection.handle(HttpConnection.java:833) >>>>>> at >>>>> org.mortbay.http.SocketListener.handleConnection(SocketListener.java:244) >>>>>> at >>>>> org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:357) >>>>>> at >>>>> org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:534) >>>>> -- >>>>> Dipl. Inf. Mario Rübsam >>>>> Geschäftsführer >>>>> CODERESEARCH GmbH & Co. KG >>>>> mail: [EMAIL PROTECTED] >>>>> web : www.coderesearch.com >>>>> tel : 03677/466420 >>>>> fax : 03677/466419 >>>>> >>> -- >>> >>> Dipl. Inf. Mario Rübsam >>> Geschäftsführer >>> CODERESEARCH GmbH & Co. KG >>> mail: [EMAIL PROTECTED] >>> web : www.coderesearch.com >>> tel : 03677/466420 >>> fax : 03677/466419 >>> >>> **************** CAUTION - Disclaimer ***************** >>> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended >>> solely for the use of the addressee(s). If you are not the intended >>> recipient, please notify the sender by e-mail and delete the original >>> message. Further, you are not to copy, disclose, or distribute this e-mail >>> or its contents to any other person and any such actions are unlawful. This >>> e-mail may contain viruses. Infosys has taken every reasonable precaution >>> to minimize this risk, but is not liable for any damage you may sustain as >>> a result of any virus in this e-mail. You should carry out your own virus >>> checks before opening the e-mail or attachment. Infosys reserves the right >>> to monitor and review the content of all messages sent to or from this >>> e-mail address. Messages sent to or from this e-mail address may be stored >>> on the Infosys e-mail system. >>> ***INFOSYS******** End of Disclaimer ********INFOSYS*** >>> >
