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/12/01 13:40:35 UTC
[GitHub] [shardingsphere] peilinqian opened a new issue, #22575: In the federation scenario, the view information in zookeeper exists duplicate name even the view has already dropped.
peilinqian opened a new issue, #22575:
URL: https://github.com/apache/shardingsphere/issues/22575
### 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
In the federation scenario, the view information in zookeeper exists duplicate name even the view has already dropped.
### Actual behavior
In the federation scenario, the view information in zookeeper exists duplicate name even the view has already dropped.
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
```
-- step1.1：相同表 union 测试；expect：查询结果正确
create view select_view as select * from t_order where order_id > 2900 union select * from t_order where order_id > 2500 order by order_id ;
-- 查询视图
select * from select_view limit 3, 5;
-- 删除视图
drop view select_view;
-- step1.2：相同表 union all测试；expect：查询结果正确
create view select_view as select * from t_order where order_id > 2900 union all select * from t_order where order_id > 2500 order by order_id ;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step1.3：相同表 union distinct 测试；expect：查询结果正确
create view select_view as select * from t_order where order_id > 2900 union distinct select * from t_order where order_id > 2500 order by order_id ;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step1.4：相同表 （）union 测试；expect：查询结果正确
create view select_view as (select * from t_order where order_id > 2900 ) union (select * from t_order where order_id > 2500 order by order_id ) order by 1,2;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step1.5：多个分片表 union 测试；expect：查询结果正确
create view select_view as select o.order_id, o.user_id from t_order o where o.order_id > 2500 union select i.order_id, i.user_id from t_order_item i where i.order_id > 2500 order by order_id limit 5, 5;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step1.6：分片表、单表 union all 测试；expect：查询结果正确
create view select_view as select o.user_id from t_order o where o.order_id > 2500 union all select u.user_id from t_user u order by user_id limit 5, 5;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step2.1：相同表 intersect all 测试；expect：查询结果正确
create view select_view as select * from t_order intersect all select * from t_order order by order_id limit 5, 5;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step2.2：相同表 intersect 测试；expect：查询结果正确
create view select_view as select * from t_order where order_id > 2000 intersect select * from t_order where order_id > 1500 order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step2.3：多个分片表 intersect all 测试；expect：查询结果正确
create view select_view as select o.order_id, o.user_id from t_order o where o.order_id > 2500 intersect all select i.order_id, i.user_id from t_order_item i where i.order_id > 2400 order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step2.4：多个分片表 intersect 测试；expect：查询结果正确
create view select_view as select o.order_id, o.user_id from t_order o where o.order_id > 2500 intersect select i.order_id, i.user_id from t_order_item i where i.order_id > 2400 order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step2.5：多个分片表 多个intersect 测试；expect：查询结果正确
create view select_view as select o.order_id, o.user_id from t_order o where o.order_id > 2500 intersect select i.order_id, i.user_id from t_order_item i where i.order_id > 2400 intersect select i.order_id, i.user_id from t_order_item i where i.order_id < 2700 order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step2.6：分片表、单表 intersect 测试；expect：查询结果正确
create view select_view as select o.user_id from t_order o where o.order_id > 2500 intersect select u.user_id from t_user u order by user_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step3.1：相同表 except all 测试；expect：查询结果正确
create view select_view as select * from t_order except all select * from t_order where order_id > 1500 order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step3.2：相同表 except 测试；expect：查询结果正确
create view select_view as select * from t_order where order_id > 1500 except select * from t_order where order_id > 2000 order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step3.3：多个分片表 except all 测试；expect：查询结果正确
create view select_view as select o.order_id, o.user_id from t_order o where o.order_id > 2500 except all select i.order_id, i.user_id from t_order_item i where i.order_id > 2000 order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step3.4：多个分片表 except 测试；expect：查询结果正确
create view select_view as select o.order_id, o.user_id from t_order o where o.order_id > 2600 except select i.order_id, i.user_id from t_order_item i where i.order_id > 2500 order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step3.5：分片表、单表 except all 测试；expect：查询结果正确
create view select_view as select o.user_id from t_order o where o.order_id > 2500 except all select u.user_id from t_user u order by user_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step3.5：分片表、单表 except 测试；expect：查询结果正确
create view select_view as select o.user_id from t_order o where o.order_id > 1500 except select u.user_id from t_user u where user_id >5 order by user_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step4.1：相同表 minus all 测试；expect：查询结果正确
create view select_view as select * from t_order where order_id > 1500 minus all select * from t_order where order_id > 1600 order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step4.2：相同表 minus 测试；expect：查询结果正确
create view select_view as select * from t_order where order_id > 2000 minus select * from t_order where order_id > 1500 order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step4.3：多个分片表 minus all 测试；expect：查询结果正确
create view select_view as select o.order_id, o.user_id from t_order o where o.order_id > 1500 minus all select i.order_id, i.user_id from t_order_item i where i.order_id > 1500 order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step4.4：多个分片表 minus 测试；expect：查询结果正确
create view select_view as select o.order_id, o.user_id from t_order o where o.order_id > 1500 minus select i.order_id, i.user_id from t_order_item i where i.order_id > 1500 order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step4.5：分片表、单表 minus all 测试；expect：查询结果正确
create view select_view as select o.user_id from t_order o where o.order_id > 2500 minus all select u.user_id from t_user u where u.user_id <29 order by user_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step4.6：分片表、单表 minus 测试；expect：查询结果正确
create view select_view as select o.user_id from t_order o where o.order_id > 2500 minus select u.user_id from t_user u where u.user_id <29 order by user_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step4.6：分片表、单表 minus distinct 测试；expect：查询结果正确
create view select_view as select o.user_id from t_order o where o.order_id > 2500 minus distinct select u.user_id from t_user u where u.user_id <29 order by user_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
-- step5.1：相同表 UNION/INTERSECT/EXCEPT/MINUS组合优先级测试验证；expect：查询结果正确
create view select_view as (select * from t_order where order_id = 1500 union select * from t_order where order_id = 1800 )INTERSECT select * from t_order where status ='finish' order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
create view select_view as select * from t_order where order_id = 1500 union (select * from t_order where order_id = 1800 INTERSECT select * from t_order where status ='finish' order by order_id);
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
create view select_view as select * from t_order where order_id = 1500 union select * from t_order where order_id = 1800 INTERSECT select * from t_order where status ='finish' order by order_id;
-- 查询视图
select * from select_view;
-- 删除视图
drop view select_view;
```
### Example codes for reproduce this issue (such as a github link).
```
drop table if exists t_order;
create table t_order (order_id int primary key, user_id int not null, status varchar(50) not null, merchant_id int not null, remark varchar(50), creation_date date);
insert into t_order values(1000, 10, 'init', 1, 'test', '2017-07-08');
insert into t_order values(1001, 10, 'init', 2, 'test', '2017-07-08');
insert into t_order values(2000, 20, 'init', 3, 'test', '2017-08-08');
insert into t_order values(2001, 20, 'init', 4, 'test', '2017-08-08');
insert into t_order values(1100, 11, 'init', 5, 'test', '2017-08-08');
insert into t_order values(1101, 11, 'init', 6, 'test', '2017-08-08');
insert into t_order values(2100, 21, 'finish', 7, 'test', '2017-08-08');
insert into t_order values(2101, 21, 'finish', 8, 'test', '2017-08-08');
insert into t_order values(1200, 12, 'finish', 9, 'finish', '2017-08-08');
insert into t_order values(1201, 12, 'finish', 10, 'test22', '2017-08-18');
insert into t_order values(2200, 22, 'finish', 11, 'test', '2017-08-18');
insert into t_order values(2201, 22, 'finish', 12, 'test', '2017-08-18');
insert into t_order values(1300, 13, 'finish', 13, '', '2017-08-18');
insert into t_order values(1301, 13, 'finish', 14, 'test', '2017-08-18');
insert into t_order values(2300, 23, 'finish ', 15, 'test', '2017-08-18');
insert into t_order values(2301, 23, 'finish', 16, 'test', '2017-08-18');
insert into t_order values(1400, 14, 'init', 17, '', '2017-08-18');
insert into t_order values(1401, 14, 'init', 18, 'test', '2017-08-18');
insert into t_order values(2400, 24, 'init', 19, 'test', '2017-08-18');
insert into t_order values(2401, 24, 'init', 20, 'test', '2017-08-18');
insert into t_order values(1500, 15, 'init', 1, '', '2017-08-28');
insert into t_order values(1501, 15, 'init', 2, 'test', '2017-08-28');
insert into t_order values(2500, 25, 'init', 3, 'test', '2017-08-28');
insert into t_order values(2501, 25, 'init', 4, 'test', '2017-08-28');
insert into t_order values(1600, 16, 'init', 5, 'test', '2017-08-28');
insert into t_order values(1601, 16, 'init', 6, '', '2017-08-28');
insert into t_order values(2600, 26, 'init', 7, 'test', '2017-08-28');
insert into t_order values(2601, 26, 'init', 8);
insert into t_order values(1700, 17, 'init', 9, 'test', '2017-08-28');
insert into t_order values(1701, 17, 'finish', 10, 'test', '2017-08-18');
insert into t_order values(2700, 27, 'finish', 11, 'test', '2017-08-18');
insert into t_order values(2701, 27, 'finish', 12, 'test', '2017-08-18');
insert into t_order values(1800, 18, 'finish', 13, 'test', '2017-08-18');
insert into t_order values(1801, 18, 'finish', 14);
insert into t_order values(2800, 28, 'finish', 15, 'test', '2017-08-18');
insert into t_order values(2801, 28, 'finish', 16, 'test', '2017-08-18');
insert into t_order values(1900, 19, 'init', 17, 'test', '2017-08-18');
insert into t_order values(1901, 19, 'init', 18, 'test', '2017-08-18');
insert into t_order values(2900, 29, 'init', 19, 'test', '2017-08-18');
insert into t_order values(2901, 29, 'init', 20, 'test', '2017-08-18');
insert into t_order values(1902, 19, 'init', 17, 'test11', '2017-08-18');
insert into t_order values(1903, 19, 'init', 18, 'test12', '2017-08-18');
insert into t_order values(2902, 29, 'init', 19, 'test', '2017-08-18');
insert into t_order values(2903, 29, 'init', 20, 'test', '2017-08-18');
```
```
schemaName: test_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/test_db?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/test_db?batchMode=on
username: tpccuser
rules:
- !SHARDING
tables:
t_user:
actualDataNodes: ds_0.t_user
t_product:
actualDataNodes: ds_0.t_product
t_merchant:
actualDataNodes: ds_1.t_merchant
t_product_detail:
actualDataNodes: ds_1.t_product_detail
t_order:
actualDataNodes: ds_${0..1}.t_order
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
t_order_item1:
actualDataNodes: ds_${0..1}.t_order_item1
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
t_new_order:
actualDataNodes: ds_${0..1}.t_new_order_${0..1}
databaseStrategy:
standard:
shardingAlgorithmName: database_inline
shardingColumn: user_id
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: table_inline
bindingTables:
- t_order,t_order_item
broadcastTables:
- t_product_category
- t_country
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
allow-range-query-with-inline-sharding: true
table_inline:
type: INLINE
props:
algorithm-expression: t_new_order_${order_id % 2}
allow-range-query-with-inline-sharding: true
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: governance_ds
server-lists: 7.212.123.28:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
authority:
users:
- user: root@%
password: root
- user: sharding
password: sharding
privilege:
type: ALL_PERMITTED
rules:
- !TRANSACTION
defaultType: XA
providerType: Atomikos
props:
sql-show: true
```
--
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] peilinqian closed issue #22575: In the federation scenario, the view information in zookeeper exists duplicate name even the view has already dropped.
Posted by GitBox <gi...@apache.org>.
peilinqian closed issue #22575: In the federation scenario, the view information in zookeeper exists duplicate name even the view has already dropped.
URL: https://github.com/apache/shardingsphere/issues/22575
--
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 #22575: In the federation scenario, the view information in zookeeper exists duplicate name even the view has already dropped.
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22575:
URL: https://github.com/apache/shardingsphere/issues/22575#issuecomment-1333793816
--
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 #22575: In the federation scenario, the view information in zookeeper exists duplicate name even the view has already dropped.
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22575:
URL: https://github.com/apache/shardingsphere/issues/22575#issuecomment-1345983834
This is a display problem of prettyzoo tool .
--
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] zhaojinchao95 commented on issue #22575: In the federation scenario, the view information in zookeeper exists duplicate name even the view has already dropped.
Posted by GitBox <gi...@apache.org>.
zhaojinchao95 commented on issue #22575:
URL: https://github.com/apache/shardingsphere/issues/22575#issuecomment-1334737547
> create view select_view as select * from t_order where order_id > 2900 union select * from t_order where order_id > 2500 order by order_id ;
Sure. i will check
--
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 #22575: In the federation scenario, the view information in zookeeper exists duplicate name even the view has already dropped.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22575:
URL: https://github.com/apache/shardingsphere/issues/22575#issuecomment-1334702115
Hi @zhaojinchao95, 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] zhaojinchao95 commented on issue #22575: In the federation scenario, the view information in zookeeper exists duplicate name even the view has already dropped.
Posted by GitBox <gi...@apache.org>.
zhaojinchao95 commented on issue #22575:
URL: https://github.com/apache/shardingsphere/issues/22575#issuecomment-1334737914
> Hi @zhaojinchao95, can you help check this issue?
Sure, i will check
--
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] zhaojinchao95 commented on issue #22575: In the federation scenario, the view information in zookeeper exists duplicate name even the view has already dropped.
Posted by GitBox <gi...@apache.org>.
zhaojinchao95 commented on issue #22575:
URL: https://github.com/apache/shardingsphere/issues/22575#issuecomment-1338930972
@strongduanmu @peilinqian Hi, i tested this issue and successfully reproduced it.
<img width="567" alt="image" src="https://user-images.githubusercontent.com/33742097/205854179-adbb276f-260a-488e-aae0-22003242d50b.png">
But, i think this is not `ShardingSphere` bug. Maybe it's `prettyZoo` Client problem. When i refresh it, the tables and views node are back to normal.
<img width="584" alt="image" src="https://user-images.githubusercontent.com/33742097/205854771-c64d0a61-aab7-42c0-a709-48ad4ca4d163.png">
<img width="1012" alt="image" src="https://user-images.githubusercontent.com/33742097/205854830-e5a8a202-5cf8-4a55-96bb-e82bf3a472e0.png">
<img width="332" alt="image" src="https://user-images.githubusercontent.com/33742097/205854869-bc7e61b2-1c8a-4a42-aad4-2f66a83eb175.png">
--
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 #22575: In the federation scenario, the view information in zookeeper exists duplicate name even the view has already dropped.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22575:
URL: https://github.com/apache/shardingsphere/issues/22575#issuecomment-1335089033
This phenomenon is caused by the concurrent operation of metadata. Since there is currently no lock and consistency guarantee, please avoid concurrent execution of DDL statements.
--
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