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?
Login
FAQ
Search
