Routed target zta_execution_detail_19 does not exist, available targets are `[zta_execution_0....

[gaolove]

Bug Report

Which version of ShardingSphere did you use?

<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core</artifactId>
<version>5.4.1</version>

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Expected behavior

Correct routing to the correct shard table

Actual behavior

Occasionally the wrong routing rule is used, most of the time it's normal

2023-11-09 17:03:05,188 [WARN ] [SqlExceptionHelper.java:145] [http-nio-8004-exec-2] SQL Error: 20081, SQLState: HY000
2023-11-09 17:03:05,193 [ERROR] [SqlExceptionHelper.java:150] [http-nio-8004-exec-2] Routed target `zta_execution_detail_19` does not exist, available targets are `[zta_execution_0, ..., zta_execution_127]`.

The logical table of zta_execution_detail_19 is zta_execution_detail, but the ‘available targets’ in the error log is zta_execution.

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

This is my configuration class.

@Configuration
@Order(Ordered.HIGHEST_PRECEDENCE)
public class ShardingsphereConfig {
    @Value("${spring.shardingsphere.datasource.testing-platform.jdbc-url}")
    private String jdbcUrl;
    @Value("${spring.shardingsphere.datasource.testing-platform.username}")
    private String userName;
    @Value("${spring.shardingsphere.datasource.testing-platform.password}")
    private String password;

    @Bean
    @Primary
    public DataSource dataSource() throws SQLException, NoSuchFieldException, IllegalAccessException {
        return ShardingSphereDataSourceFactory.createDataSource(createModeConfiguration(), createDataSourceMap(), createRuleConfiguration(), createProperties());
    }

    private ModeConfiguration createModeConfiguration() {
        return new ModeConfiguration("Standalone", new StandalonePersistRepositoryConfiguration("JDBC", new Properties()));
    }

    private Map<String, DataSource> createDataSourceMap() {
        Map<String, DataSource> result = new LinkedHashMap<>();
        result.put("testing_platform", createDataSource());
        return result;
    }

    private DataSource createDataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl(jdbcUrl);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);
        dataSource.setMaximumPoolSize(10);
        dataSource.setConnectionTimeout(300000);
        return dataSource;
    }

    private Collection<RuleConfiguration> createRuleConfiguration() {
        Collection<RuleConfiguration> result = new LinkedList<>();
        result.add(createShardingRuleConfiguration());
        result.add(createSingleRuleConfiguration());
        return result;
    }

    private ShardingRuleConfiguration createShardingRuleConfiguration() {
        ShardingRuleConfiguration result = new ShardingRuleConfiguration();
        result.getTables().add(getExecutionTableRuleConfiguration());
        result.getTables().add(getDetailTableRuleConfiguration());
        result.setDefaultDatabaseShardingStrategy(new NoneShardingStrategyConfiguration());
        result.getKeyGenerators().put("snowflake", new AlgorithmConfiguration("SNOWFLAKE", new Properties()));
        Properties executionProps = new Properties();
        executionProps.setProperty("algorithm-expression", "zta_execution_$->{job_id % 128}");
        result.getShardingAlgorithms().put("execution-inline", new AlgorithmConfiguration("INLINE", executionProps));
        Properties detailProps = new Properties();
        detailProps.setProperty("algorithm-expression", "zta_execution_detail_$->{job_id % 512}");
        result.getShardingAlgorithms().put("detail-inline", new AlgorithmConfiguration("INLINE", detailProps));
        return result;
    }

    private SingleRuleConfiguration createSingleRuleConfiguration() {
        return new SingleRuleConfiguration(Collections.singleton("*.*"), "testing_platform");
    }

    private ShardingTableRuleConfiguration getExecutionTableRuleConfiguration() {
        ShardingTableRuleConfiguration result = new ShardingTableRuleConfiguration("zta_execution", "testing_platform.zta_execution_$->{0..127}");
        result.setKeyGenerateStrategy(new KeyGenerateStrategyConfiguration("id", "snowflake"));
        result.setTableShardingStrategy(new StandardShardingStrategyConfiguration("job_id", "execution-inline"));
        return result;
    }

    private ShardingTableRuleConfiguration getDetailTableRuleConfiguration() {
        ShardingTableRuleConfiguration result = new ShardingTableRuleConfiguration("zta_execution_detail", "testing_platform.zta_execution_detail_$->{0..511}");
        result.setKeyGenerateStrategy(new KeyGenerateStrategyConfiguration("id", "snowflake"));
        result.setTableShardingStrategy(new StandardShardingStrategyConfiguration("job_id", "detail-inline"));
        return result;
    }

    private Properties createProperties() {
        Properties result = new Properties();
        return result;
    }
}

Maybe it's related to concurrency? Or do I need to modify my configuration?

Example codes for reproduce this issue (such as a github link).

[gaolove]

It should be caused by concurrency. This problem is more likely to be triggered when my system has a large number of requests.

[fengzhongye]

i hava this error too

[lenxeon]

i have got the same problem, but when i send request once more， This bug is not stable complex is now

[strongduanmu]

@lenxeon Can you give a demo to help reproduce this exception?

[lenxeon]

@lenxeon Can you give a demo to help reproduce this exception?

in my project, It is low probability randomness, when i send request once more, it is running ok.

[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.

[cunzhiwang]

I also encountered the same exception, the difference is that I used it in Datax, because Datax also executes tasks in multiple threads, so I think the error is the same as yours

[cunzhiwang]

This is my config file

dataSources:
  ds0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/db_lab_orders_0?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
    username: root
    password: root
  ds1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/db_lab_orders_1?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
    username: root
    password: root

rules:
  - !SHARDING
    tables:
      cmp_contract:
        actualDataNodes: ds0.cmp_contract_$->{[0,2,4,6]},ds1.cmp_contract_$->{[1,3,5,7]}
     
        databaseStrategy:
          standard:
            shardingColumn: ID
            shardingAlgorithmName: database_inline
      
        tableStrategy:
          standard:
            shardingColumn: ID
            shardingAlgorithmName: table_inline
    
    
    shardingAlgorithms:
      database_inline:
        type: INLINE
        props:
          algorithm-expression: ds${ID % 2}
      table_inline:
        type: INLINE
        props:
          algorithm-expression: cmp_contract_${ID % 8}