Handling multiple datasource in a smart way

[Mihalis]

Hi all,

Here are the characteristics of my projet :
- Rich Client (RCP)
- Services are represented by Beans managed by Spring,
- We access 2 SQL SERVER databases :
* Hibernate,
* C3P0 Connection pooling
* JTDS Driver

Here is an excerpt of the configuration file :

<!-- DataSource Property -->
 <bean id="OpenlyTrMciDS" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
  <property name="driverClass"><value>net.sourceforge.jtds.jdbc.Driver</value></property>
  <property name="jdbcUrl"><value>jdbc:sqlserver://10.205.100.94</value></property>
  <property name="user"><value>***</value></property>
  <property name="password"><value>***</value></property>
  <property name="acquireRetryAttempts" value="2"></property>
         <property name="preferredTestQuery" value="SELECT 1"></property> 
         <property name="testConnectionOnCheckin" value="true"></property>
         <property name="testConnectionOnCheckout" value="false"></property>
         <property name="maxIdleTime" value="50"></property>
         <property name="idleConnectionTestPeriod" value="30"></property>  
 </bean>
 
 <!-- Database Property -->
 <bean id="OpenlyTrMciProprietes"
  class="org.springframework.beans.factory.config.PropertiesFactoryBean">
  <property name="properties">
   <props>
    <prop key="hibernate.current_session_context_class">thread</prop>
    <prop key="hibernate.default_batch_fetch_size">10</prop>
    <prop key="hibernate.default_catalog">MCI</prop>
    <prop key="hibernate.default_schema">dbo</prop>
    <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
    <prop key="hibernate.format_sql">true</prop>
    <prop key="hibernate.show_sql">false</prop>
    <prop key="hibernate.jdbc.batch_versioned_data">false</prop>
    <prop key="hibernate.jdbc.use_streams_for_binary">false</prop>
    <prop key="hibernate.max_fetch_depth">10</prop>
    <prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</prop>
                 <prop key="hibernate.c3p0.acquire_increment">1</prop>
                 <prop key="hibernate.c3p0.minPoolSize">2</prop>
                 <prop key="hibernate.c3p0.maxPoolSize">50</prop>
                <prop key="hibernate.c3p0.timeout">1800</prop>
          <prop key="hibernate.c3p0.max_statement">0</prop>  
          <prop key="hibernate.c3p0.idle_test_period">30</prop>
   </props>
  </property>
 </bean>
 
 <!-- Hibernate SessionFactory -->
 <bean id="OpenlyTrMciSessionFactory"    class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
  <property name="dataSource"><ref bean="OpenlyTrMciDS" /></property>
  <property name="useTransactionAwareDataSource"><value>true</value></property>
  <property name="hibernateProperties" ref="OpenlyTrMciProprietes" />
  <property name="configLocation"><value>classpath:fr/gtietransport/openly/hbref/mci/config/hibernate_MCI.cfg.xml</value></property>
 </bean>
 
 <!-- enable the configuration of transactional behavior based on annotations -->
 <tx:annotation-driven transaction-manager="txManager" />
 
 <!-- a PlatformTransactionManager is still required -->
 <bean id="txManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
  <property name="sessionFactory" ref="OpenlyTrMciSessionFactory" />
 </bean>

+ the connection to the other database (with a new Transaction manager, new datasource, new session factory).

Everything works fine, but this morning one database crashed, and our application can not run :

2007-08-02 09:47:35,082 WARN com.mchange.v2.resourcepool.BasicResourcePool: com.mchange.v2.resourcepool.BasicResourcePool$Acqu ireTask@1e26602 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (2).
Exception in thread "main" org.eclipse.swt.SWTException: Failed to execute runnable (org.springframework.transaction.CannotCreateTrans actionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection)
at org.eclipse.swt.SWT.error(SWT.java:3499)
at org.eclipse.swt.SWT.error(SWT.java:3422)
at org.eclipse.swt.widgets.Synchronizer.runAsyncMessa ges(Synchronizer.java:126)
at org.eclipse.swt.widgets.Display.runAsyncMessages(D isplay.java:3650)
at org.eclipse.swt.widgets.Display.readAndDispatch(Di splay.java:3287)
...
Caused by: org.springframework.transaction.CannotCreateTransa ctionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.springframework.orm.hibernate3.HibernateTransa ctionManager.doBegin(HibernateTransactionManager.j ava:538)
at org.springframework.transaction.support.AbstractPl atformTransactionManager.getTransaction(AbstractPl atformTransactionManager.java:350)
at org.springframework.transaction.interceptor.Transa ctionAspectSupport.createTransactionIfNecessary(Tr ansactionAspectSupport.java:262)
at org.springframework.transaction.interceptor.Transa ctionInterceptor.invoke(TransactionInterceptor.jav a:102)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :161)
at org.springframework.aop.framework.Cglib2AopProxy$D ynamicAdvisedInterceptor.intercept(Cglib2AopProxy. java:630)
at fr.gtietransport.openly.service.alarmes.ServiceInf oAlarmes$$EnhancerByCGLIB$$70a8e464.retrieveInfoAl armes(<generated>)
at fr.gtietransport.openly.client.mci.MCI$2.demarrer( MCI.java:85)
2007-08-02 09:47:35,082 WARN org.hibernate.util.JDBCExceptionReporter: SQL Error: 0, SQLState: null
2007-08-02 09:47:35,082 ERROR org.hibernate.util.JDBCExceptionReporter: Connections could not be acquired from the underlying database!
2007-08-02 09:47:35,082 WARN org.hibernate.util.JDBCExceptionReporter: SQL Error: 0, SQLState: null
2007-08-02 09:47:35,082 ERROR org.hibernate.util.JDBCExceptionReporter: Connections could not be acquired from the underlying database!
at fr.gtietransport.lugdunumNG.client.mvc.MvcApplicat ion$1.run(MvcApplication.java:217)
at org.eclipse.swt.widgets.RunnableLock.run(RunnableL ock.java:35)
at org.eclipse.swt.widgets.Synchronizer.runAsyncMessa ges(Synchronizer.java:123)
... 4 more
Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.hibernate.exception.SQLStateConverter.handledN onSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelper.java:43)
at org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelper.java:29)
at org.hibernate.jdbc.ConnectionManager.openConnectio n(ConnectionManager.java:426)
at org.hibernate.jdbc.ConnectionManager.getConnection (ConnectionManager.java:144)
at org.hibernate.jdbc.JDBCContext.connection(JDBCCont ext.java:119)
at org.hibernate.transaction.JDBCTransaction.begin(JD BCTransaction.java:57)
at org.hibernate.impl.SessionImpl.beginTransaction(Se ssionImpl.java:1326)
at org.springframework.orm.hibernate3.HibernateTransa ctionManager.doBegin(HibernateTransactionManager.j ava:510)
... 14 more
Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtil s.java:104)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool. checkoutPooledConnection(C3P0PooledConnectionPool. java:264)
at com.mchange.v2.c3p0.PoolBackedDataSource.getConnec tion(PoolBackedDataSource.java:94)
at com.mchange.v2.c3p0.ComboPooledDataSource.getConne ction(ComboPooledDataSource.java:521)
at org.springframework.jdbc.datasource.DataSourceUtil s.doGetConnection(DataSourceUtils.java:113)
at org.springframework.jdbc.datasource.TransactionAwa reDataSourceProxy.getConnection(TransactionAwareDa taSourceProxy.java:109)
at org.springframework.orm.hibernate3.LocalDataSource ConnectionProvider.getConnection(LocalDataSourceCo nnectionProvider.java:81)
at org.hibernate.jdbc.ConnectionManager.openConnectio n(ConnectionManager.java:423)
... 19 more
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceE xception: A ResourcePool could not acquire a resource from its primary factory or source.
at com.mchange.v2.resourcepool.BasicResourcePool.awai tAcquire(BasicResourcePool.java:972)
at com.mchange.v2.resourcepool.BasicResourcePool.chec koutResource(BasicResourcePool.java:208)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool. checkoutPooledConnection(C3P0PooledConnectionPool. java:260)
... 25 more

My questions are the following :

a) How could I handle this exception ? even if one database is missing, the application should run (some modules will not be able to run, that's all).

b) When the failing database is up again, can I reconnect ?

I spend 4 hours and I really don't know where to go

Thanks for your help,

Laurent

[daliakamal]

hi
i was asking if u solve this problem and if yes, how u did?
i will be very thankfull for ur help, thanks