• FEATURES
  • PRICING
  • MARKETPLACE
  • CASE STUDIES
  • BLOG
    •

    JDBC begin transaction failed

    Technical Questions
    #1

    Hi,

    OpenSpecimen continues to work, but most mornings users receive an error message while logging on, stating “internal error, please contact your Administrator”. If the users click ‘login’ 2 or more times, they get past the error message.

    On looking in the localhost log file I see the below. Is there a setting I should change to stop this error?

    Jun 24, 2019 8:44:13 AM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet [restApi] in context with path [/openspecimen] threw exception com.krishagni.catissueplus.core.common.errors.OpenSpecimenException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin transaction failed: at com.krishagni.catissueplus.core.common.errors.OpenSpecimenException.serverError(OpenSpecimenException.java:136) at com.krishagni.catissueplus.core.common.TransactionalInterceptor.doWork(TransactionalInterceptor.java:91) at com.krishagni.catissueplus.core.common.TransactionalInterceptor.ajc$inlineAccessMethod$com_krishagni_catissueplus_core_common_TransactionalInterceptor$com_krishagni_catissueplus_core_common_TransactionalInterceptor$doWork(TransactionalInterceptor.java:1) at com.krishagni.catissueplus.core.common.TransactionalInterceptor.startTransaction(TransactionalInterceptor.java:64) at com.krishagni.catissueplus.core.auth.services.impl.UserAuthenticationServiceImpl.validateToken(UserAuthenticationServiceImpl.java:118) at com.krishagni.catissueplus.rest.filter.AuthTokenFilter.doFilter(AuthTokenFilter.java:118) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:506) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1115) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748)

    Many thanks for any advice.

    John

    #2

    Hi @John_M

    It appears the connection pool is incorrectly configured. Could you please share with us the database connection pool configuration? You might want to mask the database host, user, and password fields before sharing the configuration.

    Thanks,
    Vinayak

    #3

    Hi @vpawar

    Not 100% sure which setting you would like to see. But here are the database connection settings for Tomcat. They are the same as what is detailed in the openspecimen deployment instructions.

    Resource name=“jdbc/openspecimen” auth=“Container” type=“javax.sql.DataSource”
    maxActive=“100” maxIdle=“30” maxWait=“10000”
    username="******" password="******" driverClassName=“com.mysql.jdbc.Driver”
    url=“jdbc:mysql://localhost:3306/osdb”
    />

    Thanks,
    John

    #4

    Hi @John_M

    The connections in the pool are not validated before they are used by the app. As a result, users see those connection errors after prolonged idleness of OpenSpecimen (usually in the morning).

    To ensure the app is working with a valid connection, add the following validation parameters, testOnBorrow and validationQuery, to the pool configuration and restart OpenSpecimen.

    <Resource name=“jdbc/openspecimen” auth=“Container” 
  type=“javax.sql.DataSource”
  maxActive=“100” maxIdle=“30” maxWait=“10000”
  username="******" password="******" driverClassName=“com.mysql.jdbc.Driver”
  url=“jdbc:mysql://localhost:3306/osdb”
  testOnBorrow="true" validationQuery="select 1 from dual"/>

    This should help you get rid of the early morning connection errors.

    Thanks,
    Vinayak

    #5

    BTW, we’ve updated our deployment document to include the connection validation configuration. Thanks for the feedback.

    #6

    Thank you @vpawar.
    I haven’t received the error message since adding the extra statements.

    John