Is there any reason why I'm suddenly getting `Can'...

# komapper

[dave08]

Is there any reason why I'm suddenly getting Can't create more than max_prepared_stmt_count statements... after migrating to Komapper from Jasync? (It could be that it's not Komapper... I'm just double checking, or maybe someone might know another reason for it?)

[Toshihiro Nakamura]

Please allow me to ask two questions:
1. Are you using a connection pool?
2. Are you using transaction management feature of Komapper?

[dave08]

I'm using the r2dbc connection pool. and if by transaction management you mean withTransaction, so far, I'm not using it yet (though I was thinking to use it soon...)

[dave08]

io.r2dbc:r2dbc-pool

[dave08]

data class ConnectionPoolConfig (
    val name: String? = null,
    val aquireRetry: Int = 1,
    val backgroundEvictionInterval: Duration = ConnectionPoolConfiguration.NO_TIMEOUT,
    val initialSize: Int? = null,
    val maxSize: Int? = null,
    val minIdle: Int = 0,
    val maxAquireTime: Duration = ConnectionPoolConfiguration.NO_TIMEOUT,
    val maxCreateConnectionTime: Duration = ConnectionPoolConfiguration.NO_TIMEOUT,
    val maxIdleTime: Duration = Duration.ofMinutes(30),
    val maxLifeTime: Duration = ConnectionPoolConfiguration.NO_TIMEOUT,
    val maxValidationTime: Duration = ConnectionPoolConfiguration.NO_TIMEOUT,
    val validationQuery: String? = null,
    val validationDepth: ValidationDepth = ValidationDepth.LOCAL,
)

interface DatabaseModule {
    @Provides @Singleton
    fun provideDatabase(configs: DatabaseSettings2): R2dbcDatabase {
        val db = configs
        val cp = configs.poolSettings

        val poolConfig = ConnectionPoolConfiguration.builder().apply {
            cp.name?.let { name(it) }
            acquireRetry(cp.aquireRetry)
            backgroundEvictionInterval(cp.backgroundEvictionInterval)
            cp.initialSize?.let { initialSize(it) }
            cp.maxSize?.let { maxSize(it) }
            minIdle(cp.minIdle)
            maxAcquireTime(cp.maxAquireTime)
            maxCreateConnectionTime(cp.maxCreateConnectionTime)
            maxIdleTime(cp.maxIdleTime)
            maxLifeTime(cp.maxLifeTime)
            maxValidationTime(cp.maxValidationTime)
            cp.validationQuery?.let { validationQuery(it) }
            validationDepth(cp.validationDepth)
        }

        val pool = ConnectionPool(
            poolConfig.connectionFactory(
                ConnectionFactories.get(
                    ConnectionFactoryOptions.builder()
                        .option(ConnectionFactoryOptions.DATABASE, db.dbName)
                        .option(ConnectionFactoryOptions.DRIVER, "mysql")
                        .option(ConnectionFactoryOptions.HOST, db.host)
                        .option(ConnectionFactoryOptions.PORT, db.port)
                        .option(ConnectionFactoryOptions.USER, db.username)
                        .option(ConnectionFactoryOptions.PASSWORD, db.password)
                        .build()
                )
            )
                .build()
        )

all with default values so far...

[dave08]

The truth is, maybe they should be adjusted, but I guess I'm used to Micronaut hopefully giving me good defaults... now I started using Komapper straight away with Ktor and ended up with the above as my starting point. Although this api is still on our dev environment, and it's already giving me errors with only about 10-20 users max...

[Toshihiro Nakamura]

I believe the issue linked is related to this problem.
https://github.com/jasync-sql/jasync-sql/issues/312

[dave08]

So how can I use that change? It seems to be out... maybe a question of upgrading a dependency?

[dave08]

I use this komapper-dialect-mysql-r2dbc = { module = "org.komapper:komapper-dialect-mysql-r2dbc" } which bring in:

[dave08]

And @oshai stated it was fixed in 2.1.9. So maybe it's a setting on the driver or something?

[dave08]

I'm about to put this out to production, so it's really important to find some resolution to this problem... thanks!

[dave08]

It seems like there's no option to enable it just like that, but there's this:
https://github.com/jasync-sql/jasync-sql/pull/330#discussion_r1046779572, but I don't understand where I'd put such code... wouldn't this have to be in Komapper to work?

[Toshihiro Nakamura]

You can wrap your connection and invoke the

jst.releasePreparedStatementAfterUse

function as follows:

val db = R2dbcDatabase("r2dbc:h2:mem:///example;DB_CLOSE_DELAY=-1")

val newConfig = object : R2dbcDatabaseConfig by db.config {
    val delegatee: R2dbcSession = db.config.session
    override val session: R2dbcSession = object : R2dbcSession {
        override val connectionFactory: ConnectionFactory
            get() = delegatee.connectionFactory
        override val coroutineTransactionOperator: CoroutineTransactionOperator
            get() = delegatee.coroutineTransactionOperator
        override val flowTransactionOperator: FlowTransactionOperator
            get() = delegatee.flowTransactionOperator

        override suspend fun getConnection(): Connection {
            val connection = delegatee.getConnection()
            return object : Connection by connection {
                override fun createStatement(sql: String): Statement {
                    return connection.createStatement(sql).also {
                        //if (it is JasyncStatement) it.releasePreparedStatementAfterUse()
                    }
                }
            }
        }

        override suspend fun releaseConnection(connection: Connection) {
            delegatee.releaseConnection(connection)
        }
    }
}

// In the subsequent processes, use this object.
val newDb = R2dbcDatabase(newConfig)

[dave08]

Wow, at least that's a good solution for now... thanks! I'd suppose it won't be integrated in Komapper in a future version? In Jasync, I used to be able to control this per request even... but now... I guess with R2dbc being so limiting in this sense, at least being able to control this per client would be good enough.

[Toshihiro Nakamura]

The releasePreparedStatementAfterUse function is a unique API to Jasync, so incorporating it into Komapper might be a bit hesitant. How about creating an extension function like the following in your application?

fun R2dbcDatabase.releasePreparedStatementAfterUse(): R2dbcDatabase {
    // use the code described above
    return ...
}

You can use the above extension function as follows:

db.releasePreparedStatementAfterUse().runQuery(query)

This approach allows you to integrate the specific functionality of Jasync into Komapper in a way that’s isolated to your application, without impacting the core of Komapper.

[dave08]

That wouldn't make a different pool, it just wraps the existing one?

[Toshihiro Nakamura]

Yes, it just wraps the existing one.

[dave08]

As long as your backend and dependency for mysql in r2dbc is jasync (you do pull it in with mysql-r2dbc), it might still make sense to include this function only in that module for those that might need this?

[dave08]

Or maybe (also?) as part of the dialect's constructor? I guess that wouldn't be possible since the whole r2dbc instance needs to be wrapped... but the function itself might still be useful...

[Toshihiro Nakamura]

In the future, it is possible to consider incorporating its function into the komapper-dialect-mysql-r2dbc module.

[dave08]

I can't seem to import JasyncStatement into my code... maybe because it's declared internal? internal final class JasyncStatement

[Toshihiro Nakamura]

Yes, I think so.

[dave08]

So what could I do then...?