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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
