Hope were are not deviating u too much from the actual problem :} Thanks, Santosh. "Don't talk about yourself; it will be done when you leave. "
-----Original Message----- From: Santosh Koti [mailto:[EMAIL PROTECTED] Sent: Thursday, June 01, 2006 12:08 AM To: [email protected] Subject: RE: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB Mario, It should be possible , the Derby process inside the Geronimo runs on the port : 1527 Driver URL: jdbc:derby://<localhost/ip>:1527 Thanks, Santosh. -----Original Message----- From: Mario Rübsam [mailto:[EMAIL PROTECTED] Sent: Thursday, June 01, 2006 12:06 AM To: [email protected] Subject: Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB Vasily, I have no idea how to access the derby instance in Geronimo from our client app dbadmin and importer. I thought I need the server version to do this? Is there any way to connect to the G embedded Derby from an application that is running outside of Geronimo? Thanks, Mario Zakharov, Vasily M wrote: > 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*** >>>> >
