ShardingSphere5.2.1 Upgrade to ShardingSphere5.5.0 PartialSQLRouteExecutor

[wu979]

Question

In version 5.5.0, a pre-routing check for mandatory routing was added. However, in my testing, the RouteContext returned after mandatory routing does not seem to apply the configured primary key generation strategy or sharding strategy. Comparing with version 5.2.1, it can be observed that even the lowest strategy in the ShardingRouteEngineFactory factory is unicast or standard sharding. However, version 5.5.0 does not enter a loop, and it's unclear whether ShardingSphereRule is not taking effect or if it's designed this way.

ShardingSphere 5.2.1 PartialSQLRouteExecutor
@OverRide
@SuppressWarnings({"unchecked", "rawtypes"})
public RouteContext route(final ConnectionContext connectionContext, final QueryContext queryContext, final ShardingSphereDatabase database) {
RouteContext result = new RouteContext();
for (Entry<ShardingSphereRule, SQLRouter> entry : routers.entrySet()) {
if (result.getRouteUnits().isEmpty()) {
result = entry.getValue().createRouteContext(queryContext, database, entry.getKey(), props, connectionContext);
} else {
entry.getValue().decorateRouteContext(result, queryContext, database, entry.getKey(), props, connectionContext);
}
}
if (result.getRouteUnits().isEmpty() && 1 == database.getResourceMetaData().getDataSources().size()) {
String singleDataSourceName = database.getResourceMetaData().getDataSources().keySet().iterator().next();
result.getRouteUnits().add(new RouteUnit(new RouteMapper(singleDataSourceName, singleDataSourceName), Collections.emptyList()));
}
return result;
}

ShardingSphere5.5.0 PartialSQLRouteExecutor
@OverRide
@SuppressWarnings({"unchecked", "rawtypes"})
public RouteContext route(final ConnectionContext connectionContext, final QueryContext queryContext, final RuleMetaData globalRuleMetaData, final ShardingSphereDatabase database) {
RouteContext result = new RouteContext();
Optional dataSourceName = findDataSourceByHint(queryContext.getHintValueContext(), database.getResourceMetaData().getStorageUnits());
if (dataSourceName.isPresent()) {
result.getRouteUnits().add(new RouteUnit(new RouteMapper(dataSourceName.get(), dataSourceName.get()), Collections.emptyList()));
return result;
}
for (Entry<ShardingSphereRule, SQLRouter> entry : routers.entrySet()) {
if (result.getRouteUnits().isEmpty()) {
result = entry.getValue().createRouteContext(queryContext, globalRuleMetaData, database, entry.getKey(), props, connectionContext);
} else {
entry.getValue().decorateRouteContext(result, queryContext, database, entry.getKey(), props, connectionContext);
}
}
if (result.getRouteUnits().isEmpty() && 1 == database.getResourceMetaData().getStorageUnits().size()) {
String singleDataSourceName = database.getResourceMetaData().getStorageUnits().keySet().iterator().next();
result.getRouteUnits().add(new RouteUnit(new RouteMapper(singleDataSourceName, singleDataSourceName), Collections.emptyList()));
}
return result;
}

Sharding.yaml
dataSources:
user:
driverClassName: com.mysql.cj.jdbc.Driver
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
jdbcUrl: jdbc:mysql://127.0.0.1:3306/user?useSSL=false&characterEncoding=utf-8
username: root
password: root
hikari:
auto-commit: true
connection-test-query: SELECT 1
connection-timeout: 500000
idle-timeout: 300000
max-lifetime: 900000
maximum-pool-size: 30
minimum-idle: 10
pool-name: HikariCP
validation-timeout: 1000
rules:

• !SHARDING
  tables:
  t_user_info:
  actualDataNodes: user.t_user_info_${0..1}
  tableStrategy:
  standard:
  shardingColumn: unique_id
  shardingAlgorithmName: t_user_info_inline
  defaultDatabaseStrategy:
  hint:
  shardingAlgorithmName: dynamic-hint
  defaultKeyGenerateStrategy:
  column: unique_id
  keyGeneratorName: snowflake
  shardingAlgorithms:
  t_user_info_inline:
  type: INLINE
  props:
  algorithm-expression: t_user_info_${unique_id % 2}
  dynamic-hint:
  type: DYNAMIC-HINT
  keyGenerators:
  snowflake:
  type: SNOWFLAKE
  props:
  sql-show: true

ShardingHintRoutingAlgorithm
public final class ShardingHintRoutingAlgorithm implements HintShardingAlgorithm<Comparable<?>> {

private final Environment environment;

public ShardingHintRoutingAlgorithm() {
    environment = BeanHolder.getBean(Environment.class);
}

@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<Comparable<?>> shardingValue) {
    return Lists.newArrayList(ShardingRoutingDataSourceHolder.getSharding());
}

@Override
public void init(Properties props) {
    environment.resolvePlaceholders("");
}

@Override
public String getType() {
    return "DYNAMIC-HINT";
}

}

Enter AOP via annotations, and enforce routing with HintManager based on logic.

[wu979]

The business supports global data centers, so routing enforcement based on the user's country is implemented in AOP aspects. However, some issues have been identified. For instance, Data Center A is configured with read-write separation. Forced routing might direct traffic to a secondary node, but if the current operation is a write operation, ShardingSphere may execute the write operation on the secondary node despite the enforced routing. Therefore, considering read-write separation, compatibility with read-write operations should be ensured. Currently, compatibility with ReadwriteSplittingSQLRouter has been implemented. The code is as follows:
public final class ShardingReadwriteSplittingSQLRouter implements SQLRouter {

@Override
public RouteContext createRouteContext(QueryContext queryContext, RuleMetaData ruleMetaData, ShardingSphereDatabase shardingSphereDatabase, ReadwriteSplittingRule rule, ConfigurationProperties configurationProperties, ConnectionContext connectionContext) {
    RouteContext result = new RouteContext();
    ReadwriteSplittingDataSourceRule singleDataSourceRule = rule.getSingleDataSourceRule();
    String dataSourceName = (new ReadwriteSplittingDataSourceRouter(singleDataSourceRule, connectionContext)).route(queryContext.getSqlStatementContext(), queryContext.getHintValueContext());
    result.getRouteUnits().add(new RouteUnit(new RouteMapper(singleDataSourceRule.getName(), dataSourceName), Collections.emptyList()));
    return result;
}

@Override
public void decorateRouteContext(RouteContext routeContext, QueryContext queryContext, ShardingSphereDatabase database, ReadwriteSplittingRule rule, ConfigurationProperties props, ConnectionContext connectionContext) {
    Collection<RouteUnit> toBeRemoved = new LinkedList<>();
    Collection<RouteUnit> toBeAdded = new LinkedList<>();
    for (RouteUnit each : routeContext.getRouteUnits()) {
        String dataSourceName = each.getDataSourceMapper().getLogicName();
        Optional<ReadwriteSplittingDataSourceRule> dataSourceRule = rule.findDataSourceRule(dataSourceName);
        if (dataSourceRule.isEmpty()) {
            Map<String, ReadwriteSplittingDataSourceRule> dataSourceMappers = rule.getDataSourceRules();
            for (Map.Entry<String, ReadwriteSplittingDataSourceRule> dataSourceMapper : dataSourceMappers.entrySet()) {
                if (dataSourceMapper.getValue().getDisabledDataSourceNames().contains(dataSourceName)) {
                    dataSourceRule = rule.findDataSourceRule(dataSourceMapper.getKey());
                    if (dataSourceRule.isPresent()) {
                        toBeRemoved.add(each);
                        String actualDataSourceName = (new ReadwriteSplittingDataSourceRouter(dataSourceRule.get(), connectionContext)).route(queryContext.getSqlStatementContext(), queryContext.getHintValueContext());
                        toBeAdded.add(new RouteUnit(new RouteMapper(each.getDataSourceMapper().getLogicName(), actualDataSourceName), each.getTableMappers()));
                    }
                }
            }
        } else {
            if (dataSourceRule.get().getName().equalsIgnoreCase(each.getDataSourceMapper().getActualName())) {
                toBeRemoved.add(each);
                String actualDataSourceName = (new ReadwriteSplittingDataSourceRouter(dataSourceRule.get(), connectionContext)).route(queryContext.getSqlStatementContext(), queryContext.getHintValueContext());
                toBeAdded.add(new RouteUnit(new RouteMapper(each.getDataSourceMapper().getLogicName(), actualDataSourceName), each.getTableMappers()));
            }
        }
    }
    routeContext.getRouteUnits().removeAll(toBeRemoved);
    routeContext.getRouteUnits().addAll(toBeAdded);
}

@Override
public int getOrder() {
    return ReadwriteSplittingOrder.ORDER + 2;
}

@Override
public Class<ReadwriteSplittingRule> getTypeClass() {
    return ReadwriteSplittingRule.class;
}

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.