VaeTang opened a new issue #8513:
URL: https://github.com/apache/shardingsphere/issues/8513
When I use the ShardingSphere Proxy, Found that each query to print the
following log:
SET PROFILING=1;
SHOW STATUS;
And right after parsing the routing,a full route query is performed on the
logical table
version: shardingsphere-5.0.0-alpha ，MySQL5.7.22-log，Navicat Premium11.0.17
**server.yaml**
```
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: bpm_data_schema
props:
sql-show: true
sql-simple: flase
acceptor-size: 16
executor-size: 16
max-connections-size-per-query: 1
check-table-metadata-enabled: false
query-with-cipher-column: false
proxy-frontend-flush-threshold: 128
proxy-transaction-type: LOCAL
proxy-opentracing-enabled: false
proxy-hint-enabled: false
```
**config-sharding.yaml**
```
schemaName: bpm_data_schema
dataSourceCommon:
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
maintenanceIntervalMilliseconds: 30000
dataSources:
bpm_data_1:
url:
jdbc:mysql://localhost:3306/bpm_data?autoReconnect=true&useAffectedRows=true&useUnicode=true&characterEncoding=utf8&useSSL=false
bpm_data_2:
url:
jdbc:mysql://localhost:3306/bpm_data_1?autoReconnect=true&useAffectedRows=true&useUnicode=true&characterEncoding=utf8&useSSL=false
rules:
- !SHARDING
tables:
t_user:
actualDataNodes: bpm_data_${[1, 2]}.t_user_$->{(new
Date().parse('yyyyMMdd', '20201130')..<new Date().parse('yyyyMMdd',
'20201203')).collect{e -> return e.format('yyyyMMdd')}}
databaseStrategy:
standard:
shardingColumn: center_id
shardingAlgorithmName: database_interval
tableStrategy:
standard:
shardingColumn: end_time
shardingAlgorithmName: table_interval
shardingAlgorithms:
database_interval:
type: INLINE
props:
algorithm-expression: bpm_data_${center_id}
allow-range-query-with-inline-sharding: true
table_interval:
type: INTERVAL
props:
datetime-pattern: yyyy-MM-dd HH:mm:ss
datetime-lower: "2020-11-01 00:00:00"
#datetime-upper: yyyy-MM-dd HH:mm:ss
sharding-suffix-pattern: yyyyMMdd
datetime-interval-unit: DAYS
```
**For example, I execute SQL:**
select * from t_user t where t.center_id in (1,2) and t.end_time >=
'2020-11-30 00:00:00' and t.end_time < '2020-11-30 23:59:59' ORDER BY
user_total LIMIT 10
**Log information:**
[INFO ] 23:45:25.746 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic
SQL: SET PROFILING=1;
[INFO ] 23:45:25.746 [ShardingSphere-Command-0] ShardingSphere-SQL -
SQLStatement: MySQLSetStatement()
[INFO ] 23:45:25.746 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual
SQL: bpm_data_1 ::: SET PROFILING=1;
[INFO ] 23:45:25.746 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual
SQL: bpm_data_2 ::: SET PROFILING=1;
[INFO ] 23:45:25.749 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic
SQL: SHOW STATUS
[INFO ] 23:45:25.749 [ShardingSphere-Command-1] ShardingSphere-SQL -
SQLStatement: MySQLShowOtherStatement()
[INFO ] 23:45:25.749 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual
SQL: bpm_data_2 ::: SHOW STATUS
[INFO ] 23:45:25.758 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic
SQL: SHOW STATUS
[INFO ] 23:45:25.758 [ShardingSphere-Command-2] ShardingSphere-SQL -
SQLStatement: MySQLShowOtherStatement()
[INFO ] 23:45:25.758 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual
SQL: bpm_data_1 ::: SHOW STATUS
[INFO ] 23:45:25.773 [ShardingSphere-Command-3] ShardingSphere-SQL - Logic
SQL: select * from t_user t where t.center_id in (1,2) and t.end_time >=
'2020-11-30 00:00:00' and t.end_time < '2020-11-30 23:59:59' ORDER BY
user_total LIMIT 10
[INFO ] 23:45:25.773 [ShardingSphere-Command-3] ShardingSphere-SQL -
SQLStatement:
MySQLSelectStatement(limit=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment@650cac69],
lock=Optional.empty)
[INFO ] 23:45:25.773 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual
SQL: bpm_data_1 ::: select * from t_user_20201130 t where t.center_id in (1,2)
and t.end_time >= '2020-11-30 00:00:00' and t.end_time < '2020-11-30 23:59:59'
ORDER BY user_total LIMIT 10
[INFO ] 23:45:25.773 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual
SQL: bpm_data_2 ::: select * from t_user_20201130 t where t.center_id in (1,2)
and t.end_time >= '2020-11-30 00:00:00' and t.end_time < '2020-11-30 23:59:59'
ORDER BY user_total LIMIT 10
[INFO ] 23:45:27.830 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic
SQL: SHOW STATUS
[INFO ] 23:45:27.830 [ShardingSphere-Command-4] ShardingSphere-SQL -
SQLStatement: MySQLShowOtherStatement()
[INFO ] 23:45:27.830 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual
SQL: bpm_data_1 ::: SHOW STATUS
[INFO ] 23:45:27.884 [ShardingSphere-Command-6] ShardingSphere-SQL - Logic
SQL: SELECT * FROM `bpm_data_schema`.`t_user` LIMIT 0
[INFO ] 23:45:27.884 [ShardingSphere-Command-6] ShardingSphere-SQL -
SQLStatement:
MySQLSelectStatement(limit=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment@73328751],
lock=Optional.empty)
[INFO ] 23:45:27.884 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual
SQL: bpm_data_1 ::: SELECT * FROM `t_user_20201130` LIMIT 0
[INFO ] 23:45:27.884 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual
SQL: bpm_data_1 ::: SELECT * FROM `t_user_20201201` LIMIT 0
[INFO ] 23:45:27.884 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual
SQL: bpm_data_1 ::: SELECT * FROM `t_user_20201202` LIMIT 0
[INFO ] 23:45:27.884 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual
SQL: bpm_data_2 ::: SELECT * FROM `t_user_20201130` LIMIT 0
[INFO ] 23:45:27.884 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual
SQL: bpm_data_2 ::: SELECT * FROM `t_user_20201201` LIMIT 0
[INFO ] 23:45:27.884 [ShardingSphere-Command-6] ShardingSphere-SQL - Actual
SQL: bpm_data_2 ::: SELECT * FROM `t_user_20201202` LIMIT 0
[INFO ] 23:45:27.888 [ShardingSphere-Command-7] ShardingSphere-SQL - Logic
SQL: SHOW COLUMNS FROM `bpm_data_schema`.`t_user`
[INFO ] 23:45:27.888 [ShardingSphere-Command-7] ShardingSphere-SQL -
SQLStatement:
MySQLShowColumnsStatement(table=SimpleTableSegment(tableName=TableNameSegment(startIndex=18,
stopIndex=43, identifier=IdentifierValue(value=t_user,
quoteCharacter=BACK_QUOTE)),
owner=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.generic.OwnerSegment@7dbb0e1],
alias=Optional.empty), fromSchema=Optional.empty)
[INFO ] 23:45:27.889 [ShardingSphere-Command-7] ShardingSphere-SQL - Actual
SQL: bpm_data_1 ::: SHOW COLUMNS FROM `t_user_20201130`
**Navicat has error messages:**
[Err] 10002 - 2Unknown exception: [Can not route tables for `[PROFILING]`,
please make sure the tables are in same schema.]
----------------------------------------------------------------
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:
[email protected]
