You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/12/15 07:37:53 UTC
[GitHub] [shardingsphere] huanghao495430759 opened a new issue #8625: Does replica-query data-source-name must be same as primary-data-source-name ?
huanghao495430759 opened a new issue #8625:
URL: https://github.com/apache/shardingsphere/issues/8625
### 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.
![image](https://user-images.githubusercontent.com/34728144/102185040-6785f100-3eeb-11eb-9cd8-d01f9953ef5a.png)
![image](https://user-images.githubusercontent.com/34728144/102185046-694fb480-3eeb-11eb-9819-bcd16b4fa2bf.png)
### 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).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] huanghao495430759 commented on issue #8625: Does replica-query data-source-name must be same as primary-data-source-name ?
Posted by GitBox <gi...@apache.org>.
huanghao495430759 commented on issue #8625:
URL: https://github.com/apache/shardingsphere/issues/8625#issuecomment-745764028
> 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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #8625: Does replica-query data-source-name must be same as primary-data-source-name ?
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #8625:
URL: https://github.com/apache/shardingsphere/issues/8625#issuecomment-745731536
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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] huanghao495430759 closed issue #8625: Does replica-query data-source-name must be same as primary-data-source-name ?
Posted by GitBox <gi...@apache.org>.
huanghao495430759 closed issue #8625:
URL: https://github.com/apache/shardingsphere/issues/8625
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org