readwrite-splitting not working as expected, always reads from master(write-ds)

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

Searched open and closed GitHub issues.
Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

I have used spring-boot-starters 5.1.2, then tried with 5.2.1 also tried with 5.3.2 non-spring-boot starters

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

Spring Boot, Spring Data Jpa, ShardingSphere-JDBC

Expected behavior

Writes happen on write datasource (master) and reads happen on read datasource (slave)

Actual behavior

Reads always go to master no matter what kind of queries I tried. Even with simple queries, or @Query, or findById or custom ones it always goes to master. I've tried solutions mentioned here #15629 none seemed to help.

Reason analyze (If you can)

Worth to mention that I tried almost every loadBalancer name from org.apache.shardingsphere.readwritesplitting.algorithm.loadbalance Also tried with @Transctional, without and with read-only. Also tried the IgnoreJpaTransaction*** bean proposed in some other issues. I've been looking in PartialSQLRouteExecutor which seemingly at first creates a Sharding routeContext and then proceeds to decorate it, and when it comes to ReadwriteSplittingSQLRouter to decorate it tries to find the data source rule, but it is empty But if I dive in I can see my dataSources I find it very strange, not sure if helps though. Update 1: Tried with mysql, same behavior, will attach the configs

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Config for 5.1.2 starter

spring:
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQL10Dialect
    #        default_schema: reviews
    open-in-view: false
    #    show-sql: true
    #    properties:
    #      hibernate:
    #        format_sql: true
    hibernate:
      ddl-auto: update
  application:
    name: reviews-service
  profiles:
    active: default

  shardingsphere:
    datasource:
      names: master,slave0,slave1

      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: org.postgresql.Driver
        jdbc-url: jdbc:postgresql://localhost:5432/reviews-db
        username: my_user
        password: my_password
        auto-commit: false

      slave0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: org.postgresql.Driver
        jdbc-url: jdbc:postgresql://localhost:64111/reviews-db
        username: my_user
        password: my_password
        auto-commit: false

      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: org.postgresql.Driver
        jdbc-url: jdbc:postgresql://localhost:64110/reviews-db
        username: my_user
        password: my_password
        auto-commit: false

    rules:
      sharding:
        tables:
          reviews:
            actual-data-nodes: master.reviews_$->{0..1}
            table-strategy:
              standard:
                sharding-column: course_id
                sharding-algorithm-name: inline
            key-generate-strategy:
              column: course_id
              key-generator-name: snowflake
        sharding-algorithms:
          inline:
            type: INLINE
            props:
              algorithm-expression: reviews_$->{course_id % 2}
              allow-range-query-with-inline-sharding: true
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              max-vibration-offset: 1
              max-tolerate-time-difference-milliseconds: 10

      readwrite-splitting:
        data-sources:
          random:
            type: Static
            load-balancer-name: round_robin
            props:
              write-data-source-name: master
              read-data-source-names: slave0,slave1
        load-balancers:
          round_robin:
            type: ROUND_ROBIN

    props:
      proxy-hint-enabled: true
      sql-show: true

management:
  endpoints:
    web:
      exposure:
        include: "*"
  endpoint:
    shutdown:
      enabled: true
    health:
      show-components: always
      show-details: always
  health:
    livenessstate:
      enabled: true
    readinessstate:
      enabled: true

logging:
  level:
    inc.evil: DEBUG
server:
  port: 8070

Config for 5.2.1 starter (read write splitting)

      readwrite-splitting:
        data-sources:
          readwrite_ds:
            staticStrategy:
              writeDataSourceName: master
              readDataSourceNames:
                - slave0
                - slave1
            loadBalancerName: robin
        loadBalancers:
          robin:
            type: ROUND_ROBIN

Config for 5.3.2 non-starter

dataSources:
  master:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: org.postgresql.Driver
    jdbcUrl: jdbc:postgresql://localhost:5432/reviews-db
    username: my_user
    password: my_password

  slave0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: org.postgresql.Driver
    jdbcUrl: jdbc:postgresql://localhost:64111/reviews-db
    username: my_user
    password: my_password

  slave1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: org.postgresql.Driver
    jdbcUrl: jdbc:postgresql://localhost:64110/reviews-db
    username: my_user
    password: my_password

mode:
  type: Standalone
  repository:
    type: JDBC

rules:
  - !SHARDING
    tables:
      reviews:
        actualDataNodes: master.reviews_$->{0..1}
        tableStrategy:
          standard:
            shardingColumn: course_id
            shardingAlgorithmName: inline
        keyGenerateStrategy:
          column: course_id
          keyGeneratorName: snowflake
    shardingAlgorithms:
      inline:
        type: INLINE
        props:
          algorithm-expression: reviews_$->{course_id % 2}
          allow-range-query-with-inline-sharding: true
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
        props:
          max-vibration-offset: 1
          max-tolerate-time-difference-milliseconds: 10

  - !READWRITE_SPLITTING
    dataSources:
      readwrite_ds:
        staticStrategy:
          writeDataSourceName: master
          readDataSourceNames:
            - slave0
            - slave1
        loadBalancerName: readwrite-load-balancer
    loadBalancers:
      readwrite-load-balancer:
        type: ROUND_ROBIN
props:
  proxy-hint-enabled: true
  sql-show: true

Config for 5.3.2 with mysql

dataSources:
  master:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/reviews-db?allowPublicKeyRetrieval=true&useSSL=false
    username: my_user
    password: my_password
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
    minPoolSize: 1

  slave0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:52471/reviews-db?allowPublicKeyRetrieval=true&useSSL=false
    username: my_user
    password: my_password
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
    minPoolSize: 1

  slave1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:52472/reviews-db?allowPublicKeyRetrieval=true&useSSL=false
    username: my_user
    password: my_password
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
    minPoolSize: 1

mode:
  type: Standalone
  repository:
    type: JDBC

rules:
  - !SHARDING
    tables:
      reviews:
        actualDataNodes: master.reviews_$->{0..1}
        tableStrategy:
          standard:
            shardingColumn: course_id
            shardingAlgorithmName: inline
        keyGenerateStrategy:
          column: course_id
          keyGeneratorName: snowflake
    shardingAlgorithms:
      inline:
        type: INLINE
        props:
          algorithm-expression: reviews_$->{course_id % 2}
          allow-range-query-with-inline-sharding: true
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
        props:
          max-vibration-offset: 1
          max-tolerate-time-difference-milliseconds: 10

  - !READWRITE_SPLITTING
    dataSources:
      readwrite_ds:
        staticStrategy:
          writeDataSourceName: master
          readDataSourceNames:
            - slave0
            - slave1
        loadBalancerName: readwrite-load-balancer
    loadBalancers:
      readwrite-load-balancer:
        type: ROUND_ROBIN
props:
  proxy-hint-enabled: true
  sql-show: true

Example codes for reproduce this issue (such as a github link).

You can take a look at this repo, it is a fairly simple one, it has docker containers for postgresql master/slave. And then in commandLineRunner I try to do some fetching/inserts, sharding works as expected but as I mentioned the reads always go to master. You can take a look at the commits to follow my trying process with different versions of sharding sphere In each commit sharding works, but readwrite-splitting goes only to master. https://github.com/theFaustus/bootiful-sharding

It's pretty weird that some people still get into this trouble. Since version 5.2.1, I have no longer encountered this problem with IgnoreReadOnlyTransaction what you already mentioned.

I used 5.21 version for a while with this configuration.

spring.shardingsphere.enabled=false
spring.shardingsphere.props.sql-show=true
logging.level.org.hibernate.engine.jdbc.env.internal.LobCreatorBuilderImpl=ERROR
spring.shardingsphere.datasource.names=master,slave1,slave2
spring.shardingsphere.datasource.master.jdbc-url=jdbc:postgresql://mdo-master-staging:5432/demo?stringtype=unspecified
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=org.postgresql.Driver
spring.shardingsphere.datasource.master.username=postgres
spring.shardingsphere.datasource.master.password=postgres
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:postgresql://mdo-slave-staging:5432/demo?stringtype=unspecified
spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=org.postgresql.Driver
spring.shardingsphere.datasource.slave1.username=postgres
spring.shardingsphere.datasource.slave1.password=postgres
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:postgresql://mdo-slave-staging:5432/demo?stringtype=unspecified
spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=org.postgresql.Driver
spring.shardingsphere.datasource.slave2.username=postgres
spring.shardingsphere.datasource.slave2.password=postgres
spring.shardingsphere.rules.readwrite-splitting.data-sources.readwrite_ds.static-strategy.write-data-source-name=master
spring.shardingsphere.rules.readwrite-splitting.data-sources.readwrite_ds.static-strategy.read-data-source-names=slave1,slave2
spring.shardingsphere.rules.readwrite-splitting.data-sources.readwrite_ds.load-balancer-name=round_robin
spring.shardingsphere.rules.readwrite-splitting.load-balancers.round_robin.type=ROUND_ROBIN

For the latest version 5.3.2 I moved my configuration to kotlin. And it works perfect, here is my configuration:


import com.zaxxer.hikari.HikariDataSource
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory
import org.apache.shardingsphere.infra.config.algorithm.AlgorithmConfiguration
import org.apache.shardingsphere.infra.config.mode.ModeConfiguration
import org.apache.shardingsphere.infra.config.props.ConfigurationPropertyKey
import org.apache.shardingsphere.infra.config.rule.RuleConfiguration
import org.apache.shardingsphere.mode.repository.standalone.StandalonePersistRepositoryConfiguration
import org.apache.shardingsphere.readwritesplitting.api.ReadwriteSplittingRuleConfiguration
import org.apache.shardingsphere.readwritesplitting.api.rule.ReadwriteSplittingDataSourceRuleConfiguration
import org.apache.shardingsphere.readwritesplitting.api.strategy.StaticReadwriteSplittingStrategyConfiguration
import org.springframework.beans.factory.annotation.Value
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import java.sql.SQLException
import java.util.*
import javax.sql.DataSource

@Configuration(proxyBeanMethods = false) @ConditionalOnProperty(prefix = "app.datasource", name = ["mode"], havingValue = "multi") class ShardingSphereDataSourceConfigurer: AbstractDatasourceConfigurer() { @Value("\${app.datasource.schema:public}") private val schema: String? = null

@Value("\${app.datasource.database:demo}")
private val database: String? = null

@Value("\${app.datasource.host:localhost}")
private val host: String? = null

@Value("\${app.datasource.port:5432}")
private val port: String? = null

@Value("\${app.datasource.username:username}")
private val username: String? = null

@Value("\${app.datasource.password:password}")
private val password: String? = null

@Value("\${app.datasource.slave1.host:localhost}")
private val sl1Host: String? = null

@Value("\${app.datasource.slave1.port:5432}")
private val sl1Port: String? = null

@Value("\${app.datasource.slave2.host:localhost}")
private val sl2Host: String? = null

@Value("\${app.datasource.slave2.port:5432}")
private val sl2Port: String? = null

@Value("\${app.datasource.maximumPoolSize}")
private val maximumPoolSize: Int? = null

@Value("\${app.datasource.connectionTimeout}")
private val connectionTimeout: Long? = null

@Value("\${app.datasource.maxLifetime}")
private val maxLifetime: Long? = null

// Solving read-write problem with spring. Remove this bean if you dont cause this.
@Bean
fun ignoreJPAReadOnlyTransactionBeanPostProcessor(): IgnoreJPAReadOnlyTransactionBeanPostProcessor {
    return IgnoreJPAReadOnlyTransactionBeanPostProcessor()
}

@Bean
@Throws(SQLException::class)
fun dataSource(): DataSource {
    return ShardingSphereDataSourceFactory.createDataSource(createModeConfiguration(), createDataSourceMap(), createRuleConfiguration(), createProperties())
}

private fun createProperties(): Properties {
    val result = Properties()
    result.setProperty(ConfigurationPropertyKey.SQL_SHOW.key, "true")
    return result
}

private fun createRuleConfiguration(): Collection<RuleConfiguration> {
    val result: MutableCollection<RuleConfiguration> = LinkedList()
    result.add(createReadwriteSplittingRuleConfiguration())
    return result
}

private fun createReadwriteSplittingRuleConfiguration(): ReadwriteSplittingRuleConfiguration {
    val dataSourceConfig = ReadwriteSplittingDataSourceRuleConfiguration(
            MASTER_SOURCE,
            StaticReadwriteSplittingStrategyConfiguration(MASTER_SOURCE, listOf(SLAVE1_SOURCE, SLAVE2_SOURCE)),
            null,
            "round_robin"
    )
    return ReadwriteSplittingRuleConfiguration(setOf(dataSourceConfig),
            java.util.Map.of("round_robin", AlgorithmConfiguration("ROUND_ROBIN", null)))
}

private fun createDataSourceMap(): Map<String, DataSource> {
    val result: MutableMap<String, DataSource> = LinkedHashMap()
    result[MASTER_SOURCE] = createDataSource(MASTER_SOURCE, host, port)
    result[SLAVE1_SOURCE] = createDataSource(SLAVE1_SOURCE, sl1Host, sl1Port)
    result[SLAVE2_SOURCE] = createDataSource(SLAVE2_SOURCE, sl2Host, sl2Port)
    return result
}

private fun createDataSource(poolDs: String, host: String?, port: String?): DataSource {
    val dataSource = HikariDataSource()
    dataSource.driverClassName = "org.postgresql.Driver"
    dataSource.jdbcUrl = String.format(JDBC_URL, host, port, database)
    dataSource.poolName = "DATASOURCE - $poolDs"
    dataSource.username = username
    dataSource.password = password
    dataSource.schema = schema
    dataSource.maximumPoolSize = maximumPoolSize!!
    dataSource.connectionTimeout = connectionTimeout!!
    dataSource.maxLifetime = maxLifetime!!
    addMetaData(dataSource)
    return dataSource
}

private fun createModeConfiguration(): ModeConfiguration {
    return ModeConfiguration("Standalone", StandalonePersistRepositoryConfiguration("JDBC", Properties()))
}

companion object {
    private const val MASTER_SOURCE = "master"
    private const val SLAVE1_SOURCE = "slave1"
    private const val SLAVE2_SOURCE = "slave2"
}

}

apache / shardingsphere