Issue 1:
Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

Searched open and closed GitHub issues.

Read documentation: ShardingSphere Doc.

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 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 route0(final TableRule tableRule,

final ShardingStrategy databaseShardingStrategy, final List databaseShardingValues,

final ShardingStrategy tableShardingStrategy, final List tableShardingValues) {

Collection routedDataSources = routeDataSources(tableRule, databaseShardingStrategy, databaseShardingValues);

Collection 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).

Issue 2:
For English only, other languages we will close it directly.

Please answer these questions before submitting your issue. Thanks!

Which version of Sharding-Sphere do you using?

Expected behavior

Actual behavior

Reason analyze

Steps to reproduce the behavior

Please provide the reproduce example codes (such as a github link).