You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "theFaustus (via GitHub)" <gi...@apache.org> on 2023/04/10 09:09:35 UTC
[GitHub] [shardingsphere] theFaustus opened a new issue, #25080: readwrite-splitting not working as expected, always reads from master(read-ds)
theFaustus opened a new issue, #25080:
URL: https://github.com/apache/shardingsphere/issues/25080
## Bug Report
**For English only**, other languages will not accept.
Before report a bug, make sure you have:
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
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 datasourse (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](https://github.com/apache/shardingsphere/issues/15629) none seemed to help.
### Reason analyze (If you can)
### 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 (read write splitting)
```
- !READWRITE_SPLITTING
dataSources:
readwrite_ds:
staticStrategy:
writeDataSourceName: master
readDataSourceNames:
- slave
loadBalancerName: readwrite-load-balancer
loadBalancers:
readwrite-load-balancer:
type: ROUND_ROBIN
```
### 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
https://github.com/theFaustus/bootiful-sharding
--
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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] theFaustus commented on issue #25080: readwrite-splitting not working as expected, always reads from master(write-ds)
Posted by "theFaustus (via GitHub)" <gi...@apache.org>.
theFaustus commented on issue #25080:
URL: https://github.com/apache/shardingsphere/issues/25080#issuecomment-1502997127
>
Thank you, this fixed the issue, now if I think about it, it actually makes sense. Holy mother of god, how many hours spent debugging this. Thank you again! Will close the issue.
--
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
[GitHub] [shardingsphere] theFaustus closed issue #25080: readwrite-splitting not working as expected, always reads from master(write-ds)
Posted by "theFaustus (via GitHub)" <gi...@apache.org>.
theFaustus closed issue #25080: readwrite-splitting not working as expected, always reads from master(write-ds)
URL: https://github.com/apache/shardingsphere/issues/25080
--
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
[GitHub] [shardingsphere] therealtuyen commented on issue #25080: readwrite-splitting not working as expected, always reads from master(write-ds)
Posted by "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
[GitHub] [shardingsphere] theFaustus commented on issue #25080: readwrite-splitting not working as expected, always reads from master(write-ds)
Posted by "theFaustus (via GitHub)" <gi...@apache.org>.
theFaustus commented on issue #25080:
URL: https://github.com/apache/shardingsphere/issues/25080#issuecomment-1502985811
Thank you guys for quick reply, will take a look and let you know
--
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
[GitHub] [shardingsphere] TeslaCN commented on issue #25080: readwrite-splitting not working as expected, always reads from master(write-ds)
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #25080:
URL: https://github.com/apache/shardingsphere/issues/25080#issuecomment-1502832052
Try replacing `master` with `readwrite_ds` here.
--
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