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 2020/01/10 10:48:02 UTC
[GitHub] [incubator-shardingsphere] victorcen opened a new issue #3929: How
to config rules for range query in sharding proxy without any coding ?
victorcen opened a new issue #3929: How to config rules for range query in sharding proxy without any coding ?
URL: https://github.com/apache/incubator-shardingsphere/issues/3929
## Question
**For English only**, other languages will not accept.
Before asking a question, make sure you have:
- Googled your question.
- Searched open and closed [GitHub issues](https://github.com/apache/incubator-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 **more than 7 days** and we cannot reproduce it on current information, we will **close it**.
Forgive me my English is not very good.
Which version of ShardingSphere did you use?
4.0.0-RC3
Which project did you use? Sharding-JDBC or Sharding-Proxy?
sharding-proxy
my question: How to config rules for RangeSharding rules in sharding proxy without any coding ?
there are two data sources ds_1 and ds_2 use date sharding database
both have a table with same name: visit
config-sharding.yaml:
schemaName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://10.3.13.87:3306/risparti?characterEncoding=utf8&serverTimezone=UTC&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true
username: root
password: 111111
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_1:
url: jdbc:mysql://10.3.13.88:3306/risparti?characterEncoding=utf8&serverTimezone=UTC&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true
username: root
password: 111111
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
shardingRule:
tables:
visit:
actualDataNodes: ds_${0..1}.visit
bindingTables:
- visit
defaultDatabaseStrategy:
inline:
shardingColumn: CREATEDATE
algorithmExpression: ds_${if(CREATEDATE<=20200107) {0} else {1}}
defaultTableStrategy:
none:
Column CREATEDATE is of type bigint
The inline sharding strategy is valid for the following sql:
select * from visit where CREATEDATE=20201107
it routes to datasource ds_1 , but is invalid for an range query
i want to config an rangesharding rule to make it valid for the following sql:
select * from visit where CREATEDATE>=20190101 and CREATEDATE<=20200101
or :
select * from visit where CREATEDATE between 20190101 and 20200101
Is there any example for rangeSharding rules without any coding?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] victorcen closed issue #3929: How to
config rules for range query in sharding proxy without any coding ?
Posted by GitBox <gi...@apache.org>.
victorcen closed issue #3929: How to config rules for range query in sharding proxy without any coding ?
URL: https://github.com/apache/incubator-shardingsphere/issues/3929
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] victorcen commented on issue #3929: How
to config rules for range query in sharding proxy without any coding ?
Posted by GitBox <gi...@apache.org>.
victorcen commented on issue #3929: How to config rules for range query in sharding proxy without any coding ?
URL: https://github.com/apache/incubator-shardingsphere/issues/3929#issuecomment-572990850
The inline sharding strategy is valid for the following sql:
select * from visit where CREATEDATE=20201107
it routes to datasource ds_0
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking commented on issue #3929: How
to config rules for range query in sharding proxy without any coding ?
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #3929: How to config rules for range query in sharding proxy without any coding ?
URL: https://github.com/apache/incubator-shardingsphere/issues/3929#issuecomment-573415748
I found this error is puzzle for me in this case in past 3 weeks.
There is a sharding table named 'test' with 4 actual tables with sharding key is 'id' column, then we will try proxy with:
- 1.select * from test
- 2.select * from test where id=102
- 3.select * from test where id>102
- 4.select * from test where id>102 and id<104
- 5.select * from test where id<>102
- 6.select * from test where id!=102
- 7.select * from test where id between 102 and 104
- 8.select * from test where id in (102, 104)
- 9.select * from test where id not in (102, 104)
- 10.select * from test where id>102 or id<102
Executing these No. 3\4\7\10 SQL will occur the same exception：
`
[2020-01-12 14:01:13] [C1000][10002] 2Unknown exception: [Inline strategy cannot support range sharding.]
`
When I read `incubator-shardingsphere/sharding-core/sharding-core-common/src/main/java/org/apache/shardingsphere/core/strategy/route/inline/InlineShardingStrategy.java`, we can learn InlineShardingStrategy isn't support Range query:
`
Preconditions.checkState(shardingValue instanceof ListRouteValue, "Inline strategy cannot support range sharding.");
`
But the puzzle is: the No.10 SQL(can't executed) is equals with the No.5(executed correctly), the I wanna ask why and how to support range query in sharding strategy？
Aside from performance considerations, we can find the most effective way to directly support this feature by broadcasting the whole Range query to all sharding db/tables，just like ‘<>’ operator.
Then we can let it work well and optimized later，we can insert here one line to broadcasting sql：
`
if (shardingValue instanceof RangeRouteValue) {
return availableTargetNames;
}
`
Then the No.3\4\7\10 SQL will be executed correctly.
Related PR： https://github.com/apache/incubator-shardingsphere/pull/3945
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] victorcen commented on issue #3929: How
to config rules for range query in sharding proxy without any coding ?
Posted by GitBox <gi...@apache.org>.
victorcen commented on issue #3929: How to config rules for range query in sharding proxy without any coding ?
URL: https://github.com/apache/incubator-shardingsphere/issues/3929#issuecomment-573495845
@kimmking thanks!
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] dongzl commented on issue #3929: How to
config rules for range query in sharding proxy without any coding ?
Posted by GitBox <gi...@apache.org>.
dongzl commented on issue #3929: How to config rules for range query in sharding proxy without any coding ?
URL: https://github.com/apache/incubator-shardingsphere/issues/3929#issuecomment-573021109
Hi @victorcen , why do you want without any coding? If the the sharding strategy is complex, you can develop it and extend the sharding strategy interface, and package a jar file, put to sharding-proxy `lib directory`, it is also very simple.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking edited a comment on issue
#3929: How to config rules for range query in sharding proxy without any
coding ?
Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #3929: How to config rules for range query in sharding proxy without any coding ?
URL: https://github.com/apache/incubator-shardingsphere/issues/3929#issuecomment-573415748
I found this error is puzzle for me in this case in past 3 weeks.
There is a sharding table named 'test' with 4 actual tables with sharding key is 'id' column, then we will try proxy with:
- 1.select * from test
- 2.select * from test where id=102
- 3.select * from test where id>102
- 4.select * from test where id>102 and id<104
- 5.select * from test where id<>102
- 6.select * from test where id!=102
- 7.select * from test where id between 102 and 104
- 8.select * from test where id in (102, 104)
- 9.select * from test where id not in (102, 104)
- 10.select * from test where id>102 or id<102
Executing these No. 3\4\7\10 SQL will occur the same exception：
```
[2020-01-12 14:01:13] [C1000][10002] 2Unknown exception: [Inline strategy cannot support range sharding.]
```
When I read `incubator-shardingsphere/sharding-core/sharding-core-common/src/main/java/org/apache/shardingsphere/core/strategy/route/inline/InlineShardingStrategy.java`, we can learn InlineShardingStrategy isn't support Range query:
```
Preconditions.checkState(shardingValue instanceof ListRouteValue, "Inline strategy cannot support range sharding.");
```
But the puzzle is: the No.10 SQL(can't executed) is equals with the No.5(executed correctly), the I wanna ask why and how to support range query in sharding strategy？
Aside from performance considerations, we can find the most effective way to directly support this feature by broadcasting the whole Range query to all sharding db/tables，just like ‘<>’ operator.
Then we can let it work well and optimized later，we can insert here one line to broadcasting sql：
```
if (shardingValue instanceof RangeRouteValue) {
return availableTargetNames;
}
```
Then the No.3\4\7\10 SQL will be executed correctly.
Related PR： https://github.com/apache/incubator-shardingsphere/pull/3945
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services