Table Name Format Causing Failure in Configuring bindingTableGroups

[bineea]

Question

To configure binding table groups using org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration#bindingTableGroups, you can follow these steps. This configuration is necessary because custom sharding logic (implemented via org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm) is being used. The table name format is "logictablename_year_quarter". In the org.apache.shardingsphere.sharding.rule.TableRule#createTableDataNode logic, a regular expression Pattern.compile("\d+$") is used to parse the prefix, leading to "logictablename_year" being considered as the prefix, which results in the failure of org.apache.shardingsphere.sharding.rule.ShardingRule#isValidActualTableName validation.

shardingsphere：5.1.1
jdk1.8

[terrymanu]

What is your configuration?

[bineea]

What is your configuration?

@configuration
public class ShardingConfig {

private static final List<String> geoAndBillingDateShardingTableNames = Collections.unmodifiableList(Arrays.asList(
        "biz_sdms_accrual_billing_init",
        "biz_sdms_accrual_billing_init_history",
        "biz_sdms_accrual_billing_result",
        "biz_sdms_accrual_billing_result_detail",
        "biz_sdms_accrual_billing_retrigger_track",
        "biz_sdms_accrual_billing_factor_error",
        "biz_sdms_accrual_billing_pool_error",
        "biz_sdms_accrual_billing_late_detail",
        "biz_sdms_accrual_billing_status"
));

@Autowired
private ShardingProperties shardingProperties;
@Autowired
private HikariProperties hikariProperties;

@Bean
public DataSource shardingDataSource() throws SQLException {

    ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
    // date
    Map<String, ShardingSphereAlgorithmConfiguration> shardingAlgorithms = shardingRuleConfig.getShardingAlgorithms();
    Properties dateProps = new Properties();
    dateProps.put("strategy", "standard");
    dateProps.put("algorithmClassName", DateShardingAlgorithmImpl.class.getName());
    ShardingSphereAlgorithmConfiguration dateAlgorithmConfiguration = new ShardingSphereAlgorithmConfiguration("CLASS_BASED", dateProps);
    // geo
    Properties geoProps = new Properties();
    geoProps.put("strategy", "standard");
    geoProps.put("algorithmClassName", GeoShardingAlgorithmImpl.class.getName());
    ShardingSphereAlgorithmConfiguration geoAlgorithmConfiguration = new ShardingSphereAlgorithmConfiguration("CLASS_BASED", geoProps);
   
    shardingAlgorithms.put("date-algorithm", dateAlgorithmConfiguration);
    shardingAlgorithms.put("geo-code-algorithm", geoAlgorithmConfiguration);

    Collection<ShardingTableRuleConfiguration> tableRuleConfigs = new ArrayList<>();
    tableRuleConfigs.addAll(getGeoAndBillingDateShardingTableRuleConfig(geoAndBillingDateShardingTableNames));
    shardingRuleConfig.getTables().addAll(tableRuleConfigs);

    List<String> bindingTableGroups = new ArrayList<>();
    bindingTableGroups.add(String.join(String.valueOf(Constants.SEPARATE_COMMA), geoAndBillingDateShardingTableNames));
    shardingRuleConfig.getBindingTableGroups().addAll(bindingTableGroups);

    final Properties properties = new Properties();
    //是否打印SQL解析和改写日志
    properties.setProperty("sql-show", Boolean.FALSE.toString());
    //用于SQL执行的工作线程数量，为零则表示无限制
    properties.setProperty("kernel-executor-size", "20");
    //每个物理数据库为每次查询分配的最大连接数量
    properties.setProperty("max-connections-size-per-query", "6");
    //是否在启动时检查分表元数据一致性（将按照分库分表规则定义的第一张表获取数据库表结构信息，如果第一张表没有创建，启动没有异常，但是sql执行时将产生异常）
    properties.setProperty("check-table-metadata-enabled", "false");

    return ShardingSphereDataSourceFactory.createDataSource(createDataSourceMap(), Collections.singleton(shardingRuleConfig), properties);
}

private List<ShardingTableRuleConfiguration> getGeoAndBillingDateShardingTableRuleConfig(List<String> tableNameList) {
    List<ShardingTableRuleConfiguration> tableRuleConfigurations = new ArrayList<>();
    for (String tableName : tableNameList) {
        ShardingTableRuleConfiguration result = new ShardingTableRuleConfiguration(tableName, "DB_${['AP','LA','NA','EMEA']}." + tableName + "_${[2024,2025,2026,2027,2028,2029]}_${1..4}");
        result.setDatabaseShardingStrategy(new StandardShardingStrategyConfiguration("geo_code", "geo-code-algorithm"));
        result.setTableShardingStrategy(new StandardShardingStrategyConfiguration("billing_date", "date-algorithm"));
        tableRuleConfigurations.add(result);
    }
    return tableRuleConfigurations;
}

private Map<String, DataSource> createDataSourceMap() {
    Map<String, DataSource> result = new HashMap<>(5);
    List<DataSourceConfig> configs = shardingProperties.getConfigs();
    for (DataSourceConfig config : configs) {
        result.put(config.getName(), DataSourceUtil.createDataSource(hikariProperties, config.getDriverClassName(), config.getJdbcUrl(), config.getUserName(), config.getPassword()));
    }
    return result;
}

}

[terrymanu]

The issue involves other third-party dependencies, but our focus is solely on ShardingSphere itself. Since ShardingSphere implements the JDBC interface, standard applications should be functional. We wish to allocate more effort towards enhancing the current version, and therefore will no longer handle such issues. Please read the documentation or provide more effective information when submitting an issue.

[github-actions]

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