title: How to configure Spring / Hibernate with MySQL Read-Replica
body: <p>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 <em>should</em> only reading data (the methods are definitely not using UPDATE, INSERT or DELETE).  The application, however, is returning errors.</p>
  
  <p><strong>My questions:</strong> Why is the application failing when only reading data? How can I configure Spring/Hibernate to work with a read-replica?</p>
  
  <p><strong>The Details:</strong></p>
  
  <p>The error I'm getting is:</p>
  
  <pre><code>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(&lt;generated&gt;)
      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)
  </code></pre>
  
  <p>The code that calls the DB:</p>
  
  <pre><code>public LecturerBO getLecturerByCode(Credentials credentials) {
      return findSingleResultByCriteria(LecturerBO.class,Restrictions.eq("loginCode",credentials.getPassword()));
  }
  </code></pre>
  
  <p>where</p>
  
  <pre><code>protected &lt;T&gt; T findSingleResultByCriteria(Class&lt;T&gt; clazz, Criterion... criterion) {
      Criteria crit = getSession().createCriteria(clazz);
      for (Criterion c : criterion) {
          crit.add(c);
      }
      return (T) crit.uniqueResult();
  }
  </code></pre>
  
  <p>And last, the Hibernate configuration:</p>
  
  <pre><code>&lt;bean id="generalManagerProxyTemplate"
        class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"
        abstract="true"&gt;
      &lt;property name="transactionManager" ref="hibernateTransactionManager"/&gt;
      &lt;property name="proxyTargetClass" value="true"/&gt;
      &lt;property name="transactionAttributes"&gt;
          &lt;props&gt;
              &lt;prop key="*"&gt;PROPAGATION_REQUIRED&lt;/prop&gt;
          &lt;/props&gt;
      &lt;/property&gt;
      &lt;property name="preInterceptors"&gt;
          &lt;list&gt;
              &lt;bean class="com.iengage.exceptions.ExceptionWrapper"/&gt;
              &lt;bean class="com.iengage.utils.PerformanceLogger"/&gt;
          &lt;/list&gt;
      &lt;/property&gt;
  &lt;/bean&gt;
  
  &lt;bean id="hibernateTransactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"&gt;
      &lt;property name="sessionFactory" ref="sessionFactory"/&gt;
  &lt;/bean&gt;
  
  &lt;bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"&gt;
      &lt;property name="dataSource" ref="pooledDataSource"/&gt;
      &lt;property name="hibernateProperties"&gt;
          &lt;props&gt;
              &lt;prop key="hibernate.dialect"&gt;org.hibernate.dialect.MySQL5InnoDBDialect&lt;/prop&gt;
              &lt;prop key="transaction.auto_close_session"&gt;true&lt;/prop&gt;
              &lt;prop key="transaction.flush_before_completion"&gt;true&lt;/prop&gt;
              &lt;prop key="show_sql"&gt;false&lt;/prop&gt;
              &lt;prop key="hibernate.cache.use_query_cache"&gt;false&lt;/prop&gt;
              &lt;prop key="hibernate.show_sql"&gt;false&lt;/prop&gt;
          &lt;/props&gt;
      &lt;/property&gt;
      &lt;property name="annotatedClasses"&gt;
          &lt;bean class="com.iengage.dao.EntityList"/&gt;
      &lt;/property&gt;
  &lt;/bean&gt;
  
  &lt;bean id="pooledDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"&gt;
      &lt;property name="driverClassName" value="${db.driverClassName}"/&gt;
      &lt;property name="url" value="${db.url}"/&gt;
      &lt;property name="username" value="${db.username}"/&gt;
      &lt;property name="password" value="${db.password}"/&gt;
      &lt;property name="defaultAutoCommit" value="false"/&gt;
      &lt;property name="testWhileIdle" value="true"/&gt;
  &lt;/bean&gt;
  
  &lt;bean id="abstractDAO" class="com.iengage.dao.GeneralDAO" abstract="true"&gt;
      &lt;property name="sessionFactory" ref="sessionFactory"/&gt;
      &lt;property name="dataSource" ref="pooledDataSource"/&gt;
  &lt;/bean&gt;
  
  &lt;bean id="dao" class="com.iengage.dao.IEngageDAO" parent="abstractDAO"/&gt;
  </code></pre>
  
  <p>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.</p>
  
  <p>Also, in my research before posting this question, most answers focused on the <code>@Transactional</code> annotation, which we don't use. Sad panda.</p>
  
  <p>Thanks for any help.</p>