geronimo-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Zakharov, Vasily M" <vasily.m.zakha...@intel.com>
Subject RE: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
Date Wed, 31 May 2006 18:19:19 GMT
Mario,

You could use the Derby database that is built-in in Geronimo.
Would it help?

 Vasily


-----Original Message-----
From: Mario Rubsam [mailto:mr@coderesearch.com] 
Sent: Wednesday, May 31, 2006 10:06 PM
To: user@geronimo.apache.org
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: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.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
>>>>> 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