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*** >> > -- 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
