Intermittent test failures due to running out of DB connections

[distinctdan]

Expected Behavior

As I've added more tests, I'm starting to see intermittent failures because it looks like DB connections aren't getting closed properly sometimes. This is exacerbated by the fact that every MicronautTest recreates a connection pool. When I run tests locally, it sits there for 30 seconds then fails with a Hikari connection pool timout. The log shows that all connections are active, which doesn't seem right because it's not doing anything.

AssessmentControllerV2Test > initializationError FAILED
    io.micronaut.data.connection.jdbc.exceptions.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection
        at app//io.micronaut.data.connection.jdbc.operations.DefaultDataSourceConnectionOperations.openConnection(DefaultDataSourceConnectionOperations.java:59)
        at app//io.micronaut.data.connection.jdbc.operations.DefaultDataSourceConnectionOperations.openConnection(DefaultDataSourceConnectionOperations.java:43)
        at app//io.micronaut.data.connection.support.AbstractConnectionOperations.openNewConnectionInternal(AbstractConnectionOperations.java:195)
        at app//io.micronaut.data.connection.support.AbstractConnectionOperations.getConnection(AbstractConnectionOperations.java:168)
        at app//io.micronaut.transaction.support.AbstractTransactionOperations.openNewConnectionAndTransaction(AbstractTransactionOperations.java:541)
        at app//io.micronaut.transaction.support.AbstractTransactionOperations.getTransaction(AbstractTransactionOperations.java:477)
        at app//io.micronaut.transaction.async.AsyncUsingSyncTransactionOperations.withTransaction(AsyncUsingSyncTransactionOperations.java:51)
        at app//io.micronaut.transaction.interceptor.TransactionalInterceptor.intercept(TransactionalInterceptor.java:135)
        at app//io.micronaut.aop.chain.MethodInterceptorChain.proceed(MethodInterceptorChain.java:143)
        at app//com.cfa.ots.fsd.assessment_api.core.assessment.$PostgresAssessmentDao$Definition$Intercepted.putAssessment(Unknown Source)
        at app//com.cfa.ots.fsd.assessment_api.core.assessment.AssessmentControllerV2Test$Companion$beforeAll$1$1$1.invokeSuspend(AssessmentControllerV2Test.kt:90)
        at app//kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
        at app//kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:100)
        at app//kotlinx.coroutines.EventLoopImplBase.processNextEvent(EventLoop.common.kt:263)
        at app//kotlinx.coroutines.BlockingCoroutine.joinBlocking(Builders.kt:95)
        at app//kotlinx.coroutines.BuildersKt__BuildersKt.runBlocking(Builders.kt:69)
        at app//kotlinx.coroutines.BuildersKt.runBlocking(Unknown Source)
        at app//kotlinx.coroutines.BuildersKt__BuildersKt.runBlocking$default(Builders.kt:47)
        at app//kotlinx.coroutines.BuildersKt.runBlocking$default(Unknown Source)
        at app//com.cfa.ots.fsd.assessment_api.core.assessment.AssessmentControllerV2Test$Companion.beforeAll(AssessmentControllerV2Test.kt:69)
        at app//com.cfa.ots.fsd.assessment_api.core.assessment.AssessmentControllerV2Test.beforeAll(AssessmentControllerV2Test.kt)
        Caused by:
        java.sql.SQLTransientConnectionException: HikariPool-11 - Connection is not available, request timed out after 30006ms (total=4, active=4, idle=0, waiting=0)
            at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:706)
            at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:187)
            at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:145)
            at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:99)
            at io.micronaut.data.connection.jdbc.operations.DefaultDataSourceConnectionOperations.openConnection(DefaultDataSourceConnectionOperations.java:57)
            ... 20 more

Here's an error log from running in github actions. If you scroll down, you can see that the error is org.postgresql.util.PSQLException: FATAL: sorry, too many clients already

SecuredControllerTest > initializationError FAILED
    io.micronaut.context.exceptions.BeanInstantiationException: Bean definition [javax.sql.DataSource] could not be loaded: Error instantiating bean of type  [javax.sql.DataSource]

    Message: Failed to initialize pool: Could not create new connection
    Path Taken: DatasourceFactory.dataSource(DatasourceConfiguration datasourceConfiguration)
        at app//io.micronaut.context.DefaultBeanContext.initializeContext(DefaultBeanContext.java:2000)
        at app//io.micronaut.context.DefaultApplicationContext.initializeContext(DefaultApplicationContext.java:314)
        at app//io.micronaut.context.DefaultBeanContext.configureAndStartContext(DefaultBeanContext.java:3318)
        at app//io.micronaut.context.DefaultBeanContext.start(DefaultBeanContext.java:345)
        at app//io.micronaut.context.DefaultApplicationContext.start(DefaultApplicationContext.java:216)
        at app//io.micronaut.context.ApplicationContextBuilder.start(ApplicationContextBuilder.java:286)
        at app//io.micronaut.context.ApplicationContext.run(ApplicationContext.java:233)
        at app//io.micronaut.context.ApplicationContext.run(ApplicationContext.java:210)
        at app//com.cfa.ots.fsd.assessment_api.core.security.SecuredControllerTest$Companion.beforeAll(SecuredControllerTest.kt:78)
        at app//com.cfa.ots.fsd.assessment_api.core.security.SecuredControllerTest.beforeAll(SecuredControllerTest.kt)

        Caused by:
        io.micronaut.context.exceptions.BeanInstantiationException: Error instantiating bean of type  [javax.sql.DataSource]

        Message: Failed to initialize pool: Could not create new connection
        Path Taken: DatasourceFactory.dataSource(DatasourceConfiguration datasourceConfiguration)
            at app//io.micronaut.context.DefaultBeanContext.resolveByBeanFactory(DefaultBeanContext.java:2349)
            at app//io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2304)
            at app//io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2316)
            at app//io.micronaut.context.DefaultBeanContext.createRegistration(DefaultBeanContext.java:3127)
            at app//io.micronaut.context.SingletonScope.getOrCreate(SingletonScope.java:80)
            at app//io.micronaut.context.DefaultBeanContext.findOrCreateSingletonBeanRegistration(DefaultBeanContext.java:3029)
            at app//io.micronaut.context.DefaultBeanContext.initializeEagerBean(DefaultBeanContext.java:2693)
            at app//io.micronaut.context.DefaultBeanContext.initializeContext(DefaultBeanContext.java:1994)
            ... 9 more

            Caused by:
            com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Could not create new connection
                at app//com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:602)
                at app//com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:589)
                at app//com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:99)
                at app//com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:80)
                at app//io.micronaut.configuration.jdbc.hikari.HikariUrlDataSource.<init>(HikariUrlDataSource.java:35)
                at app//io.micronaut.configuration.jdbc.hikari.DatasourceFactory.dataSource(DatasourceFactory.java:69)
                at app//io.micronaut.configuration.jdbc.hikari.$DatasourceFactory$DataSource0$Definition.instantiate(Unknown Source)
                at app//io.micronaut.context.BeanDefinitionDelegate.instantiate(BeanDefinitionDelegate.java:170)
                at app//io.micronaut.context.DefaultBeanContext.resolveByBeanFactory(DefaultBeanContext.java:2334)
                ... 16 more

                Caused by:
                java.sql.SQLException: Could not create new connection
                    at org.testcontainers.containers.JdbcDatabaseContainer.createConnection(JdbcDatabaseContainer.java:296)
                    at org.testcontainers.jdbc.ContainerDatabaseDriver.connect(ContainerDatabaseDriver.java:140)
                    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:139)
                    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:362)
                    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:203)
                    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:479)
                    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:568)
                    ... 23 more

                    Caused by:
                    org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
                        at app//org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:711)
                        at app//org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:213)
                        at app//org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:268)
                        at app//org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54)
                        at app//org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:273)
                        at app//org.postgresql.Driver.makeConnection(Driver.java:446)
                        at app//org.postgresql.Driver.connect(Driver.java:298)
                        at app//org.testcontainers.containers.JdbcDatabaseContainer.createConnection(JdbcDatabaseContainer.java:2[87](https://github.com/cfacorp/food-safety-report-api/actions/runs/13979059721/job/39139966190?pr=2666#step:7:88))
                        ... 29 more

Actual Behaviour

No response

Steps To Reproduce

No response

Environment Information

Here are some excerts from my build.gradle:

plugins {
    id "io.micronaut.minimal.application" version "4.7.4"
    id "io.micronaut.aot" version "4.7.4"
    id "io.micronaut.test-resources" version "4.7.4"
    id "org.flywaydb.flyway" version "9.22.1"
}
micronaut {
    version "$micronautVersion" // defined in gradle.properties
    runtime "netty"
    testRuntime "junit5"
    processing {
        incremental true
        annotations "com.cfa.ots.fsd.*"
    }
    aot {
        // Please review carefully the optimizations enabled below
        // Check https://micronaut-projects.github.io/micronaut-aot/latest/guide/ for more details
        optimizeServiceLoading = false
        convertYamlToJava = false
        precomputeOperations = true
        cacheEnvironment = true
        optimizeClassLoading = true
        deduceEnvironment = true
        optimizeNetty = true
        replaceLogbackXml = false
    }
}
test {
    useJUnitPlatform()
    testLogging {
        exceptionFormat = 'full'
    }
    finalizedBy jacocoTestReport // always generate test coverage reports after running the test task
}
dependencies {
    // WARNING: DON'T use micronaut-data's DB class generation right now. It appears that it's not correctly implementing
    // coroutines, and it's failing to free DB connections in suspend functions. We'll keep using JDBI until they fix it.
//    annotationProcessor mn.micronaut.data.processor

    // Necessary for transaction management to work correctly.
    implementation mn.micronaut.data.jdbc

    // JDBI database access for manual query building.
    implementation mn.micronaut.jdbi
    implementation("org.jdbi:jdbi3-postgres")
    implementation("org.jdbi:jdbi3-kotlin")
    implementation("org.jdbi:jdbi3-sqlobject")

    // DB connection pool
    runtimeOnly(mn.micronaut.jdbc.hikari)

    // DB migrations. We're using a mix of micronaut's flyway plugin, which should integrate with micronaut test containers,
    // and flyway's gradle plugin, so that we can configure it to migrate before the "run" task when running locally.
    implementation mn.micronaut.flyway
    runtimeOnly "org.flywaydb:flyway-database-postgresql"

    // Coroutines
    implementation "org.jetbrains.kotlinx:kotlinx-coroutines-core:1.10.1"
    testImplementation "org.jetbrains.kotlinx:kotlinx-coroutines-test:1.10.1"

    // Postgres
    runtimeOnly("org.postgresql:postgresql:42.7.4")

    // JUnit
    testAnnotationProcessor "io.micronaut:micronaut-inject-java"
    testImplementation("org.junit.jupiter:junit-jupiter-api")
    testImplementation("io.micronaut.test:micronaut-test-junit5")
    testRuntimeOnly("org.junit.jupiter:junit-jupiter-engine")
    testImplementation("org.junit.jupiter:junit-jupiter-engine")
    testImplementation "org.junit.jupiter:junit-jupiter-params"

    // Test containers to run a DB for tests to use.
    testImplementation "org.testcontainers:postgresql"
    testImplementation "org.testcontainers:testcontainers"
}

Example Application

No response

Version

4.7.4

[dstepanov]

Please provide a project that is reproducing the problem

[distinctdan]

Unfortunately I can't upload my project, and I don't have time right now to create a separate project to track this down. It would probably take a large number of tests or queries to reproduce it since it's only happening with a lot of tests running.

[mikehearn]

I'm hitting the same problem. The request to allow test classes to share contexts is #1199

I'm not sure if Hikari is leaking connections but I've seen no evidence of that. Rather, we're using parallel test execution so as the test suite grows so does the demand on DB processes. If I monitor the number of running processes it does fall back down to where it started when the test suite ends.

I think the only way to make progress here is to find a way to share contexts between tests. There should be a way to have one MN context and one connection pool per module test run.