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/02/18 12:36:51 UTC
[GitHub] [shardingsphere] wallacezhou opened a new issue #15506: auto sharding algorithm wrongly finds more than one sharding table, it should find exactly one table
wallacezhou opened a new issue #15506:
URL: https://github.com/apache/shardingsphere/issues/15506
## 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?
5.0.0
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC and ShardingSphere-Proxy
### Expected behavior
auto sharding algorithm with mod sharding algorithm, 6 databases are used, every database has 99 tables, so the sharding count is 594
when insert a record of table tb_test_info with id 701450883798396931
701450883798396931 % 6 = 1, the datasource is ds_1
701450883798396931 % 594 = 1, the actual table is tb_test_info_1
the record be insert into table ds_1.tb_test_info_1
### Actual behavior
more thant one datanodes are found and exception is thrown, the record failed to insert into table ds_1.tb_test_info_1
Caused by: java.lang.IllegalStateException: Insert statement does not support sharding table routing to multiple data nodes.
at com.google.common.base.Preconditions.checkState(Preconditions.java:508)
at org.apache.shardingsphere.sharding.route.engine.validator.dml.impl.ShardingInsertStatementValidator.postValidate(ShardingInsertStatementValidator.java:105)
at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.lambda$createRouteContext$1(ShardingSQLRouter.java:57)
at java.util.Optional.ifPresent(Optional.java:159)
at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:57)
at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:44)
at org.apache.shardingsphere.infra.route.engine.impl.PartialSQLRouteExecutor.route(PartialSQLRouteExecutor.java:62)
at org.apache.shardingsphere.infra.route.engine.SQLRouteEngine.route(SQLRouteEngine.java:53)
at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.route(KernelProcessor.java:54)
at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:46)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.createExecutionContext(ShardingSpherePreparedStatement.java:378)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:286)
### Reason analyze (If you can)
postValidate() method found more than one data node in file ShardingInsertStatementValidator.java
public void postValidate(final ShardingRule shardingRule, final SQLStatementContext<InsertStatement> sqlStatementContext,
final RouteContext routeContext, final ShardingSphereSchema schema) {
Optional<SubquerySegment> insertSelect = sqlStatementContext.getSqlStatement().getInsertSelect();
if (insertSelect.isPresent() && shardingConditions.isNeedMerge()) {
boolean singleRoutingOrSameShardingCondition = routeContext.isSingleRouting() || shardingConditions.isSameShardingCondition();
Preconditions.checkState(singleRoutingOrSameShardingCondition, "Subquery sharding conditions must be same with primary query.");
}
String tableName = sqlStatementContext.getSqlStatement().getTable().getTableName().getIdentifier().getValue();
if (!routeContext.isSingleRouting() && !shardingRule.isBroadcastTable(tableName)) {
boolean isSingleDataNode = routeContext.getOriginalDataNodes().stream().allMatch(dataNodes -> dataNodes.size() == 1);
Preconditions.checkState(isSingleDataNode, "Insert statement does not support sharding table routing to multiple data nodes.");
}
}
shardingsphere proxy create tables using auto table algorithm and mod algorithm
tables will be create in format tb_test_info_{0..593} in different datasource
eg.
ds1.tb_test_info_1
ds3.tb_test_info_21
ds5.tb_test_info_11
table is found by doSharding() method in ModShardingAlgorithm.java
public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Comparable<?>> shardingValue) {
for (String each : availableTargetNames) {
if (each.endsWith(String.valueOf(getLongValue(shardingValue.getValue()) % shardingCount))) {
return each;
}
}
return null;
}
it compare and find the first table end with a number in string format
the table is in format tb_test_info_{0..593} ,
701450883798396931 % 594 = 1
so in a datasource, the firtst table end with 1 will be selected
in case shardingsphere proxy create table for auto table algorithm and mod algorithm in format tb_test_info_{0..593}
"_" should be use in endsWith coompare, as more than one tables can end with the same number
if (each.endsWith("_".contact(String.valueOf(getLongValue(shardingValue.getValue()) % shardingCount)))) {
return each;
}
method route0() in ShardingStandardRoutingEngine.java do sharding with loop of all datasources
private Collection<DataNode> route0(final TableRule tableRule,
final ShardingStrategy databaseShardingStrategy, final List<ShardingConditionValue> databaseShardingValues,
final ShardingStrategy tableShardingStrategy, final List<ShardingConditionValue> tableShardingValues) {
Collection<String> routedDataSources = routeDataSources(tableRule, databaseShardingStrategy, databaseShardingValues);
Collection<DataNode> result = new LinkedList<>();
for (String each : routedDataSources) {
result.addAll(routeTables(tableRule, each, tableShardingStrategy, tableShardingValues));
}
return result;
}
all datasource will be execute the routeTables method, table will be searched so in this case, 6 datasources
finally the follwing 3 table are select in different datasources
ds_1.tb_test_info_1
ds_3.tb_test_info_21
ds_5.tb_test_info_11
also it's not effective for auto table algorithm to query table in all datasource, it's better to find the exactly one datasource and only do sharding in the datasource,
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
configs in spring boot starter for shardingsphere jdbc like below:
spring:
shardingsphere:
rules:
sharding:
auto-tables:
tb_test_info:
actual-data-sources: ds_$->{0..5}
sharding-strategy:
standard:
sharding-column: id
sharding-algorithm-name: mod
sharding-algorithms:
mod:
type: MOD
props:
sharding-count: 594
dist sql used in shardingsphere proxy
CREATE SHARDING TABLE RULE tb_test_info (
RESOURCES(ds_0,ds_1,ds_2,ds_3,ds_4,ds_5),
SHARDING_COLUMN=id,
TYPE(NAME=MOD,PROPERTIES("sharding-count"=594))
);
create table sql
CREATE TABLE `tb_test_info` (
`id` bigint NOT NULL ,
`name` varchar(32) DEFAULT NULL ,
PRIMARY KEY (`id`) USING BTREE
)
insert a record of table tb_test_info with id 701450883798396931
### Example codes for reproduce this issue (such as a github link).
--
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] wallacezhou edited a comment on issue #15506: auto sharding algorithm wrongly finds more than one sharding table, it should find exactly one table
Posted by GitBox <gi...@apache.org>.
wallacezhou edited a comment on issue #15506:
URL: https://github.com/apache/shardingsphere/issues/15506#issuecomment-1046439563
@strongduanmu hi, just see the comment in https://github.com/apache/shardingsphere/pull/15525 your fix with regex will
--
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] strongduanmu commented on issue #15506: auto sharding algorithm wrongly finds more than one sharding table, it should find exactly one table
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #15506:
URL: https://github.com/apache/shardingsphere/issues/15506#issuecomment-1045365884
Hi @wallacezhou, thank you for your feedback, I will check this 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] tristaZero closed issue #15506: auto sharding algorithm wrongly finds more than one sharding table, it should find exactly one table
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #15506:
URL: https://github.com/apache/shardingsphere/issues/15506
--
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] strongduanmu commented on issue #15506: auto sharding algorithm wrongly finds more than one sharding table, it should find exactly one table
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #15506:
URL: https://github.com/apache/shardingsphere/issues/15506#issuecomment-1048424747
> Test with updated code in master branch, now auto table sharding using mod algorithm can route to the correct datasource and table. Checked that hash mod algorithm in HashModShardingAlgorithm class has the similar sharding logic as mod algorithm , it could have the same issue, and need to double confirm and optimize it as mod algorithm if confirmed.
@wallacezhou Yes, we will check other algorithm soon.
--
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] wallacezhou commented on issue #15506: auto sharding algorithm wrongly finds more than one sharding table, it should find exactly one table
Posted by GitBox <gi...@apache.org>.
wallacezhou commented on issue #15506:
URL: https://github.com/apache/shardingsphere/issues/15506#issuecomment-1048423525
Test with updated code in master branch, now auto table sharding using mod algorithm can route to the correct datasource and table.
Checked that hash mod algorithm in HashModShardingAlgorithm class has the similar sharding logic as mod algorithm , it could have the same issue, and need to double confirm and optimize it as mod algorithm if confirmed.
--
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] wallacezhou edited a comment on issue #15506: auto sharding algorithm wrongly finds more than one sharding table, it should find exactly one table
Posted by GitBox <gi...@apache.org>.
wallacezhou edited a comment on issue #15506:
URL: https://github.com/apache/shardingsphere/issues/15506#issuecomment-1046439563
Hi, @strongduanmu , just see the comment in https://github.com/apache/shardingsphere/pull/15525
so your fix with regex will not be merged to master and need another way to fix it?
--
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] wallacezhou commented on issue #15506: auto sharding algorithm wrongly finds more than one sharding table, it should find exactly one table
Posted by GitBox <gi...@apache.org>.
wallacezhou commented on issue #15506:
URL: https://github.com/apache/shardingsphere/issues/15506#issuecomment-1046439563
@strongduanmu
--
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] strongduanmu commented on issue #15506: auto sharding algorithm wrongly finds more than one sharding table, it should find exactly one table
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #15506:
URL: https://github.com/apache/shardingsphere/issues/15506#issuecomment-1047681438
@wallacezhou Can you help test this problem in master branch?
--
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] wallacezhou commented on issue #15506: auto sharding algorithm wrongly finds more than one sharding table, it should find exactly one table
Posted by GitBox <gi...@apache.org>.
wallacezhou commented on issue #15506:
URL: https://github.com/apache/shardingsphere/issues/15506#issuecomment-1047684713
> @wallacezhou Can you help test this problem in master branch?
OK, I'will test it tommorrow and feed back the test result.
--
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] strongduanmu commented on issue #15506: auto sharding algorithm wrongly finds more than one sharding table, it should find exactly one table
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #15506:
URL: https://github.com/apache/shardingsphere/issues/15506#issuecomment-1046444819
> Hi, @strongduanmu , just see the comment in #15525 so your fix with regex will not be merged to master and need another way to fix it?
Yes, using regex will lead to performance degradation, I will try other ways to fix this today.
--
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