com.microsoft.sqlserver.jdbc.sqlserverexception: the stream is closed

Question

I am using mssql-jdbc-12.2.0.jre8.jar and HikariCP-4.0.3.jar to connect to Microsof SQL Server Database

SQL Server version is: Microsoft SQL Server 2012 - 11.0.5548.0 (X64) Sep 6 2014 17:19:28 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Hibernate version: hibernate-core-5.6.11.Final

JRE version: jre1.8.0_281

Apache Tomcat version: 9.0.71

Springboot version: 2.7.4

Spring DataSource configuration:

spring.datasource.url=jdbc:sqlserver://mydatabaseServer:51803;databaseName=mydatabaseName;encrypt=false;trustServerCertificate=true;socketTimeout=60000
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.password = mypassword
spring.datasource.testWhileIdle = true
spring.datasource.test-on-borrow=true
spring.datasource.validationQuery = SELECT 1
spring.datasource.sql-script-encoding=UTF-8
spring.datasource.username = myusername
spring.datasource.hikari.pool-name=myPool
spring.datasource.hikari.maximum-pool-size=300
spring.datasource.hikari.connectionTimeout=80000 
spring.datasource.hikari.max-lifetime=300000
spring.datasource.hikari.idle-timeout=60000
spring.datasource.hikari.leakDetectionThreshold=300000
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.SQLServer2012Dialect
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true
spring.jpa.open-in-view=false
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy

The query causing the issue is returning maximum 10 rows as follows:

@Repository
@Transactional(readOnly = true)
@Log4j2
public class SearchService {

    @PersistenceContext
    private EntityManager entityManager;
    
    public List<Request> searchRequests(SearchModel searchModel, int pageNumber) {
    

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Request> criteriaQuery = criteriaBuilder.createQuery(Request.class);
        Root<Request> requestRoot = criteriaQuery.from(Request.class);
        requestRoot.alias("r");
        
        Predicate predicate = createConditions(requestRoot, criteriaBuilder, searchModel);
        criteriaQuery.select(requestRoot);
        Order orderBy = criteriaBuilder.desc(requestRoot.get("id"));
        String orderByProperty = searchModel.getOrderBy();
        criteriaQuery.where(predicate).orderBy(orderBy);

        // pageSize is 10
        // searchModel.getMaxResult() = 10
        // for pageNumber 1 startFrom will be 0 and maxResult will be 10
        int startFrom = (pageNumber - 1) * searchModel.getPageSize();
        List<Request> result = entityManager.createQuery(criteriaQuery).setMaxResults(searchModel.getMaxResult())
                .setFirstResult(startFrom).getResultList();
        return result;
    
    }
    

}

The above setup works fine from long time, but recently sometimes I get lots of the following exception: com.microsoft.sqlserver.jdbc.sqlserverexception: the stream is closed on the line :

entityManager.createQuery(criteriaQuery).setMaxResults(searchModel.getMaxResult())
                    .setFirstResult(startFrom).getResultList();

This is translated to:

SELECT * FROM request ORDER BY id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

when excuting this query direct from SQL Server it takes 10ms and when the stream is closed issue happens, in the monitoring tools I can see that it's closed in this query multiple times !

Note : the Request entity/table which I am selecting from has no Eager relationships and has no Binary columns but it has two varchar(MAX) columns and the maximum length reached was 5368

what might be causing this issue ? can it be application related or database/windows server issue ?

Answer 1

The "stream is closed" exception is... quite vague.

Assuming there is no network issue, and the SQL Server logs not show any other more detailed error message, or the server itself has no CPU/memory/disk space issue, you might consider:

• Review your HikariCP connection pool settings. You might want to adjust the max-lifetime, idle-timeout, and leakDetectionThreshold settings to ensure that connections are not being closed prematurely or held for too long.

• Optimize your Query: Although the query itself seems to be performing well when executed directly on the SQL Server, you can still try to optimize the query further by adding appropriate indexes to the Request table or by using pagination with a more efficient approach, such as using a keyset-based pagination.

Try also to catch exception with more details, as in, for instance (from "How to catch Hibernate SQL EXCEPTIONS IN SPRING"):

import javax.persistence.PersistenceException;
import org.hibernate.exception.JDBCConnectionException;
import org.springframework.dao.DataAccessException;

@Repository
@Transactional(readOnly = true)
@Log4j2
public class SearchService {

    @PersistenceContext
    private EntityManager entityManager;

    public List<Request> searchRequests(SearchModel searchModel, int pageNumber) {
        List<Request> result = new ArrayList<>();

        try {
            CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
            CriteriaQuery<Request> criteriaQuery = criteriaBuilder.createQuery(Request.class);
            Root<Request> requestRoot = criteriaQuery.from(Request.class);
            requestRoot.alias("r");

            Predicate predicate = createConditions(requestRoot, criteriaBuilder, searchModel);
            criteriaQuery.select(requestRoot);
            Order orderBy = criteriaBuilder.desc(requestRoot.get("id"));
            String orderByProperty = searchModel.getOrderBy();
            criteriaQuery.where(predicate).orderBy(orderBy);

            int startFrom = (pageNumber - 1) * searchModel.getPageSize();
            result = entityManager.createQuery(criteriaQuery).setMaxResults(searchModel.getMaxResult())
                    .setFirstResult(startFrom).getResultList();
        } catch (PersistenceException | DataAccessException e) {
            if (e.getCause() instanceof JDBCConnectionException) {
                log.error("JDBC connection exception occurred: {}", e.getMessage(), e);
            } else {
                log.error("An exception occurred while executing the search query: {}", e.getMessage(), e);
            }
        } catch (Exception e) {
            log.error("An unexpected exception occurred while executing the search query: {}", e.getMessage(), e);
        }

        return result;
    }
}

I have wrapped the query execution in a try-catch block to catch any PersistenceException, DataAccessException, or other general exceptions. If a JDBCConnectionException occurs, it will be logged with a specific message. For other exceptions, a more generic error message will be logged.
This should help you identify and diagnose any issues that might occur during the execution of the query.