[GitHub] [shardingsphere] therealtuyen commented on issue #25080: readwrite-splitting not working as expected, always reads from master(write-ds)

["therealtuyen (via GitHub)" <gi...@apache.org>]

therealtuyen commented on issue #25080:
URL: https://github.com/apache/shardingsphere/issues/25080#issuecomment-1502982839

   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](https://github.com/apache/shardingsphere/issues/15629#issuecomment-1105154441) 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/boffice_mst?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/boffice_mst?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/boffice_mst?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"
       }
   }
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org