Spring Boot, SQL Server, AKS, The connection is closed

Question

We have a spring boot app running over AKS (Azure Kubernetes Service). In the service, we inject and call a Procedure invoker class:

package com.thing;

import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class ProcedureInvoker {

@PersistenceContext
private final EntityManager entityManager;

@Autowired
public ProcedureInvoker(final EntityManager entityManager) {
    this.entityManager = entityManager;
}


public int getValueCall(String user, String password) {


    StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("dbo.CHECK_USER");


    // In out parameters
    storedProcedureQuery.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
    storedProcedureQuery.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
    storedProcedureQuery.registerStoredProcedureParameter(3, Integer.class, ParameterMode.INOUT);


    //
    storedProcedureQuery.setParameter(1, user);
    storedProcedureQuery.setParameter(2, password);
    storedProcedureQuery.setParameter(3, new Integer(-3));

    Object result = storedProcedureQuery.getOutputParameterValue(3);

    storedProcedureQuery.execute();

    int resu = ((Integer)result).intValue();

    return resu; 
}

}

Sometimes (after 1 day o several hours working properly; about 3000 calls), we start getting this error:

com.microsoft.sqlserver.jdbc.SQLServerException:
   at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError 
(SQLServerException.java227)
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed 
(SQLServerConnection.java796)
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.setNetworkTimeout 
(SQLServerConnection.java4768)
   at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout (PoolBase.java541)
   at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection 
(PoolBase.java129)
   at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1 
(HikariPool.java434)
   at java.util.concurrent.ThreadPoolExecutor.runWorker 
(ThreadPoolExecutor.java1149)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run 
(ThreadPoolExecutor.java624)
   at java.lang.Thread.run (Thread.java748)

To solve it, we delete pods in order to restart them. But It is not a solution.

Pom has:

<!--  DDBB -->
    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <!-- End DDBB -->

Properties from yaml

spring.datasource.driverClassName: 
com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.show-sql: false
spring.jpa.hibernate.dialect: org.hibernate.dialect.SQLServer2012Dialect
spring.jpa.properties.hibernate.format_sql: true
logging.level.org.hibernate.type: debug

SOLVED!! Finally we solved it adding a:

entityManager.close()

We thought that sentence is not necessary for procedures or functions (it was closed by spring).

The problem probably comes from SQL Server because the procedure has a return statement which is not declared (We had some problems calling it from Spring). Maybe SQL server delegates the close of the connection to spring and spring made the opposite (delegating to SQL server)

Looks like connection pooling issue. Can you please check if you have defined connection pool properly and don't have any connection leaks? — Sheetal Mohan Sharma, Dec 03 '18 at 15:32
Hi, thanks, we only had the properties from yaml. We just also add some hikari properties: spring.datasource.hikari.maximumPoolSize: 7 spring.datasource.hikari.leakDetectionThreshold: 1500 spring.datasource.hikari.connectionTimeout: 30000 spring.datasource.hikari.maxLifetime: 60000 We are thinking about increase the number of pods and reduce pool size. For the moment error continues, less occurences maybe — mvelazquezm, Dec 04 '18 at 07:22
Are these properties loaded properly? Can you test this in unit tests? I would suggest running tests to see if there are any connection leaks and fix that first. A relevant question on how to configure hikari may be useful https://stackoverflow.com/questions/26490967/how-do-i-configure-hikaricp-in-my-spring-boot-app-in-my-application-properties-f and read this https://vladmihalcea.com/the-best-way-to-detect-database-connection-leaks/ for testing connection leaks — Sheetal Mohan Sharma, Dec 04 '18 at 10:31
https://dzone.com/articles/database-connection-pooling-in-java-with-hikaricp — Sheetal Mohan Sharma, Dec 04 '18 at 10:32
Thanks for the responses. We resolved it adding a entityManager.close() See my edit — mvelazquezm, Dec 05 '18 at 06:22

Spring Boot, SQL Server, AKS, The connection is closed

0 Answers0