geronimo-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Jencks <david_jen...@yahoo.com>
Subject Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
Date Wed, 31 May 2006 21:19:18 GMT
Mario,

I don't see an obvious way for this stuff to break, but if you are  
set up for debugging you could get the tranql connector source code  
and trace through starting at  
org.tranql.connector.jdbc.ConnectionHandle.commit().  I don't see how  
the commit call wouldn't get eventually to the db, but knowing which  
path execution takes should make it simpler to figure out what is  
going on.

BTW someone was mentioning setting CommitBeforeAutocommit to true.   
You should only set this on very buggy jdbc drivers that violate the  
jdbc spec by not committing pending work when you set autocommit to  
true (axion).  Any real database should not need this set.

thanks
david jencks

On May 31, 2006, at 11:05 AM, Mario Rübsam wrote:

> 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:Santosh_Koti@infosys.com] Sent:  
>> Wednesday, May 31, 2006 11:10 PM
>> To: user@geronimo.apache.org
>> 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:mr@coderesearch.com] Sent: Wednesday,  
>> May 31, 2006 10:55 PM
>> To: user@geronimo.apache.org
>> 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:mr@coderesearch.com] Sent: Wednesday,  
>>> May 31, 2006 9:54 PM
>>> To: user@geronimo.apache.org
>>> 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:mr@coderesearch.com]
>>>>
>>>> Sent: Wednesday, May 31, 2006 7:11 PM
>>>> To: user@geronimo.apache.org
>>>> 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 <mr@coderesearch.com> 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 <mr@coderesearch.com>
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.AllExceptionsAre

>>>>>> FatalSorter</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
>>>>>> SOURCE=mr@aeon: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
>>>>>> SOURCE=mr@aeon:CDbContext.getPreparedStatement() SID=cpa. 
>>>>>> 1-1-1.cpa
>>>>>> MESSAGE=prepared statement
>>>>>> "org.tranql.connector.jdbc.PreparedStatementHandle@1728a26"
>>>>>>> 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: mr@coderesearch.com
>>>>>> web : www.coderesearch.com
>>>>>> tel : 03677/466420
>>>>>> fax : 03677/466419
>>>>>>
>>>> --
>>>>
>>>> Dipl. Inf. Mario Rübsam
>>>> Geschäftsführer
>>>> CODERESEARCH GmbH & Co. KG
>>>> mail: mr@coderesearch.com
>>>> 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***
>>>>
>


Mime
View raw message