AWS JDBC Wrapper: Read-Write Splitting Plugin Fails to Fallback to Writer When Reader is Unavailable

[seiichi1101]

Describe the bug

I am building a web application using ALB + ECS + Aurora.
I am using Aurora MySQL - 8.0.mysql_aurora.3.04.3, with two instances (one writer and one reader) spread across different availability zones.

With Read/Write Splitting Plugin, I successfully distributed the load between the writer and reader instances.
However, when I use AWS Fault Injection Simulator (FIS) with the action aws:network:disrupt-connectivityaws to block network access to the reader's subnet, API requests from the client get stuck.

The logs continuously output WARN messages indicating connection failures:

{
    "timestamp": "2025-03-14T19:18:28.634671716Z",
    "message": "HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@74fbcc42 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.",
    "logger": "com.zaxxer.hikari.pool.PoolBase",
    "level": "WARN",
    "level_value": 30000
},
{
    "timestamp": "2025-03-14T19:18:33.591088926Z",
    "message": "HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@39f81981 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.",
    "logger": "com.zaxxer.hikari.pool.PoolBase",
    "level": "WARN",
    "level_value": 30000
},
{
    "timestamp": "2025-03-14T19:18:33.640687431Z",
    "message": "HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@7730174e (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.",
    "logger": "com.zaxxer.hikari.pool.PoolBase",
    "level": "WARN",
    "level_value": 30000
},
{
    "timestamp": "2025-03-14T19:18:38.548419767Z",
    "message": "Failed to connect to reader host: '<reader-instance-identifier>.abcdefghijklm.ap-northeast-1.rds.amazonaws.com:3306/'",
    "logger": "software.amazon.jdbc.plugin.readwritesplitting.ReadWriteSplittingPlugin",
    "level": "WARN",
    "level_value": 30000,
}

When a connection to a reader instance cannot be established, I expect the AWS JDBC Wrapper to automatically fallback to the writer instance, but this does not seem to happen.

Is there a configuration option in the AWS JDBC Wrapper that allows for automatic fallback to the writer when all reader instances are unavailable?

Expected Behavior

When a connection to a reader instance cannot be established, the Read/Write Splitting Plugin automatically route read queries to the writer instance instead of continuously failing.

What plugins are used? What other connection properties were set?

Read/Write Splitting Plugin

Current Behavior

• The plugin tries to connect to a reader instance.
• If the connection fails, it keeps logging WARN messages but does not fallback to the writer.
• This issue persists even though the writer instance remains available.

Reproduction Steps

Use the following configuration:

• application.yaml

spring:
  datasource:
    aws-jdbc-wrapper:
      url: <db-identifier>.cluster-abcdefghijklm.ap-northeast-1.rds.amazonaws.com
      username: ${DB_USERNAME}
      password: ${DB_PASSWORD}
      wrapper-plugins: initialConnection,auroraConnectionTracker,readWriteSplitting,failover2,efm2
      wrapper-dialect: aurora-mysql
      reader-host-selector-strategy: random
      cluster-instance-host-pattern: ?.abcdefghijklm.ap-northeast-1.rds.amazonaws.com

• DataSource

@ConfigurationProperties(prefix = "spring.datasource.aws-jdbc-wrapper")
data class AwsJdbcWrapperConfig(
    val url: String,
    val username: String,
    val password: String,
    val wrapperPlugins: String,
    val wrapperDialect: String,
    val readerHostSelectorStrategy: String,
    val clusterInstanceHostPattern: String,
)

import com.zaxxer.hikari.HikariConfig
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import org.springframework.jdbc.datasource.SimpleDriverDataSource
import software.amazon.jdbc.ConnectionProviderManager
import software.amazon.jdbc.HikariPooledConnectionProvider
import software.amazon.jdbc.hostlistprovider.RdsHostListProvider
import java.util.*
import java.util.concurrent.TimeUnit
import javax.sql.DataSource

@Configuration
class DataSourceConfig(
    val awsJdbcWrapperConfig: AwsJdbcWrapperConfig,
) {
    @Bean
    fun dataSource(): DataSource {
        // Disable HikariCP of Spring by using SimpleDriverDataSource
        val ds = SimpleDriverDataSource()
        val properties = Properties()

        // wrapper config
        properties["wrapperPlugins"] = awsJdbcWrapperConfig.wrapperPlugins
        properties["wrapperDialect"] = awsJdbcWrapperConfig.wrapperDialect
        properties["readerHostSelectorStrategy"] = awsJdbcWrapperConfig.readerHostSelectorStrategy
        properties["clusterInstanceHostPattern"] = awsJdbcWrapperConfig.clusterInstanceHostPattern
        properties["connectTimeout"] = TimeUnit.SECONDS.toMillis(30)

        // https://github.com/aws/aws-advanced-jdbc-wrapper/issues/1138
		// workaround to use internal connection pool
        properties[RdsHostListProvider.CLUSTER_ID.name] = "test"

        ds.setDriverClass(software.amazon.jdbc.Driver::class.java)
        ds.url = awsJdbcWrapperConfig.url
        ds.username = awsJdbcWrapperConfig.username
        ds.password = awsJdbcWrapperConfig.password
        ds.connectionProperties = properties

		// Enable jdbc wrapper internal connection pool		
        // https://github.com/aws/aws-advanced-jdbc-wrapper/blob/main/docs/using-the-jdbc-driver/using-plugins/UsingTheReadWriteSplittingPlugin.md#internal-connection-pooling
        ConnectionProviderManager.setConnectionProvider(
            HikariPooledConnectionProvider { _, _ ->
                // https://github.com/brettwooldridge/HikariCP
                val config = HikariConfig()
                config.maximumPoolSize = 10
                config.connectionTimeout = TimeUnit.SECONDS.toMillis(30)
				
                return@HikariPooledConnectionProvider config
            },
        )

        return ds
    }
}

Possible Solution

Add an option that allows automatic fallback to the writer instance when no reader instance is available.

Additional Information/Context

Server Side Kotlin with Spring Boot framework

The AWS Advanced JDBC Driver version used

software.amazon.jdbc:aws-advanced-jdbc-wrapper:2.5.4

JDK version used

openjdk version "17.0.14" 2025-01-21 LTS

Operating System and version

NAME="Amazon Linux" VERSION="2023" ID="amzn" ID_LIKE="fedora" VERSION_ID="2023" PLATFORM_ID="platform:al2023" PRETTY_NAME="Amazon Linux 2023.6.20250218"

[sergiyvamz]

Hi @seiichi1101

Thank you for reaching out with this issue. In the case you explained, the ReadWrite Splitting Plugin tries to establish a connection to a reader node, and, if it fails, it stays with the current connection. In your scenario it's a valid writer connection. No failover is necessary in this case.

In order to confirm this behaviour, could you please provide driver logs for the package software.amazon.jdbc ? Logs around provided timestamp, few seconds before and few seconds after, would be sufficient for troubleshooting.

"timestamp": "2025-03-14T19:18:38.548419767Z"

You can find more details about driver logging here:
https://github.com/aws/aws-advanced-jdbc-wrapper/blob/main/docs/using-the-jdbc-driver/UsingTheJdbcDriver.md#logging

Thank you!