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