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/06 15:57:38 UTC
[GitHub] [shardingsphere] VaeTang opened a new issue #8513: Please help to check whether it is a bug or a configuration error?
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:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #8513: Please help to check whether it is a bug or a configuration error?
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #8513:
URL: https://github.com/apache/shardingsphere/issues/8513#issuecomment-739649399
Hi Which SQL causes the following exception?
> [Err] 10002 - 2Unknown exception: [Can not route tables for [PROFILING], please make sure the tables are in same schema.]
Given the log you provided, I guess `` ran well, didn't it?
```
[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
```
----------------------------------------------------------------
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 edited a comment on issue #8513: Please help to check whether it is a bug or a configuration error?
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #8513:
URL: https://github.com/apache/shardingsphere/issues/8513#issuecomment-740365248
> All the log information is there
Where is the full log? Especially the fore-and-aft around `[Err] 10002 - 2Unknown exception: `?
From that exception, I guess there was a query on `PROFILING`, and I am curious which query it is.
----------------------------------------------------------------
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] VaeTang commented on issue #8513: Please help to check whether it is a bug or a configuration error?
Posted by GitBox <gi...@apache.org>.
VaeTang commented on issue #8513:
URL: https://github.com/apache/shardingsphere/issues/8513#issuecomment-739658477
**Execute SQL in Navicat:**
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
**Navicat has error messages:**
[Err] 10002 - 2Unknown exception: [Can not route tables for [PROFILING], please make sure the tables are in same schema.]
**All the log information is there**
----------------------------------------------------------------
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] terrymanu commented on issue #8513: Please help to check whether it is a bug or a configuration error?
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #8513:
URL: https://github.com/apache/shardingsphere/issues/8513#issuecomment-749898167
Closed because of no response
----------------------------------------------------------------
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] terrymanu closed issue #8513: Please help to check whether it is a bug or a configuration error?
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #8513:
URL: https://github.com/apache/shardingsphere/issues/8513
----------------------------------------------------------------
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 edited a comment on issue #8513: Please help to check whether it is a bug or a configuration error?
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #8513:
URL: https://github.com/apache/shardingsphere/issues/8513#issuecomment-739649399
Hi Which SQL causes the following exception?
> [Err] 10002 - 2Unknown exception: [Can not route tables for [PROFILING], please make sure the tables are in same schema.]
Given the log you provided, I guess `SET PROFILING=1` ran well, didn't it?
```
[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
```
----------------------------------------------------------------
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 #8513: Please help to check whether it is a bug or a configuration error?
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #8513:
URL: https://github.com/apache/shardingsphere/issues/8513#issuecomment-740365248
> All the log information is there
Where is the full log? Especially the fore-and-aft around `[Err] 10002 - 2Unknown exception: `?
----------------------------------------------------------------
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