Does replica-query data-source-name must be same as primary-data-source-name ?

[huanghao495430759]

Which version of ShardingSphere did you use?

5.0.0-RC1-SNAPSHOT I use spring-boot starter

    <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
      <version>5.0.0-RC1-SNAPSHOT</version>
    </dependency>

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

ShardingSphere-JDBC

Expected behavior

my query-sql[select id,user_id,name from user where user_id=1] executed in replica datasource.

Actual behavior

my query-sql[select id,user_id,name from user where user_id=1] executed in primary datasource.

Reason analyze (If you can)

When debug into ReplicaQuerySQLRouter.decorateRouteContext(), it can not find replica datasource, so executed in primary datasource.

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

user logic table SQL script:

CREATE TABLE `user_0` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `user_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

SQL to execute:

select id,user_id,name from user where user_id=1

sharding rule configuration:

spring:
  shardingsphere:
    datasource:
      names: node1,node1-replica0
      node1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/node1?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=GMT%2B8
        username: root
        password: A!12345678
        platform: mysql
        continue-on-error: true
        initialSize: 5
        minIdle: 1
        maxActive: 50
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: false
        filters: stat,wall
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      node1-replica0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/node1_s0?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=GMT%2B8
        username: root
        password: A!12345678
        platform: mysql
        continue-on-error: true
        initialSize: 5
        minIdle: 1
        maxActive: 50
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: false
        filters: stat,wall
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    rules:
      sharding:
        tables:
          user:
            key-generate-strategy:
              key-generator-name: SNOWFLAKE
              column: id
            actual-data-nodes: node1.user_$->{0..1}
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: user-inline
        sharding-algorithms:
          user-inline:
            type: INLINE
            props:
              algorithm-expression: user_$->{user_id % 2}
      replica-query:
        data-sources:
          node1-replica:
            primary-data-source-name: node1
            replica-data-source-names: node1-replica0
            load-balancer-name: RANDOM
        load-balancers:
          RANDOM:
            type: RANDOM
    props:
      sql-show: true

mybatis:
  mapper-locations: classpath:mapping/*.xml
  type-aliases-package: XXXXXXXX

the result exetuted sql:

2020-12-15 15:21:30.610  INFO 51308 --- [           main] ShardingSphere-SQL                       : Logic SQL: select id,user_id,name from user where user_id= ?;
2020-12-15 15:21:30.610  INFO 51308 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty)
2020-12-15 15:21:30.610  INFO 51308 --- [           main] ShardingSphere-SQL                       : Actual SQL: node1 ::: select id,user_id,name from user_1 where user_id= ?; ::: [1]
[]

after I rename replica-query.data-sources name same as primary data-source-name[node1] :

    replica-query:
        data-sources:
          node1:
            primary-data-source-name: node1
            replica-data-source-names: node1-replica0
            load-balancer-name: RANDOM
        load-balancers:
          RANDOM:
            type: RANDOM

the result is true.

2020-12-15 15:25:08.544  INFO 16200 --- [           main] ShardingSphere-SQL                       : Logic SQL: select id,user_id,name from user where user_id= ?;
2020-12-15 15:25:08.545  INFO 16200 --- [           main] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty)
2020-12-15 15:25:08.545  INFO 16200 --- [           main] ShardingSphere-SQL                       : Actual SQL: node1-replica0 ::: select id,user_id,name from user_1 where user_id= ?; ::: [1]

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

[tristaZero]

Hi @huanghao495430759 ,

If you use sharding and replica feature together, it is necessary to configure sharding Rule with replica-query data-source-name. The reason is that sharding rule will regard replica-query dataName as a regular dataName without caring about query executed on replica or primary.

[huanghao495430759]

Hi @huanghao495430759 ,

If you use sharding and replica feature together, it is necessary to configure sharding Rule with replica-query data-source-name. The reason is that sharding rule will regard replica-query dataName as a regular dataName without caring about query executed on replica or primary.

Thanks ! I got it.