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/11/26 04:43:53 UTC
[GitHub] [shardingsphere] peilinqian opened a new issue, #22432: When the federation function is enabled, an exception occurs when tpcc is executed.
peilinqian opened a new issue, #22432:
URL: https://github.com/apache/shardingsphere/issues/22432
### Which version of ShardingSphere did you use?
we find java version: java8, full_version=1.8.0_342, full_path=/home/peilq_sharding/bisheng-jdk1.8.0_342//bin/java
ShardingSphere-5.2.2-SNAPSHOT
Commit ID: dirty-753c0cee8ee6fd3db00536da55b64bc5198a3758
Commit Message: Optimize sqlFederationExecutor init logic when sqlFederationType modify dynamically (https://github.com/apache/shardingsphere/pull/22209)
Branch: https://github.com/apache/shardingsphere/commit/753c0cee8ee6fd3db00536da55b64bc5198a3758
Build time: 2022-11-19T10:18:41+0800
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
When the federation function is enabled, tpcc is executed successfully.
### Actual behavior
When the federation function is enabled, an exception occurs when tpcc is executed.
```
12:23:11,457 [main] INFO jTPCC : Term-00, 12:23:13,754 [Thread-5] ERROR jTPCCTData : Unexpected SQLException in STOCK_LEVELge: 308MB / 1963MB
12:23:13,754 [Thread-5] ERROR jTPCCTData : [90.90.44.173:32424/90.90.44.175:11000] ERROR: no vertex rel#3096:HepRelVertex(rel#3092:LogicalFilter.NONE.[](input=HepRelVertex#3058,condition=AND(=($0, ?0), <($2, ?1))))
org.opengauss.util.PSQLException: [90.90.44.173:32424/90.90.44.175:11000] ERROR: no vertex rel#3096:HepRelVertex(rel#3092:LogicalFilter.NONE.[](input=HepRelVertex#3058,condition=AND(=($0, ?0), <($2, ?1))))
at org.opengauss.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2901)
at org.opengauss.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2630)
at org.opengauss.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:362)
at org.opengauss.jdbc.PgStatement.runQueryExecutor(PgStatement.java:562)
at org.opengauss.jdbc.PgStatement.executeInternal(PgStatement.java:539)
at org.opengauss.jdbc.PgStatement.execute(PgStatement.java:397)
at org.opengauss.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:156)
at org.opengauss.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:112)
at jTPCCTData.executeStockLevel(jTPCCTData.java:1279)
at jTPCCTData.execute(jTPCCTData.java:101)
at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:172)
at jTPCCTerminal.run(jTPCCTerminal.java:87)
at java.lang.Thread.run(Thread.java:748)
12:23:13,769 [Thread-9] ERROR jTPCCTData : Unexpected SQLException in STOCK_LEVEL
12:23:13,769 [Thread-9] ERROR jTPCCTData : [90.90.44.173:32492/90.90.44.175:11000] ERROR: java.lang.AssertionError: Relational expression rel#3088:LogicalAggregate.NONE.[](input=LogicalJoin#3097,group={},low_stock=COUNT()) belongs to a different planner than is currently being used.
org.opengauss.util.PSQLException: [90.90.44.173:32492/90.90.44.175:11000] ERROR: java.lang.AssertionError: Relational expression rel#3088:LogicalAggregate.NONE.[](input=LogicalJoin#3097,group={},low_stock=COUNT()) belongs to a different planner than is currently being used.
at org.opengauss.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2901)
at org.opengauss.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2630)
at org.opengauss.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:362)
at org.opengauss.jdbc.PgStatement.runQueryExecutor(PgStatement.java:562)
at org.opengauss.jdbc.PgStatement.executeInternal(PgStatement.java:539)
at org.opengauss.jdbc.PgStatement.execute(PgStatement.java:397)
at org.opengauss.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:156)
at org.opengauss.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:112)
at jTPCCTData.executeStockLevel(jTPCCTData.java:1279)
at jTPCCTData.execute(jTPCCTData.java:101)
at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:172)
at jTPCCTerminal.run(jTPCCTerminal.java:87)
at java.lang.Thread.run(Thread.java:748)
```
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
gsql -d tpccdb -p 11000 -h 90.90.44.175 -U sharding -W sharding -r
gsql ((openGauss 3.0.0 build cad66d01) compiled at 2022-08-31 20:15:45 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
tpccdb=> set dist variable sql_federation_type="NONE";
SUCCESS
tpccdb=> set dist variable sql_federation_type="ADVANCED";
SUCCESS
step1：set dist variable sql_federation_type="NONE"; and then “. runBenchmark.sh props.proxy” --successfully
step2：set dist variable sql_federation_type="ADVANCED"; and then “. runBenchmark.sh props.proxy” --failure
### Example codes for reproduce this issue (such as a github link).
```
schemaName: tpcc_db
dataSources:
ds_0:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 260
minPoolSize: 10
password: Test@123
url: jdbc:opengauss://90.90.44.171:14000/tpccdb?batchMode=on
username: tpccuser
ds_1:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 260
minPoolSize: 10
password: Test@123
url: jdbc:opengauss://90.90.44.171:15000/tpccdb?batchMode=on
username: tpccuser
rules:
- !SHARDING
bindingTables:
- bmsql_warehouse, bmsql_customer
- bmsql_stock, bmsql_district, bmsql_order_line
defaultDatabaseStrategy:
standard:
shardingAlgorithmName: database_inline
shardingColumn: ds_id
defaultTableStrategy:
none: null
shardingAlgorithms:
database_inline:
props:
algorithm-expression: ds_${ds_id % 1}
type: INLINE
ds_bmsql_item_inline:
props:
algorithm-expression: ds_${i_id % 2}
type: INLINE
ds_bmsql_customer_inline:
props:
algorithm-expression: ds_${c_w_id % 2}
type: INLINE
ds_bmsql_district_inline:
props:
algorithm-expression: ds_${d_w_id % 2}
type: INLINE
ds_bmsql_history_inline:
props:
algorithm-expression: ds_${h_w_id % 2}
type: INLINE
ds_bmsql_new_order_inline:
props:
algorithm-expression: ds_${no_w_id % 2}
type: INLINE
ds_bmsql_oorder_inline:
props:
algorithm-expression: ds_${o_w_id % 2}
type: INLINE
ds_bmsql_order_line_inline:
props:
algorithm-expression: ds_${ol_w_id % 2}
type: INLINE
ds_bmsql_stock_inline:
props:
algorithm-expression: ds_${s_w_id % 2}
type: INLINE
ds_bmsql_warehouse_inline:
props:
algorithm-expression: ds_${w_id % 2}
type: INLINE
tables:
bmsql_config:
actualDataNodes: ds_0.bmsql_config
bmsql_item:
actualDataNodes: ds_${0..1}.bmsql_item
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_item_inline
shardingColumn: i_id
bmsql_customer:
actualDataNodes: ds_${0..1}.bmsql_customer
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_customer_inline
shardingColumn: c_w_id
bmsql_district:
actualDataNodes: ds_${0..1}.bmsql_district
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_district_inline
shardingColumn: d_w_id
bmsql_history:
actualDataNodes: ds_${0..1}.bmsql_history
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_history_inline
shardingColumn: h_w_id
bmsql_new_order:
actualDataNodes: ds_${0..1}.bmsql_new_order
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_new_order_inline
shardingColumn: no_w_id
bmsql_oorder:
actualDataNodes: ds_${0..1}.bmsql_oorder
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_oorder_inline
shardingColumn: o_w_id
bmsql_order_line:
actualDataNodes: ds_${0..1}.bmsql_order_line
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_order_line_inline
shardingColumn: ol_w_id
bmsql_stock:
actualDataNodes: ds_${0..1}.bmsql_stock
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_stock_inline
shardingColumn: s_w_id
bmsql_warehouse:
actualDataNodes: ds_${0..1}.bmsql_warehouse
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_warehouse_inline
shardingColumn: w_id
```
--
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] tuichenchuxin closed issue #22432: When the federation function is enabled, an exception occurs when tpcc is executed.
Posted by GitBox <gi...@apache.org>.
tuichenchuxin closed issue #22432: When the federation function is enabled, an exception occurs when tpcc is executed.
URL: https://github.com/apache/shardingsphere/issues/22432
--
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 #22432: When the federation function is enabled, an exception occurs when tpcc is executed.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22432:
URL: https://github.com/apache/shardingsphere/issues/22432#issuecomment-1327982587
Can you provide the original sql which cause this 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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] peilinqian commented on issue #22432: When the federation function is enabled, an exception occurs when tpcc is executed.
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22432:
URL: https://github.com/apache/shardingsphere/issues/22432#issuecomment-1327991773
> Can you provide the original sql which cause this exception?
Log doesn't contain error SQL information。
**benchmark version：**
https://gitee.com/opengauss_sharding/benchmarksql
--
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 #22432: When the federation function is enabled, an exception occurs when tpcc is executed.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22432:
URL: https://github.com/apache/shardingsphere/issues/22432#issuecomment-1342311864
```sql
// PreparedStatements for STOCK_LEVEL
switch (dbType)
{
case jTPCCConfig.DB_POSTGRES:
stmtStockLevelSelectLow = dbConn.prepareStatement(
"SELECT count(*) AS low_stock FROM (" +
" SELECT s_w_id, s_i_id, s_quantity " +
" FROM bmsql_stock " +
" WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN (" +
" SELECT ol_i_id " +
" FROM bmsql_district " +
" JOIN bmsql_order_line ON ol_w_id = d_w_id " +
" AND ol_d_id = d_id " +
" AND ol_o_id >= d_next_o_id - 20 " +
" AND ol_o_id < d_next_o_id " +
" WHERE d_w_id = ? AND d_id = ? " +
" ) " +
" ) AS L");
break;
default:
stmtStockLevelSelectLow = dbConn.prepareStatement(
"SELECT count(*) AS low_stock FROM (" +
" SELECT s_w_id, s_i_id, s_quantity " +
" FROM bmsql_stock " +
" WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN (" +
" SELECT ol_i_id " +
" FROM bmsql_district " +
" JOIN bmsql_order_line ON ol_w_id = d_w_id " +
" AND ol_d_id = d_id " +
" AND ol_o_id >= d_next_o_id - 20 " +
" AND ol_o_id < d_next_o_id " +
" WHERE d_w_id = ? AND d_id = ? " +
" ) " +
" )");
break;
}
```
--
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 #22432: When the federation function is enabled, an exception occurs when tpcc is executed.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22432:
URL: https://github.com/apache/shardingsphere/issues/22432#issuecomment-1343735628
Latest result after optimizing sql decider logic:
```sql
sharding_db=> SELECT count(*) AS low_stock FROM (
SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 1 AND s_quantity < 100 AND s_i_id IN (
SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 1 AND d_id = 1
)
) AS L;sharding_db(> sharding_db(> sharding_db(> sharding_db(>
low_stock
-----------
0
(1 row)
[INFO ] 2022-12-09 09:57:41.875 [Connection-3-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT count(*) AS low_stock FROM (
SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 1 AND s_quantity < 100 AND s_i_id IN (
SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 1 AND d_id = 1
)
) AS L;
[INFO ] 2022-12-09 09:57:41.875 [Connection-3-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: SELECT count(*) AS low_stock FROM (
SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 1 AND s_quantity < 100 AND s_i_id IN (
SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 1 AND d_id = 1
)
) AS L;
```
--
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] wsm12138 commented on issue #22432: When the federation function is enabled, an exception occurs when tpcc is executed.
Posted by GitBox <gi...@apache.org>.
wsm12138 commented on issue #22432:
URL: https://github.com/apache/shardingsphere/issues/22432#issuecomment-1343999877
<img width="1755" alt="image" src="https://user-images.githubusercontent.com/86462784/206658220-74b2d3ab-c344-4b99-89a7-25a9e4739744.png">
fix by 631fdf40f87223e176abe5c851a51b3287b4d6de
--
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] peilinqian commented on issue #22432: When the federation function is enabled, an exception occurs when tpcc is executed.
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22432:
URL: https://github.com/apache/shardingsphere/issues/22432#issuecomment-1327992152
> > Can you provide the original sql which cause this exception?
> > Log doesn't contain error SQL information。
> > **benchmark version：**
> > https://gitee.com/opengauss_sharding/benchmarksql
there is another error ，I have add information under ‘Actual behavior ’
--
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] peilinqian commented on issue #22432: When the federation function is enabled, an exception occurs when tpcc is executed.
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22432:
URL: https://github.com/apache/shardingsphere/issues/22432#issuecomment-1327992114
>
there is another error ，I have add information under ‘**Actual behavior** ’
--
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