Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 8 posts ] 
Author Message
 Post subject: testConnectionOnCheckout doesn't work
PostPosted: Wed Apr 04, 2012 1:13 pm 
Newbie

Joined: Wed Apr 04, 2012 12:12 pm
Posts: 10
Hi all. I'm having a serious problem restoring the connection pool after a network interruption. For whatever reason the server network connection is occasionally broken, at which time my Spring FTP adapter starts barking errors on every poll. The FTP adapter restores itself when the network is corrected but c3p0 doesn't say anything until an attempt is made to use a connection from the pool, then I get this:

Code:
[2012-04-01 11:06:30,211|WARN|spi.SqlExceptionHelper|143] SQL Error: 17002, SQLState: null
[2012-04-01 11:06:30,212|ERROR|spi.SqlExceptionHelper|144] Io exception: Connection reset
[2012-04-01 11:06:30,214|WARN|impl.NewPooledConnection|425] [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
[2012-04-01 11:06:30,217|WARN|impl.NewPooledConnection|426] [c3p0] Another error has occurred [ java.sql.SQLException: Closed Connection ] which will not be reported to listeners!
java.sql.SQLException: Closed Connection


Rollbacks fail too, but there is at least partial information inserted during the operation:

Code:
Caused by: org.hibernate.TransactionException: unable to rollback against JDBC connection
   at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:167)
   at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.rollback(AbstractTransactionImpl.java:209)
   ... 35 more
Caused by: java.sql.SQLException: Closed Connection
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:161)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:226)
   at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:1041)
   at com.mchange.v2.c3p0.impl.NewProxyConnection.rollback(NewProxyConnection.java:855)
   at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:163)
   ... 36 more


The traffic isn't extremely heavy, maybe a dozen interactions an hour, so I thought testConnectionOnCheckout was appropriate. It looks like the connection is found broken but a new one isn't acquired, even though the network is restored by then.

My cfg.xml looks like:

Code:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
  <property name="hibernate.connection.password">*pwd*</property>
  <property name="hibernate.connection.username">*schema_name*</property>
  <property name="hibernate.connection.url">jdbc:oracle:thin:@192.168.100.242:1521:prod</property>
  <property name="hibernate.dialect">org.hibernate.dialect.Oracle9iDialect</property>
  <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
  <property name="hibernate.search.autoregister_listeners">false</property>
  <property name="hibernate.default_entity_mode">pojo</property>
  <property name="hibernate.show_sql">false</property>
  <property name="hibernate.c3p0.min_size">5</property>
  <property name="hibernate.c3p0.max_size">100</property>
  <property name="hibernate.c3p0.timeout">1800</property>
  <property name="hibernate.c3p0.max_statements">50</property>
  <property name="hibernate.c3p0.acquire_increment">5</property>
  <property name="hibernate.c3p0.idle_test_period">300</property>
  <mapping resource=" * ... * "/>
</session-factory>
</hibernate-configuration>


And the c3p0-config.xml file is (any tips on the preferredTestQuery? I'm letting it use the default.):

Code:
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
     <default-config> 
        <property name="testConnectionOnCheckout">true</property> 

        <!--  Including the preferredTestQuery property with variations of "select 1 from dual;"
              causes the application to HANG when initiating the pool.
              Variations tried:
              select 1 from dual
              select 1 from dual;
              select * from dual
              select * from dual;
              select 1
              select 1;
         -->
        <!--  <property name="preferredTestQuery">SELECT 1</property>  -->        
      <property name="acquireRetryAttempts">30</property>
      <property name="acquireRetryDelay">1000</property>
      <property name="breakAfterAcquireFailure">false</property>
      <!-- <property name="unreturnedConnectionTimeout">1800</property>  -->
      <property name="debugUnreturnedConnectionStackTraces">true</property>        
   </default-config>
</c3p0-config>


As a work around I am trying to detect the error sent to the logs and restart the application context. Ugly! Any help would be appreciated.


Last edited by fleabit on Thu Apr 05, 2012 4:06 pm, edited 1 time in total.

Top
 Profile  
 
 Post subject: Re: testConnectionOnCheckout doesn't work
PostPosted: Thu Apr 05, 2012 10:49 am 
Newbie

Joined: Wed Apr 04, 2012 12:12 pm
Posts: 10
Any feedback / ideas would be great. If there is anything that stands out as a question or possible problem, just being aware of it would help. The properties file, including testing the connection, are the result of trying to prevent the exceptions.

I can't seem to trap the java.net.UnknownHostException that happens at the FTP adapter, which would give me early warning of a network outage.

I can't detect and prevent the invalid connection that causes spi.SqlExceptionHelper - Io exception: Connection reset, which would prevent the java.sql.SQLException: Closed Connection exception that starts with com.mchange.v2.c3p0.impl.NewProxyConnection.rollback(NewProxyConnection.java:855)
at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:163), obviously on the rollback attempt.

Does anyone have references or clear methods of trapping any of this? Leaving the spring adapter out of it, is there anything that works to prevent a dead connection from the pool?


Top
 Profile  
 
 Post subject: Re: testConnectionOnCheckout doesn't work
PostPosted: Fri Apr 13, 2012 3:53 pm 
Newbie

Joined: Wed Apr 04, 2012 12:12 pm
Posts: 10
Is there anything visibly wrong with the posted config, or anything unclear about the problem?
The application has been stable for several days, probably due to a stable network, but today I got the error below. It might be related to the other problem, but it only occurred once so I assume the referenced 'resource' was eventually closed.

Any suggestions would be appreciated!


Code:
[2012-04-13 12:48:13,864|INFO|impl.NewPooledConnection|650] [c3p0] Exceptions occurred while trying to close a PooledConnection's resources normally.
[2012-04-13 12:48:13,875|INFO|impl.NewPooledConnection|660] [c3p0] NewPooledConnection close Exception.
java.sql.SQLException: Io exception: Connection reset
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:161)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:273)
   at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:405)
   at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:1082)
   at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:474)
   at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:191)
   at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:470)
   at com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:964)
   at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
[2012-04-13 12:48:13,878|WARN|resourcepool.BasicResourcePool|972] Failed to destroy resource: com.mchange.v2.c3p0.impl.NewPooledConnection@5f847e53
java.sql.SQLException: Some resources failed to close properly while closing com.mchange.v2.c3p0.impl.NewPooledConnection@5f847e53
   at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:496)
   at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:191)
   at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:470)
   at com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:964)
   at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)


Top
 Profile  
 
 Post subject: Re: testConnectionOnCheckout doesn't work
PostPosted: Mon Apr 16, 2012 6:04 am 
Senior
Senior

Joined: Fri May 08, 2009 12:27 pm
Posts: 168
This sounds more like a Hibernate than a Hibernate Tools problem.
Or maybe even a C3P0 problem. Dunno.

I guess nobody really tests connection-lost conditions because it's not easy to automate that.
Plus, network failures are so rare nowadays that people spend their time on other issues.We certainly do here, even though I did have some similar crashes. Our software needs to be restartable in its batch runs anyway, so we don't care that much and simply hit the Start button again. (I'm aware that this is not an option for all projects.)


Top
 Profile  
 
 Post subject: Re: testConnectionOnCheckout doesn't work
PostPosted: Mon Apr 16, 2012 9:13 am 
Newbie

Joined: Wed Apr 04, 2012 12:12 pm
Posts: 10
Thanks for your response.

That's more or less what I'm doing now; watching the log files and restarting the process when there's a problem. If it goes on for any amount of time though I have a lot of partially inserted data that I have to clean up and manually rebuild by dropping the source files into the FTP folder that's being monitored.

I need a way to detect and stop it from processing. Then I can take steps to restart manually or automatically when it's resolved. The problem I'm having is that these aren't being caught by try/catch at the point of the .persist or at the .commit and Hibernate doesn't seem to prevent it based on the testConnection config settings.

Is there something I'm missing in the config?


Top
 Profile  
 
 Post subject: Re: testConnectionOnCheckout doesn't work
PostPosted: Mon Apr 16, 2012 10:23 am 
Senior
Senior

Joined: Fri May 08, 2009 12:27 pm
Posts: 168
Frankly, I don't know if there's anything wrong with the config.
I suggest moving the question to the appropriate forum. This forum is about Hibernate Tools, the Eclipse add-on, not about the Hibernate library itself; I also suppose that the Hibernate forum is less dead than this one.


Top
 Profile  
 
 Post subject: Re: testConnectionOnCheckout doesn't work
PostPosted: Sat Apr 21, 2012 10:18 am 
Newbie

Joined: Wed Apr 04, 2012 12:12 pm
Posts: 10
Thanks for your response. As Joachim pointed out, the Tools forum is probably the wrong place for the post. I've re-posted here:

https://forum.hibernate.org/viewtopic.php?f=1&t=1015229

But the question is basically how to restore the connection pool and prevent the errors shown after a network interruption, and, why does testConnectionOnCheckout = true allow this problem to happen in the first place?


Top
 Profile  
 
 Post subject: Re: testConnectionOnCheckout doesn't work
PostPosted: Mon Apr 23, 2012 8:52 am 
Senior
Senior

Joined: Fri May 08, 2009 12:27 pm
Posts: 168
I suspect Riley103 is a link spammer.
First-time poster, pointless posting, a link to something that wants traffic... that's a pretty strong indicator.
(Unfortunately, there is little if any moderation going on in the forum.)


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 8 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.