How to configure Spring / Hibernate with MySQL Read-Replica

Question

I have a Java application that uses Spring/Hibernate (running on Amazon Elastic Beanstalk) with a normal Amazon RDS instance. To distribute load, I'm adding a read-replica. I swapped the DB.url to the read replica and tested a method that should only reading data (the methods are definitely not using UPDATE, INSERT or DELETE). The application, however, is returning errors.

My questions: Why is the application failing when only reading data? How can I configure Spring/Hibernate to work with a read-replica?

The Details:

The error I'm getting is:

INFO: Internal error in method IEngageManager.getSimpleExamActivityLiveResults, ARGUMENTS=[
  Credentials {
(m) password=0c2a765c057a31c51b68d0f6c75cef93, 
(m) email=w1jg
  }
]
com.iengage.exceptions.IEException: Internal error in method IEngageManager.getSimpleExamActivityLiveResults, ARGUMENTS=[
  Credentials {
    (m) password=0c2a765c057a31c51b68d0f6c75cef93, 
    (m) email=w1jg
  }
]
    at com.iengage.exceptions.ExceptionWrapper.overridingInvoke(ExceptionWrapper.java:25)
    at com.iengage.springobjects.SingletonMethodInterceptor.invoke(SingletonMethodInterceptor.java:18)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
    at com.iengage.managers.IEngageManager$$EnhancerByCGLIB$$c8a2c385.getSimpleExamActivityLiveResults(<generated>)
    at com.iengage.services.IEngageService.getSimpleExamActivityLiveResults(IEngageService.java:379)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:616)
    at com.sun.xml.ws.api.server.InstanceResolver$1.invoke(InstanceResolver.java:246)
    at com.sun.xml.ws.server.InvokerTube$2.invoke(InvokerTube.java:146)
    at com.sun.xml.ws.server.sei.EndpointMethodHandler.invoke(EndpointMethodHandler.java:257)
    at com.sun.xml.ws.server.sei.SEIInvokerTube.processRequest(SEIInvokerTube.java:93)
    at com.sun.xml.ws.api.pipe.Fiber.__doRun(Fiber.java:595)
    at com.sun.xml.ws.api.pipe.Fiber._doRun(Fiber.java:554)
    at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:539)
    at com.sun.xml.ws.api.pipe.Fiber.runSync(Fiber.java:436)
    at com.sun.xml.ws.server.WSEndpointImpl$2.process(WSEndpointImpl.java:243)
    at com.sun.xml.ws.transport.http.HttpAdapter$HttpToolkit.handle(HttpAdapter.java:444)
    at com.sun.xml.ws.transport.http.HttpAdapter.handle(HttpAdapter.java:244)
    at com.sun.xml.ws.transport.http.servlet.ServletAdapter.handle(ServletAdapter.java:135)
    at com.sun.xml.ws.transport.http.servlet.WSServletDelegate.doGet(WSServletDelegate.java:129)
    at com.sun.xml.ws.transport.http.servlet.WSServletDelegate.doPost(WSServletDelegate.java:160)
    at com.sun.xml.ws.transport.http.servlet.WSSpringServlet.doPost(WSSpringServlet.java:52)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
    at org.apache.catalina.valves.RemoteIpValve.invoke(RemoteIpValve.java:680)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:999)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:565)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:307)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
    at java.lang.Thread.run(Thread.java:679)

The code that calls the DB:

public LecturerBO getLecturerByCode(Credentials credentials) {
    return findSingleResultByCriteria(LecturerBO.class,Restrictions.eq("loginCode",credentials.getPassword()));
}

where

protected <T> T findSingleResultByCriteria(Class<T> clazz, Criterion... criterion) {
    Criteria crit = getSession().createCriteria(clazz);
    for (Criterion c : criterion) {
        crit.add(c);
    }
    return (T) crit.uniqueResult();
}

And last, the Hibernate configuration:

<bean id="generalManagerProxyTemplate"
      class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"
      abstract="true">
    <property name="transactionManager" ref="hibernateTransactionManager"/>
    <property name="proxyTargetClass" value="true"/>
    <property name="transactionAttributes">
        <props>
            <prop key="*">PROPAGATION_REQUIRED</prop>
        </props>
    </property>
    <property name="preInterceptors">
        <list>
            <bean class="com.iengage.exceptions.ExceptionWrapper"/>
            <bean class="com.iengage.utils.PerformanceLogger"/>
        </list>
    </property>
</bean>

<bean id="hibernateTransactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory"/>
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource" ref="pooledDataSource"/>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</prop>
            <prop key="transaction.auto_close_session">true</prop>
            <prop key="transaction.flush_before_completion">true</prop>
            <prop key="show_sql">false</prop>
            <prop key="hibernate.cache.use_query_cache">false</prop>
            <prop key="hibernate.show_sql">false</prop>
        </props>
    </property>
    <property name="annotatedClasses">
        <bean class="com.iengage.dao.EntityList"/>
    </property>
</bean>

<bean id="pooledDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${db.driverClassName}"/>
    <property name="url" value="${db.url}"/>
    <property name="username" value="${db.username}"/>
    <property name="password" value="${db.password}"/>
    <property name="defaultAutoCommit" value="false"/>
    <property name="testWhileIdle" value="true"/>
</bean>

<bean id="abstractDAO" class="com.iengage.dao.GeneralDAO" abstract="true">
    <property name="sessionFactory" ref="sessionFactory"/>
    <property name="dataSource" ref="pooledDataSource"/>
</bean>

<bean id="dao" class="com.iengage.dao.IEngageDAO" parent="abstractDAO"/>

And last, as a sanity check, I did connect with the read-replica directly and via a simple script that executed SELECT queries that I hosted on a third-party server. All worked as expected.

Also, in my research before posting this question, most answers focused on the @Transactional annotation, which we don't use. Sad panda.

Thanks for any help.

Answer 1

To provide that type of access (Only Read-Replica) you have to implement this property.

        <property name="transactionAttributes">
            <props>
                <prop key="get*">PROPAGATION_REQUIRED,readOnly
                </prop>
                <prop key="find*">PROPAGATION_REQUIRED,readOnly
                </prop>
                <prop key="load*">PROPAGATION_REQUIRED,readOnly
                </prop>
                <prop key="store*">PROPAGATION_REQUIRED</prop>
                <prop key="add*">PROPAGATION_REQUIRED</prop>
            </props>
        </property>

here, i use this two to allow store and add into DB. you have to remove this if you not require.

       <prop key="store*">PROPAGATION_REQUIRED</prop>
       <prop key="add*">PROPAGATION_REQUIRED</prop>