Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Spring Boot Blocked Connection Pool with enabled SSL

I have a Spring Boot Application (Version 2.5.3) with enabled SSL using a self signed certificate. One endpoint is used to download a file in the client using a StreamingResponseBody.

Problem

The Problem is, when a user requests a file via this endpoint, the connection pool doesn't get cleaned up. Working example showcasing the problem here: https://github.com/smotastic/blocked-connection-pool

@GetMapping("/ping")
public ResponseEntity<StreamingResponseBody> ping() {
    // service.findSomeFile(...)
    StreamingResponseBody body = new StreamingResponseBody() {
        
        @Override
        public void writeTo(OutputStream outputStream) throws IOException {
            // outputStream.write(someFile);
        }
    };
    return ResponseEntity.ok(body);
}

After calling this, and turning on the debug logging for the Hikari Connection Pool, the following will be logged.

Pool stats (total=10, active=1, idle=9, waiting=0)

As you can see, one connection is active and will never be released again. If I call this endpoint 9 more times, the connection pool is full and any subsequent requests will run into a timeout.

What i found out so far

The combination of having a ssl enabled application, the call to a service or a repository and the return value of a StreamingResponseBody seems to be my problem here. As soon as i turn one of these factors off, the problem disappears and the connection gets released as expected.

// SecurityConfig.java
@Override
protected void configure(HttpSecurity http) throws Exception {
    http.authorizeRequests().anyRequest().permitAll();
    http.requiresChannel().anyRequest().requiresSecure(); // turning this off 'solves' the problem
}

In the example i provided a simple FooEntity and a FooRepository, where i just call the count method. I guess that this opens a new transaction and provokes the error here.

// Controller.java
@Autowired
FooRepository fooRepo;

@GetMapping("/ping")
public ResponseEntity<StreamingResponseBody> ping() {
    fooRepo.count(); // removing this line 'solves' the error
    StreamingResponseBody body = new StreamingResponseBody() {
            
        @Override
        public void writeTo(OutputStream outputStream) throws IOException {
          // outStream.write(...)
        }
    };
    return ResponseEntity.ok(body);
}

The FooRepository is in this case just a simple CrudRepository from Spring, with no extra functionality.

// FooRepository.java
@Repository
public interface FooRepository extends CrudRepository<Foo, Long>{

}

The FooEntity is a simple entity with an id.

// Foo.java
@Entity
public class Foo {

    @GeneratedValue
    @Id
    private Long id;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }
    
}

In this example i'm using a h2 datasource, but i also tried it with postgres. Both are not working.

# application.properties
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create

spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:db;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=sa

The ssl is enabled via the following configuration.

# https via self signed certificate
server.ssl.key-store-type=${SSL_KEY_STORE_TYPE:PKCS12}
# The path to the keystore containing the certificate
server.ssl.key-store=${SSL_KEY_STORE:classpath:identity.p12}
# The password used to generate the certificate
server.ssl.key-store-password=${SSL_KEY_STORE_PASSWORD:hello}
# The alias mapped to the certificate
server.ssl.key-alias=${SSL_KEY_ALIAS:mykey}
server.ssl.enabled=${SSL_ENABLED:true}

The certificate is a self signed certificate, created on my Windows 10 machine.

Also, the Hikari Connection Pool doesn't seem to be the problem. I also tried the Tomcat JDBC Connection Pool. The Pool still gets blocked.

# application.properties
spring.datasource.type=org.apache.tomcat.jdbc.pool.DataSource
like image 655
smotastic Avatar asked Nov 07 '22 00:11

smotastic


1 Answers

try adding

spring.jpa.open-in-view=false

in properties.

Introducing Open Session in View

Running out of JDBC connections when loading entity in StreamingResponseBody

like image 154
Marc Stroebel Avatar answered Nov 12 '22 18:11

Marc Stroebel