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 2022/10/31 15:55:37 UTC
[GitHub] [shardingsphere] jlovej opened a new issue, #21874: Failed to force a route
jlovej opened a new issue, #21874:
URL: https://github.com/apache/shardingsphere/issues/21874
Service Description: The sharding Key is not a database field, but needs to be divided according to the business logic, so it needs to use a custom forced sharding algorithm
Environment configuration: springboot+dubbo+ss5.1.2+seata
Problem description: according to the official documentation, implementation HintShardingAlgorithm interfaces, and configuration of the services, at the same time according to the official documentation, where calls using HintManager. SetDatabaseShardingValue to set the shard
Related configuration:
![a7cb313955f540f4906a7ca0acad1172](https://user-images.githubusercontent.com/8819998/199049707-b6f0ad28-d2a4-4baa-9e97-cef7ef95e098.png)
services config
![c1182f854aac47ebbc461e443ebae962](https://user-images.githubusercontent.com/8819998/199049968-06483f77-df1a-4f3e-83bd-f7619887fb50.png)
Relevant code:
Customize the sharding algorithm
![6a3cd19289e64eb69f9425b8f2d0c5f5](https://user-images.githubusercontent.com/8819998/199050368-8131482a-0bec-4f97-bca5-435141e638d6.png)
![6046ebf3336b4c7cb61993161e494294](https://user-images.githubusercontent.com/8819998/199050708-e420db79-95b3-4672-9ba3-1b36e719192b.png)
Logs are executed after the function is enabled:
![82e873f8e9e341da8cfe47c9855243ea](https://user-images.githubusercontent.com/8819998/199051208-e22ddaac-1daa-497a-bdb8-2b2acae85ed2.png)
In theory, ds-hlm data source should be used,But we're actually going to ds-0:
![Uploading 8e5e9f638856492d9e0b4693242252df.png…]()
May I ask which configurations are wrong or missing? Please check and look forward to your reply
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jlovej commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
jlovej commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1297950905
> > https://user-images.githubusercontent.com/8819998/199050368-8131482a-0bec-4f97-bca5-435141e638d6.png
>
> Sorry, I didn't see you using `hintShardingValue` in the doSharding method?
It has not entered the custom routing algorithm all the time, so the value 'hintShardingValue' is not used
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jlovej commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
jlovej commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1299822036
> @jlovej You need to understand what each configuration you write means
>
> <img alt="image" width="844" src="https://user-images.githubusercontent.com/5668787/199412874-05261578-b0a4-456d-a298-35cd11326022.png">
>
> 1. Logic table name should be the same with what in your SQL statement. (sys_shop_user)
> 2. Where is ds-${0..3}? You just configured `ds-0, ds-hla, ds-hlm, ds-ncl`
> 3. ShardingAlgorithmName is a refference of one of `sharding-algorithms`, may be `database-tenant` or `database-default`.
>
> Here is another example of `CLASS_BASED` sharding algorithm: https://github.com/apache/shardingsphere/blob/master/examples/shardingsphere-jdbc-example/single-feature-example/extension-example/custom-sharding-algortihm-example/class-based-sharding-algorithm-example/class-based-sharding-raw-jdbc-example/src/main/resources/META-INF/sharding-databases.yaml
>
> If you still need help, please paste the text configuration, and think about how the person helping you will edit the picture.
Sorry, the front two points have been dealt with. The screenshot has some problems and is misleading to you. I still don't understand the third point. But there is a new progress. I will only keep the 'actualDataNodes' configuration under' tables'. After removing all the others, after setting 'DatabaseSharedingValue' in the business code, you can enter doSharing to confirm whether you need to set each table? In addition, setting 'DatabaseShardingValue' needs to be coupled to business code. Besides using AOP, are there any other official solutions (with business code)?
The configuration file is as follows:
```
spring:
shardingsphere:
props:
sql-show: true
datasource:
names: ds-0,ds-1,ds-2,ds-3
ds-0:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3306/hl_workbench_master?useUnicode=true&characterEncoding=utf8&useOldAliasMetadataBehavior=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useInformationSchema=false&serverTimezone=UTC
username: root
password: abc123
ds-1:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3306/hl_workbench_a?useUnicode=true&characterEncoding=utf8&useOldAliasMetadataBehavior=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useInformationSchema=false&serverTimezone=UTC
username: root
password: abc123
ds-2:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3306/hl_workbench_b?useUnicode=true&characterEncoding=utf8&useOldAliasMetadataBehavior=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useInformationSchema=false&serverTimezone=UTC
username: root
password: abc123
ds-3:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3306/hl_workbench_c?useUnicode=true&characterEncoding=utf8&useOldAliasMetadataBehavior=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useInformationSchema=false&serverTimezone=UTC
username: root
password: abc123
rules:
sharding:
tables:
sys_shop_user:
actualDataNodes: ds-$->{0..3}.sys_shop_user
shop:
actualDataNodes: ds-$->{0..3}.shop
shardingAlgorithms:
database-tenant:
type: CLASS_BASED
props:
strategy: HINT
algorithmClassName: com.heilan.shop.workbench.common.algorithm.TenantDatabaseShardingAlgorithm
defaultDatabaseStrategy:
hint:
sharding-algorithm-name: database-tenant
defaultTableStrategy:
none:
enabled: true
```
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1298045114
I found that your `Logic SQL` is select from table `sys_shop_user`, but there is no table named `sys_shop_user` in your sharding configuration.
<img width="787" alt="image" src="https://user-images.githubusercontent.com/5668787/199164584-d3682de9-b097-49ed-9237-cc8c8b4cdc77.png">
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1297918416
> https://user-images.githubusercontent.com/8819998/199050368-8131482a-0bec-4f97-bca5-435141e638d6.png
Sorry, I didn't see you using `hintShardingValue` in the doSharding method?
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jlovej commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
jlovej commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1298612319
> @jlovej You may need to understand how to configure sharding tables (including sharding databases only) in SharidngSphere, rather than customization from the beginning.
>
> > The sharding table must be configured explicitly
>
> Here is an example: https://github.com/apache/shardingsphere/blob/master/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/resources/META-INF/sharding-databases.yaml
Explicitly configuring the sharding table rules still doesn't work
![05053475d6904201817c8270dd001549](https://user-images.githubusercontent.com/8819998/199261022-e06fed08-884c-4898-8e99-c9c56917f2b6.png)
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jlovej commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
jlovej commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1302281544
> Every sharding table should be configured, such as
>
> ```yaml
> rules:
> sharding:
> tables:
> sys_shop_user:
> actualDataNodes: ds-$->{0..3}.sys_shop_user
> shop:
> actualDataNodes: ds-$->{0..3}.shop
> t_a:
> actualDataNodes: xx
> t_b:
> actualDataNodes: xx
> ...
> ```
>
> If a table has no rule configuration, it is a single table.
Thanks
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1299465426
> Are logical tables and physical tables exactly the same?
They can be different.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1299464664
Logical table name is what in your SQL, actual table names must match your physical tables in storage.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jlovej commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
jlovej commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1300642890
> @jlovej The new configuration seems to be working, congratulations!
>
> Yes, if you are using ShardingSphere-JDBC, you need to set hint value through HintManager, no other choice.
Whether each table should be configured, and whether there are other ways not to configure all tables
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1298105057
@jlovej
You may need to understand how to configure sharding tables (including sharding databases only) in SharidngSphere, rather than customization from the beginning.
> The sharding table must be configured explicitly
Here is an example: https://github.com/apache/shardingsphere/blob/master/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/resources/META-INF/sharding-databases.yaml
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1299644788
@jlovej You need to understand what each configuration you write means
<img width="844" alt="image" src="https://user-images.githubusercontent.com/5668787/199412874-05261578-b0a4-456d-a298-35cd11326022.png">
1. Logic table name should be the same with what in your SQL statement. (sys_shop_user)
2. Where is ds-${0..3}? You just configured `ds-0, ds-hla, ds-hlm, ds-ncl`
3. ShardingAlgorithmName is a refference of one of `sharding-algorithms`, may be `database-tenant` or `database-default`.
Here is another example of `CLASS_BASED` sharding algorithm:
https://github.com/apache/shardingsphere/blob/master/examples/shardingsphere-jdbc-example/single-feature-example/extension-example/custom-sharding-algortihm-example/class-based-sharding-algorithm-example/class-based-sharding-raw-jdbc-example/src/main/resources/META-INF/sharding-databases.yaml
If you still need help, please paste the text configuration, and think about how the person helping you will edit the picture.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1299458094
`sys_shop_user` and `sys-shop-user` are different
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang closed issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
RaigorJiang closed issue #21874: Failed to force a route
URL: https://github.com/apache/shardingsphere/issues/21874
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1302861698
Close because it has been resolved.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jlovej commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
jlovej commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1298080036
> I found that your `Logic SQL` is select from table `sys_shop_user`, but there is no table named `sys_shop_user` in your sharding configuration. <img alt="image" width="787" src="https://user-images.githubusercontent.com/5668787/199164584-d3682de9-b097-49ed-9237-cc8c8b4cdc77.png">
I only divide libraries and not tables, so the configuration of the 'table' part has no effect, and the custom route of the hint does not take effect after I remove the configuration of the 'table' part
![d59030a328ab4f85863850d75439a307](https://user-images.githubusercontent.com/8819998/199171560-f4149bb3-d3e2-4407-8263-9faf1583346f.png)
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1300594879
@jlovej
The new configuration seems to be working, congratulations!
Yes, if you are using ShardingSphere-JDBC, you need to set hint value through HintManager, no other choice.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jlovej commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
jlovej commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1299470501
> > Are logical tables and physical tables exactly the same?
>
> They can be different.
It's convenient to add WeChat or staple something? Is it more efficient?
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jlovej commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
jlovej commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1299468272
> > Are logical tables and physical tables exactly the same?
>
> They can be different.
So, what's wrong with my rule configuration?
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jlovej commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
jlovej commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1299461403
> `sys_shop_user` and `sys-shop-user` are different
Do they have to be exactly the same?
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21874: Failed to force a route
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21874:
URL: https://github.com/apache/shardingsphere/issues/21874#issuecomment-1300731201
Every sharding table should be configured, such as
```yaml
rules:
sharding:
tables:
sys_shop_user:
actualDataNodes: ds-$->{0..3}.sys_shop_user
shop:
actualDataNodes: ds-$->{0..3}.shop
t_a:
actualDataNodes: xx
t_b:
actualDataNodes: xx
...
```
If a table has no rule configuration, it is a single table.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org