-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: Auto reconnect to Database using c3p0 connection pool
PostPosted: Tue Oct 25, 2005 2:59 am 
Newbie

Joined: Tue Oct 25, 2005 2:45 am
Posts: 2
Hi,

How can I reconnect to the database, when a network failure/database server is down and up.?

I am using c3p0 connetion pool properties with hibernate. I set the following in hibernate.properties.

hibernate.c3p0.timeout=0
hibernate.connection.autoReconnect=true
hibernate.connection.driver_class=oracle.jdbc.driver.OracleDriver
hibernate.c3p0.max_statements=10
hibernate.dialect=net.sf.hibernate.dialect.Oracle9Dialect
hibernate.c3p0.max_size=15
hibernate.c3p0.idle_test_period=30
hibernate.c3p0.validate=true
hibernate.c3p0.min_size=1

Eventhough I set hibernate.connection.autoReconnect=true its not getting connected to DB.

Your reply is appreciated.
Thanking you.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Oct 25, 2005 4:50 am 
Beginner
Beginner

Joined: Fri Aug 20, 2004 3:45 pm
Posts: 22
Location: Florence - ITALY
For an old project (hibernate 2) I was using this configuration:

hibernate.properties
Code:
hibernate.c3p0.max_size=20
hibernate.c3p0.idle_test_period=30

c3p0.properties
Code:
c3p0.testConnectionOnCheckin=true
c3p0.minPoolSize=1
c3p0.maxPoolSize=10
c3p0.checkoutTimeout=10000
c3p0.maxIdleTime=80
c3p0.automaticTestTable=test_c3p0
c3p0.acquireRetryAttemps=100000
c3p0.acquireRetryDelay=30
c3p0.breakAfterAcquireFailure=false


I made many tests and this configuration do the works.

Gianni
Florence - ITALY


Top
 Profile  
 
 Post subject:
PostPosted: Tue Oct 25, 2005 11:43 am 
Newbie

Joined: Tue Oct 25, 2005 2:45 am
Posts: 2
Hi Gianni,

Thanks for your reply. But Still I have hard time here.

I have set following in hibernate.properties

hibernate.c3p0.timeout=100
hibernate.connection.autoReconnect=true
hibernate.connection.driver_class=oracle.jdbc.driver.OracleDriver
hibernate.c3p0.max_statements=10
hibernate.dialect=net.sf.hibernate.dialect.Oracle9Dialect
hibernate.c3p0.max_size=15
hibernate.c3p0.idle_test_period=30
hibernate.c3p0.validate=true
hibernate.c3p0.min_size=1
hibernate.connection.autoReconnectForPools=true

The following in c3p0.properties

c3p0.acquireRetryDelay=1000
c3p0.acquireRetryAttempts=100000
c3p0.breakAfterAcquireFailure=false
c3p0.autoCommitOnClose=true
c3p0.testConnectionOnCheckin=true

My work around is as follows:

After starting up my Server ( JBoss) , I used to unplug my network connection ,which inturn automatically disconnect my application from database server. Then after some time I re-plug the network.
Now, the auto reconnection is not getting.


I got the following exception :

19:11:19,363 INFO [NewPooledConnection] [c3p0] Exceptions occurred while trying
to close a PooledConnection's resources normally.
19:11:19,363 INFO [NewPooledConnection] [c3p0] NewPooledConnection close Except
ion.
java.sql.SQLException: Io exception: Connection reset by peer: socket write erro
r
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:274)
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:393)
at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:1
036)
at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnectio
n.java:405)
at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnectio
n.java:135)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.destroyResource(C
3P0PooledConnectionPool.java:208)
at com.mchange.v2.resourcepool.BasicResourcePool$5.run(BasicResourcePool
.java:634)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(Thre
adPoolAsynchronousRunner.java:368)
19:11:19,378 WARN [BasicResourcePool] Failed to destroy resource: com.mchange.v
2.c3p0.impl.NewPooledConnection@1b90c55
java.sql.SQLException: Some resources failed to close properly while closing com
.mchange.v2.c3p0.impl.NewPooledConnection@1b90c55
at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnectio
n.java:418)
at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnectio
n.java:135)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.destroyResource(C
3P0PooledConnectionPool.java:208)
at com.mchange.v2.resourcepool.BasicResourcePool$5.run(BasicResourcePool
.java:634)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(Thre
adPoolAsynchronousRunner.java:368)
19:11:46,784 WARN [BasicResourcePool] BasicResourcePool: An idle resource is br
oken and will be purged.
java.sql.SQLException: Connection is invalid
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.testPooledConnect
ion(C3P0PooledConnectionPool.java:196)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.refurbishIdleReso
urce(C3P0PooledConnectionPool.java:136)
at com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTa
sk.run(BasicResourcePool.java:1394)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(Thre
adPoolAsynchronousRunner.java:368)
19:11:46,800 WARN [BasicResourcePool] BasicResourcePool: An idle resource is br
oken and will be purged.
java.sql.SQLException: Connection is invalid
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.testPooledConnect
ion(C3P0PooledConnectionPool.java:196)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.refurbishIdleReso
urce(C3P0PooledConnectionPool.java:136)
at com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTa
sk.run(BasicResourcePool.java:1394)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(Thre
adPoolAsynchronousRunner.java:368)
19:11:46,800 WARN [BasicResourcePool] BasicResourcePool: An idle resource is br
oken and will be purged.
java.sql.SQLException: Connection is invalid
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.testPooledConnect
ion(C3P0PooledConnectionPool.java:196)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.refurbishIdleReso
urce(C3P0PooledConnectionPool.java:136)
at com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTa
sk.run(BasicResourcePool.java:1394)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(Thre
adPoolAsynchronousRunner.java:368)
19:11:46,816 WARN [BasicResourcePool] BasicResourcePool: An idle resource is br
oken and will be purged.
java.sql.SQLException: Connection is invalid
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.testPooledConnect
ion(C3P0PooledConnectionPool.java:196)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.refurbishIdleReso
urce(C3P0PooledConnectionPool.java:136)
at com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTa
sk.run(BasicResourcePool.java:1394)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(Thre
adPoolAsynchronousRunner.java:368)
19:11:46,816 WARN [BasicResourcePool] BasicResourcePool: An idle resource is br
oken and will be purged.
java.sql.SQLException: Connection is invalid
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.testPooledConnect
ion(C3P0PooledConnectionPool.java:196)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.refurbishIdleReso
urce(C3P0PooledConnectionPool.java:136)
at com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTa
sk.run(BasicResourcePool.java:1394)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(Thre
adPoolAsynchronousRunner.java:368)
19:12:06,769 WARN [ThreadPoolAsynchronousRunner] com.mchange.v2.async.ThreadPoo
lAsynchronousRunner$DeadlockDetector@b8f09c -- APPARENT DEADLOCK!!! Creating eme
rgency threads for unassigned pending tasks!
19:12:06,769 WARN [ThreadPoolAsynchronousRunner] com.mchange.v2.async.ThreadPoo
lAsynchronousRunner$DeadlockDetector@b8f09c -- APPARENT DEADLOCK!!! Complete Sta
tus: [num_managed_threads: 3, num_active: 3; activeTasks: com.mchange.v2.resourc
epool.BasicResourcePool$5@1bcb91e (com.mchange.v2.async.ThreadPoolAsynchronousRu
nner$PoolThread-#1), com.mchange.v2.resourcepool.BasicResourcePool$5@11d6176 (co
m.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0), com.mchange.v2.r
esourcepool.BasicResourcePool$5@1e990b4 (com.mchange.v2.async.ThreadPoolAsynchro
nousRunner$PoolThread-#2); pendingTasks: com.mchange.v2.c3p0.stmt.GooGooStatemen
tCache$2@11b1b6d, com.mchange.v2.resourcepool.BasicResourcePool$5@a5bfd5, com.mc
hange.v2.c3p0.stmt.GooGooStatementCache$2@1d56250, com.mchange.v2.c3p0.stmt.GooG
ooStatementCache$2@298bbf, com.mchange.v2.resourcepool.BasicResourcePool$5@195c1
4c, com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@c5a1ea, com.mchang
ge.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0); pendingTasks: com.mchan
ge.v2.resourcepool.BasicResourcePool$AcquireTask@62e21b]
19:13:12,737 WARN [JDBCExceptionReporter] SQL Error: 17002, SQLState: null
19:13:12,737 ERROR [JDBCExceptionReporter] Io exception: Connection reset by pee
r: socket write error
19:13:12,737 WARN [JDBCExceptionReporter] SQL Error: 17002, SQLState: null
19:13:12,737 ERROR [JDBCExceptionReporter] Io exception: Connection reset by pee
r: socket write error
19:13:12,753 WARN [TransactionImpl] XAException: tx=TransactionImpl:XidImpl [Fo
rmatId=257, GlobalId=SHANMUGAM//481, BranchQual=] errorCode=XA_UNKNOWN(0)
org.jboss.resource.connectionmanager.JBossLocalXAException: could not commit loc
al tx; - nested throwable: (org.jboss.resource.JBossResourceException: SQLExcept
ion; - nested throwable: (java.sql.SQLException: Closed Connection))
at org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResou
rce.commit(TxConnectionManager.java:701)
at org.jboss.tm.TransactionImpl.commitResources(TransactionImpl.java:165
6)
at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:375)
at org.jboss.ejb.plugins.TxInterceptorCMT.endTransaction(TxInterceptorCM
T.java:458)
at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxIntercep
torCMT.java:326)
at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:1
50)
at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.


Please any one help me on this issue.

Thanks in advance.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 26, 2005 2:46 pm 
Beginner
Beginner

Joined: Fri Aug 20, 2004 3:45 pm
Posts: 22
Location: Florence - ITALY
Try to use ALL the property of my properties file.
For example, use:

Code:
c3p0.checkoutTimeout=10000
c3p0.automaticTestTable=test_c3p0


Code:
c3p0.maxIdleTime=80

This property seem to be very useful. With the default value "0", the connection never expire. See the manul:

Quote:
maxIdleTime
Default: 0
Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.


I think that the property maxIdleTime, maybe solve youre problem.

Gianni

P.S. Please, rate to "yes" my answer if solve your problem.[/b]


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 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.