Could not open Hibernate Session for transaction on First Login

Question

I have a full operating Spring MVC Application running with Spring Security but I get the following error whenever the server hasn't been active in a while and someone tries to login:

HTTP Status 500 - Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin transaction failed

Sometimes after (approximately 5 seconds later) it starts to work normally.

I searched and found this link but I don't know how to configure connection testing in the connection pool configuration.

Any advice?

EDIT:

I found this link to set the configuration pool, so I tried to implement it in my dataSource:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">

        <property name="driverClassName" value="com.mysql.jdbc.Driver" />

        <property name="url" value="jdbc:mysql://192.168.254.45:3306/7jogos" />
        <property name="password" value="..." />
        <property name="username" value="..." />


<!--      Configuration pool -->
        <property name="validationQuery" value="SELECT 1" />
        <property name="validationInterval" value="34000" />
        <property name="testOnBorrow" value="true" />
        <property name="removeAbandoned" value="true" />
        <property name="removeAbandonedTimeout" value="55" />


    </bean>

But I end up getting an error on validationInterval saying:

Multiple annotations found at this line: - No setter found for property 'validationInterval' in class 'org.apache.commons.dbcp.BasicDataSource'

Solution 1

The answer is on my edit that was to add the following in my beans that have the connection data:

<!--      Configuration pool -->
        <property name="validationQuery" value="SELECT 1" />
        <property name="validationInterval" value="34000" />
        <property name="testOnBorrow" value="true" />
        <property name="removeAbandoned" value="true" />
        <property name="removeAbandonedTimeout" value="55" />

I don't know why but @Paweł Głowacz 's answer didn't work for me. Thanks for your help guys

Solution 2

Without a stacktrace it is hard to tell for sure, but you are most likely dealing with expired DB connections. Hibernate fails to start a transaction for you because the first statement it tries to run ("START TRANSACTION - via JDBC) fails. Again, I assume that because the underlying DB connection got invalidated in the meantime.

This can happen due to various reason:

• DB settings (most likely)
• network/firewall
• local settings, etc.

Regardless of the source though, you have the option to ensure that this is not happening. If you are using a JDBC pool (like Tomcat JDBC pool), you can configure the pool to test and reopen connections if necessary. The testOnBorrow setting for example would instruct the pool to constantly test the db connection before actually offering it to the application for use. You can fine-tune these settings to eliminate performance-related concerns.

ADDITIONAL INFO

You are trying to implement the pooling fine, but you got the variable name: validationInterval wrong.

You are getting that error because the BasicDataSource does not have a variable named like that, so Spring fails to set the property. Take a look at the javadoc of the BasicDataSource (link) to see what configuration variables are available for you. The validationInterval variable can be applied on tomcat pooling implementation (what I suggested above), the one you choose does not have this feature.

Solution 3

Replace

<property name="validationQuery" value="SELECT 1" />
<property name="validationInterval" value="34000" />
<property name="testOnBorrow" value="true" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="55" />

With this:

<property name="initialSize" value="10" />
<property name="maxActive" value="5" />
<property name="maxWait" value="5000" />