Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 1 post ] 
Author Message
 Post subject: c3p0 - testConnectionOnCheckout, exception trapping problem
PostPosted: Fri Apr 20, 2012 10:27 am 
Newbie

Joined: Wed Apr 04, 2012 12:12 pm
Posts: 10
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 there's nothing logged by hibernate or c3p0 until a connection is used 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 hibernate.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>



Here is a snippet from the most common occurrence in code:

Code:
      if (!error){
         session.beginTransaction();
         
         for(ProcessMessage pMsg : oMessages){
            ...
               
               entry = new CltestLogStat();
               
               entry.setCustNo(cust_no);
               entry.setReqno(reqno);
               
               entry.setSourcePath(dpath);
               entry.setSourceName(fname);
               entry.setMessage(msgPart);
               entry.setProcessStep(pMsg.getClassname()+"."+pMsg.getMethod());
               entry.setStatus(status);   
                       
               entry.setDatetime(msg_date);
   
               if (!msg_err){
                  try {      
                     session.persist(entry);
                     session.flush();   
                     session.evict(entry);   
                  } catch (HibernateException e){         
                     pMessage ...

/*
    This error occurs at the following rollback:
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

Enclosing the rollback in a try / catch (java.sql.SQLException) gives a compile error that the SQLException is never thrown.

*/

                      session.getTransaction().rollback();
                  } catch (Throwable t){   
                     pMessage ...      
                      error = true;
                  }
               }
               ...
         }
         
         try {
            session.getTransaction().commit();            
         } catch (HibernateException he){      
            pMessage ...
             session.getTransaction().rollback();
         }
      }


Any feedback / ideas would be great. I can't seem to trap the java.net.UnknownHostException that happens at the FTP adapter. It would give that application early warning of a outage if I could.

I can't detect and prevent the invalid connection that causes spi.SqlExceptionHelper - Io exception: Connection reset.

- 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  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

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.