Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 6 posts ] 
Author Message
 Post subject: c3p0 - testConnectionOnCheckout, exception trapping problem
PostPosted: Fri Apr 20, 2012 10:28 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  
 
 Post subject: Re: c3p0 - testConnectionOnCheckout, exception trapping problem
PostPosted: Mon Apr 23, 2012 5:05 am 
Expert
Expert

Joined: Tue Jun 16, 2009 3:36 am
Posts: 988
Quote:
is there anything that works to prevent a dead connection from the pool?


see c3po parameter testConnectionOnCheckout
Quote:
there's nothing logged by hibernate or c3p0 until a connection is used


you configured c3po to check periodically its pooled connections by idleTestPeriod=300
so it shold even detect dead pooled connection before them get cheched out by hibernate if you wait long enough.

Quote:
Rollbacks fail too, ...


While you are in transaction you are working with a checked out connection.
C3po only tests pooled (= idle) connections not checked out connections.
If a checked out connection is broken, then you can do nothing anymore to re-establish it.
Probably on database side, the transaction has already been rollbacked,
so actually you just have to close the hibernate session and to repeat your work-unit from scratch.


Top
 Profile  
 
 Post subject: Re: c3p0 - testConnectionOnCheckout, exception trapping problem
PostPosted: Mon Apr 23, 2012 1:04 pm 
Newbie

Joined: Wed Apr 04, 2012 12:12 pm
Posts: 10
I see that at least some of the data is inserted, even as these exceptions are happening, but there are sometimes hundreds of records being processed from file so I wasn't able to verify that all are inserted, and with that many problems in the log, I don't trust it.

Quote:
you configured c3po to check periodically its pooled connections by idleTestPeriod=300
so it shold even detect dead pooled connection before them get cheched out by hibernate if you wait long enough.

So, as you're saying, either the testConnectionOnCheckout OR the idleTestPeriod should check and prevent this... but it's happening. Do my config settings conflict with each other?


Thanks.


Top
 Profile  
 
 Post subject: Re: c3p0 - testConnectionOnCheckout, exception trapping problem
PostPosted: Mon Apr 23, 2012 1:31 pm 
Newbie

Joined: Wed Apr 04, 2012 12:12 pm
Posts: 10
Does the defaultQuery problem I referenced have anything to do with the testConnectionOnCheckout not working?


Top
 Profile  
 
 Post subject: Re: c3p0 - testConnectionOnCheckout, exception trapping problem
PostPosted: Tue Apr 24, 2012 2:36 am 
Expert
Expert

Joined: Tue Jun 16, 2009 3:36 am
Posts: 988
Quote:
I see that at least some of the data is inserted,


Which Isolation level are you using when verifying this?
If you use READ_UNCOMMITED (dirty-read) isolation level then it may be,
because the database engine did not recognized yet, that the connection to that transaction is broken,
and thus not having rollbacked yet the transaction. This would mean, you see uncommited inserts.

If you see that partial data inserted by accessing the database with READ_COMMITED isolation level (or higher),
then there must have been intermediate commits.
Quote:
Does the defaultQuery problem I referenced have anything to do with the testConnectionOnCheckout not working?


How do you define hibernate.c3p0.preferredTestQuery ?


Top
 Profile  
 
 Post subject: Re: c3p0 - testConnectionOnCheckout, exception trapping problem
PostPosted: Tue Apr 24, 2012 7:54 am 
Newbie

Joined: Wed Apr 04, 2012 12:12 pm
Posts: 10
Thanks for your reply. I see that I have some of the data inserted by using Oracle's SQL Developer and looking at the table contents after I discover the exceptions in the logs. So it seems these are committed results. Until now I've been unable to trap these errors so I cant even send myself an email notice. I'm tied to this thing 24/7.

In my original post, in the c3p0 properties xml, I have notes in comments about the experience with preferred test query:

Code:
       <!--  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>  -->


But, with preferredTestQuery removed the default "getTables" should be used, correct?
http://www.mchange.com/projects/c3p0/index.html#preferredTestQuery


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 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.