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 06:30:36 UTC
[GitHub] [shardingsphere] wsm12138 opened a new issue, #20077: The same scene，create view sometimes success，sometimes failed.
wsm12138 opened a new issue, #20077:
URL: https://github.com/apache/shardingsphere/issues/20077
## Bug Report
### Which version of ShardingSphere did you use?
```
ShardingSphere-5.1.3-SNAPSHOT
Commit ID: 9dd0d3990c849d50c17c6dc7c92ec2d4ce0ad7e5
```
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
Nomatter drop ds0's table or ds1's table，when proxy execute same sql. the result should be a same one.
### Actual behavior
Drop ds0's table , proxy execute create view, create success.
Drop ds1's table , proxy execute create view, create failed.
### Reason analyze (If you can)
Maybe we need DDL transaction to deal with it.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
```
databaseName: sharding_db
#
dataSources:
ds_0:
url: jdbc:opengauss://127.0.0.1:15432/demo_ds_0?batchMode=on
username: gaussdb
password: Secretpassword@123
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:opengauss://127.0.0.1:15432/demo_ds_1?batchMode=on
username: gaussdb
password: Secretpassword@123
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
t_order_view:
actualDataNodes: ds_${0..1}.t_order_view_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_view_inline
t_order_view2:
actualDataNodes: ds_${0..1}.t_order_view2_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_view2_inline
t_item:
actualDataNodes: ds_${0..1}.t_item_${0..1}
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: t_item_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
bindingTables:
- t_order,t_order_view,t_order_view2
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
t_order_inline:
type: INLINE
props:
algorithm-expression: t_order_${order_id % 2}
t_order_view_inline:
type: INLINE
props:
algorithm-expression: t_order_view_${order_id % 2}
t_order_view2_inline:
type: INLINE
props:
algorithm-expression: t_order_view2_${order_id % 2}
t_item_inline:
type: INLINE
props:
algorithm-expression: t_item_${id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKE
```
```
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: governance_ds
server-lists: localhost:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
overwrite: true
rules:
- !AUTHORITY
users:
- root@%:root
- sharding@:sharding
provider:
type: ALL_PERMITTED
props:
```
##### case 1
- proxy create sharding table
CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
- connect db
demo_ds_1=> drop table t_order_1;
- proxy create view
- no error，but we found demo_ds_1 's t_order_view_1 don't create sucess
sharding_db=> CREATE VIEW t_order_view as select order_id, status from t_order where order_id >= 1000;
CREATE VIEW
- have error，other view deleted
sharding_db=> drop view t_order_view;
ERROR: view "t_order_view_1" does not exist
sharding_db=> drop table t_order;
ERROR: table "t_order_1" does not exist
##### case2
- proxy create sharding table
REFRESH TABLE METADATA;
CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
- connect db
demo_ds_0=> drop table t_order_0;
- proxy create view
- have error，in fact create view failed
sharding_db=> CREATE VIEW t_order_view as select order_id, status from t_order where order_id >= 1000;
ERROR: relation "t_order_0" does not exist on gaussdb
LINE 1: ...EW t_order_view as select order_id, status from t_order wher...
### Example codes for reproduce this issue (such as a github link).
--
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 commented on issue #20077: The same scene，create view sometimes success，sometimes failed.
Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #20077:
URL: https://github.com/apache/shardingsphere/issues/20077#issuecomment-1211706164
This case may be supported if we have gloable DDL transaction.
--
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] github-actions[bot] closed issue #20077: The same scene，create view sometimes success，sometimes failed.
Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #20077: The same scene，create view sometimes success，sometimes failed.
URL: https://github.com/apache/shardingsphere/issues/20077
--
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] github-actions[bot] commented on issue #20077: The same scene，create view sometimes success，sometimes failed.
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #20077:
URL: https://github.com/apache/shardingsphere/issues/20077#issuecomment-1272348917
Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.
--
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] tuichenchuxin commented on issue #20077: The same scene，create view sometimes success，sometimes failed.
Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #20077:
URL: https://github.com/apache/shardingsphere/issues/20077#issuecomment-1211704404
I investigate this issue.
We execute with parallel threads. When drop ds_0's table. Then actual create view is two threads. ds_0 is main thread. ds_1 is executed by asynchronous thread.
So if the main thread execution fast, then ds1's connection will be recycled, ds_1 won't execute successfully. But if main thread execute slow, Then ds_1 will be executed.
So in this case, whether or not ds_1 executes successfully is uncertain.
When we drop ds_1's table. Then main thread can always executed correctly.
--
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