You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/10/11 18:23:33 UTC
[GitHub] [shardingsphere] Jiabing-cyber opened a new issue, #21509: Auto Sharding Algorithm
Jiabing-cyber opened a new issue, #21509:
URL: https://github.com/apache/shardingsphere/issues/21509
# 1. issue
```pom
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.2.0</version>
</dependency>
```
> when insert sql`user_id` is `1`,doSharding create datasource is `db_order_0_readwrite_splitting1`,but I need `db_order_1_readwrite_splitting`.
exception：
```
Caused by: java.lang.IllegalStateException: No database route info
at com.google.common.base.Preconditions.checkState(Preconditions.java:508)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeDataSources(ShardingStandardRoutingEngine.java:209)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route0(ShardingStandardRoutingEngine.java:196)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditionsWithCondition(ShardingStandardRoutingEngine.java:117)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditions(ShardingStandardRoutingEngine.java:110)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.getDataNodes(ShardingStandardRoutingEngine.java:87)
at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route(ShardingStandardRoutingEngine.java:69)
at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:59)
at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:46)
at org.apache.shardingsphere.infra.route.engine.impl.PartialSQLRouteExecutor.route(PartialSQLRouteExecutor.java:58)
at org.apache.shardingsphere.infra.route.engine.SQLRouteEngine.route(SQLRouteEngine.java:55)
at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.route(KernelProcessor.java:59)
at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:51)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.createExecutionContext(ShardingSpherePreparedStatement.java:498)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:393)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:194)
```
```yml
spring:
config:
activate:
on-profile: 水平分表 + 读写分离
sharding-sphere:
props:
sql-show: true # 打印 sql
database:
name: ice-sharding-sphere-datasource # ShardingSphereDataSource 名
mode:
type: Standalone # 运行模式类型。可选配置：Standalone、Cluster
repository:
type: JDBC # 持久化仓库类型
datasource:
names:
db_order_0_master,
db_order_0_slave_0,
db_order_0_slave_1,
db_order_1_master,
db_order_1_slave_0,
db_order_1_slave_1, # 真实数据源名称，多个数据源用逗号区分
db_order_0_master:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url:
username:
password:
connection-timeout: 30_000 # 数据库链接超时时间，默认30秒，即30000
idle-timeout: 1800_000 # 空闲连接存活最大时间
max-lifetime: 0 # 控制池中链接的最长生命周期，值0表示无限生命周期，默认1800000，即30分钟
maximum-pool-size: 20 # 连接池最大连接数
minimum-idle: 4 # 最小空闲连接数量
auto-commit: true # 控制从池返回的链接的默认自动提交行为，默认值：true
db_order_0_slave_0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url:
username:
password:
connection-timeout: 30_000 # 数据库链接超时时间，默认30秒，即30000
idle-timeout: 1800_000 # 空闲连接存活最大时间
max-lifetime: 0 # 控制池中链接的最长生命周期，值0表示无限生命周期，默认1800000，即30分钟
maximum-pool-size: 20 # 连接池最大连接数
minimum-idle: 4 # 最小空闲连接数量
auto-commit: true # 控制从池返回的链接的默认自动提交行为，默认值：true
db_order_0_slave_1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url:
username:
password:
connection-timeout: 30_000 # 数据库链接超时时间，默认30秒，即30000
idle-timeout: 1800_000 # 空闲连接存活最大时间
max-lifetime: 0 # 控制池中链接的最长生命周期，值0表示无限生命周期，默认1800000，即30分钟
maximum-pool-size: 20 # 连接池最大连接数
minimum-idle: 4 # 最小空闲连接数量
auto-commit: true # 控制从池返回的链接的默认自动提交行为，默认值：true
db_order_1_slave_0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url:
username:
password:
connection-timeout: 30_000 # 数据库链接超时时间，默认30秒，即30000
idle-timeout: 1800_000 # 空闲连接存活最大时间
max-lifetime: 0 # 控制池中链接的最长生命周期，值0表示无限生命周期，默认1800000，即30分钟
maximum-pool-size: 20 # 连接池最大连接数
minimum-idle: 4 # 最小空闲连接数量
auto-commit: true # 控制从池返回的链接的默认自动提交行为，默认值：true
db_order_1_slave_1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url:
username:
password:
connection-timeout: 30_000 # 数据库链接超时时间，默认30秒，即30000
idle-timeout: 1800_000 # 空闲连接存活最大时间
max-lifetime: 0 # 控制池中链接的最长生命周期，值0表示无限生命周期，默认1800000，即30分钟
maximum-pool-size: 20 # 连接池最大连接数
minimum-idle: 4 # 最小空闲连接数量
auto-commit: true # 控制从池返回的链接的默认自动提交行为，默认值：true
db_order_1_master:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url:
username:
password:
connection-timeout: 30_000 # 数据库链接超时时间，默认30秒，即30000
idle-timeout: 1800_000 # 空闲连接存活最大时间
max-lifetime: 0 # 控制池中链接的最长生命周期，值0表示无限生命周期，默认1800000，即30分钟
maximum-pool-size: 20 # 连接池最大连接数
minimum-idle: 4 # 最小空闲连接数量
auto-commit: true # 控制从池返回的链接的默认自动提交行为，默认值：true
rules:
readwrite-splitting:
# 读写数据源配置
data-sources:
db_order_0_readwrite_splitting:
static-strategy:
write-data-source-name: db_order_0_master # 写库数据源名称
read-data-source-names: # 读库数据源列表，多个从数据源用逗号分隔
- db_order_0_slave_0
- db_order_0_slave_1
load-balancer-name: load-balancer-name-1 # 负载均衡算法名称
db_order_1_readwrite_splitting:
static-strategy:
write-data-source-name: db_order_1_master # 写库数据源名称
read-data-source-names: # 读库数据源列表，多个从数据源用逗号分隔
- db_order_1_slave_0
- db_order_1_slave_1
load-balancer-name: load-balancer-name-1 # 负载均衡算法名称
# 负载均衡算法配置
load-balancers:
load-balancer-name-1:
type: ROUND_ROBIN # 轮询算法
sharding:
tables:
# 数据库逻辑表名
t_order:
actual-data-nodes:
db_order_$->{0..1}_readwrite_splitting.t_order_$->{0..1}.t_order_0,
# 分库策略，缺省表示使用默认分库策略，以下的分片策略只能选其一
database-strategy:
# 分片策略一：用于单分片键的标准分片场景
standard:
# 分片列名称
sharding-column: user_id
# 分片算法名称
sharding-algorithm-name: mod-algorithm
# 分表策略，同分库策略
table-strategy:
standard:
sharding-column: order_no
sharding-algorithm-name: hash-mod-algorithm
# 分片算法配置
sharding-algorithms:
# 分片算法名称
mod-algorithm:
type: MOD
props:
sharding-count: 2
```
# 2. solution
> rename
`spring.sharding-sphere.rules.readwrite-splitting.data-sources.db_order_0_readwrite_splitting`
`spring.sharding-sphere.rules.readwrite-splitting.data-sources.db_order_1_readwrite_splitting`
to
`spring.sharding-sphere.rules.readwrite-splitting.data-sources.db_order_0`
`spring.sharding-sphere.rules.readwrite-splitting.data-sources.db_order_1`
set `spring.sharding-sphere.rules.sharding.tables.t_order.actual-data-nodes=db_order_$->{0..1}.t_order_$->{0..1}`
--
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] Jiabing-cyber closed issue #21509: Auto Sharding Algorithm
Posted by GitBox <gi...@apache.org>.
Jiabing-cyber closed issue #21509: Auto Sharding Algorithm
URL: https://github.com/apache/shardingsphere/issues/21509
--
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