If the DB goes down at on point, even after the DB is up again all following request will fail

[Morbo]

If the DB connection fail for one moment (Cannot open connection), even after the DB in up againt all following request will fail too.
For now I have to restart the play application (1.0.1) to make it work again.
I guess something is not rebuild or checked.
Maybe in play.db.jpa.JPAPlugin.startTx(JPAPlugin.java:227)
Regards,

Here is the Exception I got after restarting the DB (oracle 10G) :

@61p8f726i
Internal Server Error (500) for request GET /interfaces

Oops: PersistenceException
An unexpected error occured caused by exception PersistenceException:
org.hibernate.exception.GenericJDBCException: Cannot open connection

play.exceptions.UnexpectedException: Unexpected Error
        at play.Invoker$Invocation.onException(Invoker.java:142)
        at play.Invoker$Invocation.run(Invoker.java:182)
        at Invocation.HTTP Request(Play!)
Caused by: javax.persistence.PersistenceException:
org.hibernate.exception.GenericJDBCException: Cannot open connection
        at
org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:
614)
        at org.hibernate.ejb.TransactionImpl.begin(TransactionImpl.java:41)
        at play.db.jpa.JPAPlugin.startTx(JPAPlugin.java:227)
        at play.db.jpa.JPAPlugin.beforeInvocation(JPAPlugin.java:190)
        at play.Invoker$Invocation.before(Invoker.java:114)
        at play.Invoker$Invocation.run(Invoker.java:175)
        ... 1 more
Caused by: org.hibernate.exception.GenericJDBCException: Cannot open
connection
        at
org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:
126)
        at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:
114)
        at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:
66)
        at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:
52)
        at
org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:
449)
        at
org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:
167)
        at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)
        at
org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:
85)
        at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:
1354)
        at org.hibernate.ejb.TransactionImpl.begin(TransactionImpl.java:38)
        ... 5 more
Caused by: java.sql.SQLException: An SQLException was provoked by the
following failure: com.mchange.v2.resourcepool.ResourcePoolException:
Attempted to use a closed or broken resource pool
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:62)
        at
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:
531)
        at
com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:
128)
        at
org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:
46)
        at
org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:
446)
        ... 10 more
Caused by: com.mchange.v2.resourcepool.ResourcePoolException:
Attempted to use a closed or broken resource pool
        at
com.mchange.v2.resourcepool.BasicResourcePool.ensureNotBroken(BasicResourcePool.java:
1632)
        at
com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:
524)
        at
com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:
477)
        at
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:
525)
        ... 13 more

[Marcos Silva Pereira (marcospereira)]

Just to add some information about this issue, you can configure C3P0 pool to check connections periodically:

http://www.mchange.com/projects/c3p0/index.html#configuring_connection_testing

Not sure about that, but maybe you just need to add the following property in you application.conf:

hibernate.c3p0.idleConnectionTestPeriod=5 #or any other number of seconds

[Morbo (philippe-gauthier-inserm)]

Hi,
Thanks for the pointer, that's probably the right direction.
I just tried some of parameters in the application.conf but unfortunately it's not enough to solve the problem.
The application is still blocked.

It seems I have fewer Exception though.

I tried serveral params like :
hibernate.c3p0.idleConnectionTestPeriod=5
hibernate.c3p0.acquireRetryAttempts=1000
hibernate.c3p0.acquireRetryDelay=10000
hibernate.c3p0.breakAfterAcquireFailure=false

I alway have the following exception :
Caused by: java.sql.SQLException: An SQLException was provoked by the following
failure: com.mchange.v2.resourcepool.ResourcePoolException: Attempted to use a closed or broken resource pool
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:62)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:531)

[Marcos Silva Pereira (marcospereira)]

Just sending a patch that enables users to create a conf/c3p0.properties that will be readed by DBPlugin.

[Morbo (philippe-gauthier-inserm)]

Hi,
So in the forum we found the answer was to add :
ds.setBreakAfterAcquireFailure(false);
see :
http://bazaar.launchpad.net/~play-developers/play/1.0/revision/1080
Adding the pach of Marcos could be great to fine tune the DB pool.
U can close this I guess.
Best regards

[Oded Peer (oded-50)]

You can work around this by changing the connection pool configuration programatically by adding your own plugin.
DBPlugin sets the configuration in onApplicationStart(), so in your plugin change the configuration in the afterApplicationStart() method, for example:

public class MyPlugin extends PlayPlugin {
 public void afterApplicationStart() {
  Properties p = Play.configuration;

  // change the main Play datasource configuration
  ComboPooledDataSource mainDataSource = (ComboPooledDataSource)DB.datasource;
  mainDataSource.setAcquireRetryAttempts( Integer.parseInt( p.getProperty( "db.retry.attempts", "2" ) ) );
  mainDataSource.setAcquireRetryDelay( Integer.parseInt( p.getProperty( "db.retry.delay", "200" ) ) );
  mainDataSource.setBreakAfterAcquireFailure( false );
        }
}