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/10 03:50:33 UTC
[GitHub] [shardingsphere] lanrenbulan opened a new issue, #21437: SQL comment hint not work in proxy
lanrenbulan opened a new issue, #21437:
URL: https://github.com/apache/shardingsphere/issues/21437
## Question
SQL comment hint not work in proxy, it queries all databases.
## ENV
proxy-5.2.0
windows11
## config
service.yaml
```
mode:
type: Standalone
rules:
- !AUTHORITY
users:
- root@%:root
provider:
type: ALL_PRIVILEGES_PERMITTED
- !SQL_PARSER
sqlCommentParseEnabled: true
sqlStatementCache:
initialCapacity: 2000
maximumSize: 65535
parseTreeCache:
initialCapacity: 128
maximumSize: 1024
props:
max-connections-size-per-query: 1
kernel-executor-size: 16 # Infinite by default.
proxy-frontend-flush-threshold: 128 # The default value is 128.
proxy-opentracing-enabled: false
proxy-hint-enabled: true
sql-show: true
check-table-metadata-enabled: false
```
config.sharding.yaml
```
databaseName: tenant_db
dataSources:
tenant_db_1:
url: jdbc:mysql://localhost/tenant_db_1
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
tenant_db_2:
url: jdbc:mysql://localhost/tenant_db_2
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
user:
actualDataNodes: tenant_db_${1..2}.user
tableStrategy:
hint:
defaultDatabaseStrategy:
none:
defaultTableStrategy:
none:
```
## log
The port is configured as 3309
The classpath is "..;..\lib\*;..\ext-lib\*"
we find java version: java17, full_version=17.0.3"
Starting the ShardingSphere-Proxy ...
Java HotSpot(TM) 64-Bit Server VM warning: JVM cannot use large page memory because it does not have enough privilege to lock pages in memory.
Thanks for using Atomikos! This installation is not registered yet.
REGISTER FOR FREE at http://www.atomikos.com/Main/RegisterYourDownload and receive:
- tips & advice
- working demos
- access to the full documentation
- special exclusive bonus offers not available to others
- everything you need to get the most out of using Atomikos!
[INFO ] 2022-10-10 11:46:58.201 [main] o.a.s.d.p.c.l.PipelineContextManagerLifecycleListener - mode type is not Cluster, mode type='Standalone', ignore
[INFO ] 2022-10-10 11:46:58.239 [main] o.a.s.p.v.ShardingSphereProxyVersion - Database name is `MySQL`, version is `5.7.36`, database name is `tenant_db`
[INFO ] 2022-10-10 11:46:58.830 [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy Standalone mode started successfully
[INFO ] 2022-10-10 11:47:00.634 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: /* ShardingSphere hint: dataSourceName=tenant_db_1 */ SELECT * FROM user;
[INFO ] 2022-10-10 11:47:00.634 [Connection-1-ThreadExecutor] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[CommentSegment(text=/* ShardingSphere hint: dataSourceName=tenant_db_1 */, startIndex=0, stopIndex=52)]), projections=ProjectionsSegment(startIndex=61, stopIndex=61, projections=[ShorthandProjectionSegment(startIndex=61, stopIndex=61, owner=Optional.empty, alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=68, stopIndex=71, identifier=IdentifierValue(value=user, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional.empty, groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-10-10 11:47:00.636 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: tenant_db_1 ::: /* ShardingSphere hint: dataSourceName=tenant_db_1 */ SELECT * FROM user;
[INFO ] 2022-10-10 11:47:00.636 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: tenant_db_2 ::: /* ShardingSphere hint: dataSourceName=tenant_db_1 */ SELECT * FROM user;
--
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] RaigorJiang closed issue #21437: SQL comment hint not work in proxy
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang closed issue #21437: SQL comment hint not work in proxy
URL: https://github.com/apache/shardingsphere/issues/21437
--
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] strongduanmu commented on issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21437:
URL: https://github.com/apache/shardingsphere/issues/21437#issuecomment-1288531448
You can refer ShardingSphere 5.2.1 release document——https://mp.weixin.qq.com/s/pv7c9oG6uTAgUzTpkfMhag.
--
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] lanrenbulan commented on issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
lanrenbulan commented on issue #21437:
URL: https://github.com/apache/shardingsphere/issues/21437#issuecomment-1288347395
> > Hi @dongzl, can you help check this issue?
>
> Hi @strongduanmu I will check it.
How about it, can it be reproduced?
--
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] lanrenbulan commented on issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
lanrenbulan commented on issue #21437:
URL: https://github.com/apache/shardingsphere/issues/21437#issuecomment-1283324693
@strongduanmu I tested proxy5.2.1, but it still doesn't work. It queries all.
I tested proxy5.2.1, but it still doesn't work. It uses the first source.
```
rules:
- !SHARDING
tables:
user:
actualDataNodes: tenant_db_${1..2}.user
```
[INFO ] 2022-10-19 10:40:19.102 [Connection-3-ThreadExecutor] ShardingSphere-SQL - Actual SQL: tenant_db_1 ::: /* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=2 */select * FROM user;
[INFO ] 2022-10-19 10:40:19.102 [Connection-3-ThreadExecutor] ShardingSphere-SQL - Actual SQL: tenant_db_2 ::: /* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=2 */select * FROM user;
--
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] lanrenbulan closed issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
lanrenbulan closed issue #21437: SQL comment hint not work in proxy
URL: https://github.com/apache/shardingsphere/issues/21437
--
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] dongzl commented on issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
dongzl commented on issue #21437:
URL: https://github.com/apache/shardingsphere/issues/21437#issuecomment-1285047209
> Hi @dongzl, can you help check this issue?
Hi @strongduanmu I will check 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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21437:
URL: https://github.com/apache/shardingsphere/issues/21437#issuecomment-1284739484
Hi @dongzl, can you help check this issue?
--
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] strongduanmu commented on issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21437:
URL: https://github.com/apache/shardingsphere/issues/21437#issuecomment-1288369969
@lanrenbulan Can you add hint sharding algorithm?
--
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] lanrenbulan closed issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
lanrenbulan closed issue #21437: SQL comment hint not work in proxy
URL: https://github.com/apache/shardingsphere/issues/21437
--
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] lanrenbulan commented on issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
lanrenbulan commented on issue #21437:
URL: https://github.com/apache/shardingsphere/issues/21437#issuecomment-1288523470
> algorithm
I don't know how to add it. Do I need to add it to implement mandatory routing by SQL annotation? PHP I use.
--
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] strongduanmu commented on issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21437:
URL: https://github.com/apache/shardingsphere/issues/21437#issuecomment-1273012622
Hi @lanrenbulan, this feature has been removed since 5.2.0. In next version, we will provide force sharding with sql hint.
--
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 #21437: SQL comment hint not work in proxy
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #21437:
URL: https://github.com/apache/shardingsphere/issues/21437#issuecomment-1407418431
Closed due to no response.
If this problem persists, please reopen it or submit a new one.
--
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] lanrenbulan commented on issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
lanrenbulan commented on issue #21437:
URL: https://github.com/apache/shardingsphere/issues/21437#issuecomment-1273954814
> Hi @lanrenbulan, this feature has been removed since 5.2.0. In next version, we will provide force sharding with sql hint.
DistSQL set sharding hint database_ Does value also not support 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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] dongzl commented on issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
dongzl commented on issue #21437:
URL: https://github.com/apache/shardingsphere/issues/21437#issuecomment-1285047618
> Hi @dongzl, can you help check this issue?
Hi @strongduanmu I will check 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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] dongzl commented on issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
dongzl commented on issue #21437:
URL: https://github.com/apache/shardingsphere/issues/21437#issuecomment-1292876148
> > You can refer ShardingSphere 5.2.1 release document——https://mp.weixin.qq.com/s/pv7c9oG6uTAgUzTpkfMhag.
>
> @strongduanmu
>
> I have read this article and can basically use it. In the Apache document, I do not know the usage of the ${value} variable. In addition, I need to follow the rules:
>
> 1. Value is a number
> 2. Table must be defined in the rule?
>
> At present, I only need to define databases but not tables (tables can also be used). There are hundreds of tables that need to be defined?
>
> ```
> rules:
> - !SHARDING
> // ** When I do not define tables, it always use the first data source **
> #tables:
> # user:
> # actualDataNodes: tenant_db_${1..2}.user
> defaultDatabaseStrategy:
> hint:
> shardingAlgorithmName: database_hint_inline
>
> shardingAlgorithms:
> database_hint_inline:
> type: HINT_INLINE
> props:
> algorithm-expression: tenant_db_${value}
> ```
Hi @lanrenbulan , `shardingsphere-jdbc` or `shardingsphere-proxy`?
--
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] lanrenbulan commented on issue #21437: SQL comment hint not work in proxy
Posted by GitBox <gi...@apache.org>.
lanrenbulan commented on issue #21437:
URL: https://github.com/apache/shardingsphere/issues/21437#issuecomment-1288609896
> You can refer ShardingSphere 5.2.1 release document——https://mp.weixin.qq.com/s/pv7c9oG6uTAgUzTpkfMhag.
I have read this article and can basically use it. In the Apache document, I do not know the usage of the ${value} variable. In addition, I need to follow the rules:
1. Value is a number
2. Table must be defined in the rule?
At present, I only need to define databases but not tables (tables can also be used). There are hundreds of tables that need to be defined?
```
rules:
- !SHARDING
// ** When I do not define tables, it always use the first data source **
#tables:
# user:
# actualDataNodes: tenant_db_${1..2}.user
defaultDatabaseStrategy:
hint:
shardingAlgorithmName: database_hint_inline
shardingAlgorithms:
database_hint_inline:
type: HINT_INLINE
props:
algorithm-expression: tenant_db_${value}
```
--
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