Mario, Just give another try (of course a wild advise :-))
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 ?? If so , some configuration needs to be looked at ? ! 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*** > -- 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
