Announcement Announcement Module
Collapse
No announcement yet.
Handling multiple datasource in a smart way Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Handling multiple datasource in a smart way

    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 :
    Code:
    <!-- 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

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

    Comment

    Working...
    X