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 11:03:42 UTC
[GitHub] [shardingsphere] peilinqian opened a new issue, #22569: In the federation scenario, after creating a view, and select the view, the result is "ERROR: relation "select_view" does not exist on dn_6001_6002_6003".
peilinqian opened a new issue, #22569:
URL: https://github.com/apache/shardingsphere/issues/22569
### 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, after creating a view, and select the view, the result is "ERROR: relation "select_view" does not exist on dn_6001_6002_6003".
### Actual behavior
In the federation scenario, after creating a view, and select the view, the result is correct .
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
step 3.1 excute with error
```
-- step1：all
create view select_view as select all * from t_order where order_id>2800;
select * from select_view;
drop view select_view;
-- step2.1：distinct
create view select_view as select distinct(user_id) from t_order order by user_id;
select * from select_view;
drop view select_view;
-- step2.2：distinct on
create view select_view as select distinct on (user_id) * from t_order order by user_id,order_id;
select * from select_view;
drop view select_view;
-- step3.1：limit
create view select_view as select o.order_id,o.user_id,status,remark,merchant_name from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 2 limit 10;
select * from select_view;
drop view select_view;
```
```
[INFO ] 2022-12-01 18:45:04.377 [Connection-305-ThreadExecutor] ShardingSphere-SQL - SQLStatement: OpenGaussDropViewStatement(super=DropViewStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), views=[SimpleTableSegment(tableName=TableNameSegment(startIndex=10, stopIndex=20, identifier=IdentifierValue(value=select_view, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty)]))
[INFO ] 2022-12-01 18:45:04.377 [Connection-305-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: drop view select_view
[INFO ] 2022-12-01 18:45:04.392 [Connection-305-ThreadExecutor] ShardingSphere-SQL - Logic SQL: create view select_view as select o.order_id,o.user_id,status,remark,merchant_name from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 2 limit 10
[INFO ] 2022-12-01 18:45:04.392 [Connection-305-ThreadExecutor] ShardingSphere-SQL - SQLStatement: OpenGaussCreateViewStatement(super=CreateViewStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), view=SimpleTableSegment(tableName=TableNameSegment(startIndex=12, stopIndex=22, identifier=IdentifierValue(value=select_view, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), viewDefinition=select o.order_id,o.user_id,status,remark,merchant_name from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 2 limit 10, select=OpenGaussSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=34, stopIndex=81, projections=[ColumnProjectionSegment(column=ColumnSegment(startIndex=34, stopIndex=43, identifier=IdentifierValue(value=order_id, quoteChar
acter=NONE), owner=Optional[OwnerSegment(startIndex=34, stopIndex=34, identifier=IdentifierValue(value=o, quoteCharacter=NONE), owner=Optional.empty)]), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=45, stopIndex=53, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=45, stopIndex=45, identifier=IdentifierValue(value=o, quoteCharacter=NONE), owner=Optional.empty)]), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=55, stopIndex=60, identifier=IdentifierValue(value=status, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=62, stopIndex=67, identifier=IdentifierValue(value=remark, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=69, stopIndex=81, identifier=IdentifierValue(value=merchant_name, quoteCharacter=NONE), owner=Opti
onal.empty), alias=Optional.empty)], distinctRow=false), from=JoinTableSegment(startIndex=88, stopIndex=98, alias=Optional.empty, left=SimpleTableSegment(tableName=TableNameSegment(startIndex=88, stopIndex=98, identifier=IdentifierValue(value=t_new_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional[o]), joinType=INNER, right=SimpleTableSegment(tableName=TableNameSegment(startIndex=113, stopIndex=122, identifier=IdentifierValue(value=t_merchant, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional[m]), condition=null, using=[ColumnSegment(startIndex=132, stopIndex=142, identifier=IdentifierValue(value=merchant_id, quoteCharacter=NONE), owner=Optional.empty)]), where=Optional[WhereSegment(startIndex=145, stopIndex=183, expr=BinaryOperationExpression(startIndex=151, stopIndex=183, left=BinaryOperationExpression(startIndex=151, stopIndex=164, left=ColumnSegment(startIndex=151, stopIndex=159, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Opt
ional[OwnerSegment(startIndex=151, stopIndex=151, identifier=IdentifierValue(value=o, quoteCharacter=NONE), owner=Optional.empty)]), right=LiteralExpressionSegment(startIndex=163, stopIndex=164, literals=10), operator=>, text=o.user_id > 10), right=BinaryOperationExpression(startIndex=170, stopIndex=183, left=ColumnSegment(startIndex=170, stopIndex=178, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=170, stopIndex=170, identifier=IdentifierValue(value=o, quoteCharacter=NONE), owner=Optional.empty)]), right=LiteralExpressionSegment(startIndex=182, stopIndex=183, literals=15), operator=<, text=o.user_id < 15), operator=and, text=o.user_id > 10 and o.user_id < 15))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional[OrderBySegment(startIndex=185, stopIndex=206, orderByItems=[ColumnOrderByItemSegment(super=TextOrderByItemSegment(), column=ColumnSegment(startIndex=194, stopIndex=203, identifier=IdentifierValue(value=or
der_id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=194, stopIndex=194, identifier=IdentifierValue(value=o, quoteCharacter=NONE), owner=Optional.empty)])), IndexOrderByItemSegment(super=OrderByItemSegment(startIndex=206, stopIndex=206, orderDirection=ASC, nullOrderDirection=ASC), columnIndex=2)])], combine=Optional.empty), limit=Optional[LimitSegment(startIndex=208, stopIndex=215, offset=Optional.empty, rowCount=Optional[NumberLiteralLimitValueSegment(super=LimitValueSegment(startIndex=214, stopIndex=215), value=10)])], lock=Optional.empty, window=Optional.empty)))
[INFO ] 2022-12-01 18:45:04.392 [Connection-305-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: create view select_view as select o.order_id,o.user_id,status,remark,merchant_name from t_new_order_0 o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 2 limit 10
[INFO ] 2022-12-01 18:45:04.406 [HikariPool-2 connection adder] o.o.core.v3.ConnectionFactoryImpl - [5df32809-1401-46a8-9cf9-1699c6065ab1] Try to connect. IP: 10.29.180.204:16000
[INFO ] 2022-12-01 18:45:04.452 [HikariPool-2 connection adder] o.o.core.v3.ConnectionFactoryImpl - [7.212.123.28:35872/10.29.180.204:16000] Connection is established. ID: 5df32809-1401-46a8-9cf9-1699c6065ab1
[INFO ] 2022-12-01 18:45:04.458 [HikariPool-2 connection adder] o.o.core.v3.ConnectionFactoryImpl - Connect complete. ID: 5df32809-1401-46a8-9cf9-1699c6065ab1
[INFO ] 2022-12-01 18:45:05.308 [Connection-305-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select * from select_view
[INFO ] 2022-12-01 18:45:05.308 [Connection-305-ThreadExecutor] ShardingSphere-SQL - SQLStatement: OpenGaussSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=7, projections=[ShorthandProjectionSegment(startIndex=7, stopIndex=7, owner=Optional.empty, alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=14, stopIndex=24, identifier=IdentifierValue(value=select_view, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional.empty, groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combine=Optional.empty), limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-12-01 18:45:05.308 [Connection-305-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from select_view
[ERROR] 2022-12-01 18:45:05.316 [Connection-305-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
org.opengauss.util.PSQLException: [7.212.123.28:53188/10.29.180.204:15000] ERROR: relation "select_view" does not exist on dn_6001_6002_6003
Position: 15
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.execute(PgPreparedStatement.java:145)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyPreparedStatementExecutorCallback.execute(ProxyPreparedStatementExecutorCallback.java:43)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:75)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:68)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:45)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:90)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:69)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:135)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:131)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:116)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:67)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:75)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:229)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.doExecute(ProxySQLExecutor.java:186)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:151)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:131)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.JDBCPortal.bind(JDBCPortal.java:101)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.bind.PostgreSQLComBindExecutor.execute(PostgreSQLComBindExecutor.java:53)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.PostgreSQLAggregatedCommandExecutor.execute(PostgreSQLAggregatedCommandExecutor.java:41)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:111)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:78)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
```
### Example codes for reproduce this issue (such as a github link).
```
--create table
drop table if exists t_new_order;
drop table if exists t_merchant;
create table t_new_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);
create table t_merchant (merchant_id int primary key, country_id int not null, merchant_name varchar(50) not null, business_code varchar(50) not null, telephone varchar(50) not null, creation_date date not null);
-- t_new_order
insert into t_new_order values(1000, 10, 'init', 1, 'test', '2017-07-08');
insert into t_new_order values(1001, 10, 'init', 2, 'test', '2017-07-08');
insert into t_new_order values(2000, 20, 'init', 3, 'test', '2017-08-08');
insert into t_new_order values(2001, 20, 'init', 4, 'test', '2017-08-08');
insert into t_new_order values(1100, 11, 'init', 5, 'test', '2017-08-08');
insert into t_new_order values(1101, 11, 'init', 6, 'test', '2017-08-08');
insert into t_new_order values(2100, 21, 'finish', 7, 'test', '2017-08-08');
insert into t_new_order values(2101, 21, 'finish', 8, 'test', '2017-08-08');
insert into t_new_order values(1200, 12, 'finish', 9, 'finish', '2017-08-08');
insert into t_new_order values(1201, 12, 'finish', 10, 'test22', '2017-08-18');
insert into t_new_order values(2200, 22, 'finish', 11, 'test', '2017-08-18');
insert into t_new_order values(2201, 22, 'finish', 12, 'test', '2017-08-18');
insert into t_new_order values(1300, 13, 'finish', 13, '', '2017-08-18');
insert into t_new_order values(1301, 13, 'finish', 14, 'test', '2017-08-18');
insert into t_new_order values(2300, 23, 'finish ', 15, 'test', '2017-08-18');
insert into t_new_order values(2301, 23, 'finish', 16, 'test', '2017-08-18');
insert into t_new_order values(1400, 14, 'init', 17, '', '2017-08-18');
insert into t_new_order values(1401, 14, 'init', 18, 'test', '2017-08-18');
insert into t_new_order values(2400, 24, 'init', 19, 'test', '2017-08-18');
insert into t_new_order values(2401, 24, 'init', 20, 'test', '2017-08-18');
insert into t_new_order values(1500, 15, 'init', 1, '', '2017-08-28');
insert into t_new_order values(1501, 15, 'init', 2, 'test', '2017-08-28');
insert into t_new_order values(2500, 25, 'init', 3, 'test', '2017-08-28');
insert into t_new_order values(2501, 25, 'init', 4, 'test', '2017-08-28');
insert into t_new_order values(1600, 16, 'init', 5, 'test', '2017-08-28');
insert into t_new_order values(1601, 16, 'init', 6, '', '2017-08-28');
insert into t_new_order values(2600, 26, 'init', 7, 'test', '2017-08-28');
insert into t_new_order values(2601, 26, 'init', 8);
insert into t_new_order values(1700, 17, 'init', 9, 'test', '2017-08-28');
insert into t_new_order values(1701, 17, 'finish', 10, 'test', '2017-08-18');
insert into t_new_order values(2700, 27, 'finish', 11, 'test', '2017-08-18');
insert into t_new_order values(2701, 27, 'finish', 12, 'test', '2017-08-18');
insert into t_new_order values(1800, 18, 'finish', 13, 'test', '2017-08-18');
insert into t_new_order values(1801, 18, 'finish', 14);
insert into t_new_order values(2800, 28, 'finish', 15, 'test', '2017-08-18');
insert into t_new_order values(2801, 28, 'finish', 16, 'test', '2017-08-18');
insert into t_new_order values(1900, 19, 'init', 17, 'test', '2017-08-18');
insert into t_new_order values(1901, 19, 'init', 18, 'test', '2017-08-18');
insert into t_new_order values(2900, 29, 'init', 19, 'test', '2017-08-18');
insert into t_new_order values(2901, 29, 'init', 20, 'test', '2017-08-18');
insert into t_new_order values(1902, 19, 'init', 17, 'test11', '2017-08-18');
insert into t_new_order values(1903, 19, 'init', 18, 'test12', '2017-08-18');
insert into t_new_order values(2902, 29, 'init', 19, 'test', '2017-08-18');
insert into t_new_order values(2903, 29, 'init', 20, 'test', '2017-08-18');
-- 插入 t_merchant 测试数据
insert into t_merchant values(1, 86, 'tencent', '86000001', '86100000001', '2017-08-08');
insert into t_merchant values(2, 86, 'haier', '86000002', '86100000002', '2017-08-08');
insert into t_merchant values(3, 86, 'huawei', '86000003', '86100000003', '2017-08-08');
insert into t_merchant values(4, 86, 'alibaba', '86000004', '86100000004', '2017-08-08');
insert into t_merchant values(5, 86, 'lenovo', '86000005', '86100000005', '2017-08-08');
insert into t_merchant values(6, 86, 'moutai', '86000006', '86100000006', '2017-12-08');
insert into t_merchant values(7, 86, 'baidu', '86000007', '86100000007', '2017-08-08');
insert into t_merchant values(8, 86, 'xiaomi', '86000008', '86100000008', '2017-08-08');
insert into t_merchant values(9, 86, 'vivo', '86000009', '86100000009', '2017-11-08');
insert into t_merchant values(10, 86, 'oppo', '86000010', '86100000010', '2017-08-08');
insert into t_merchant values(11, 1, 'google', '01000011', '01100000011', '2017-08-08');
insert into t_merchant values(12, 1, 'walmart', '01000012', '01100000012', '2017-08-18');
insert into t_merchant values(13, 1, 'amazon', '01000013', '01100000013', '2017-08-08');
insert into t_merchant values(14, 1, 'apple', '01000014', '01100000014', '2017-07-08');
insert into t_merchant values(15, 1, 'microsoft', '01000015', '01100000015', '2017-08-08');
insert into t_merchant values(16, 1, 'dell', '01000016', '01100000016', '2017-08-08');
insert into t_merchant values(17, 1, 'johnson', '01000017', '01100000017', '2017-08-08');
insert into t_merchant values(18, 1, 'intel', '01000018', '01100000018', '2017-06-08');
insert into t_merchant values(19, 1, 'hp', '01000019', '01100000019', '2017-08-08');
insert into t_merchant values(20, 1, 'tesla', '01000020', '01100000020', '2017-08-08');
```
```
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 commented on issue #22569: In the federation scenario, after creating a view, and select the view, the result is "ERROR: relation "select_view" does not exist on dn_6001_6002_6003".
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22569:
URL: https://github.com/apache/shardingsphere/issues/22569#issuecomment-1333600277
```
<?xml version="1.0" encoding="UTF-8"?>
<ROOT>
<CLUSTER>
<PARAM name="clusterName" value="openGauss"/>
<PARAM name="master" value="linux208178" />
<PARAM name="nodeNames" value="linux208178,linux208183"/>
<PARAM name="gaussdbAppPath" value="/data/opt/openGauss/cluster/gauss/app"/>
<PARAM name="gaussdbLogPath" value="/data/opt/log/gaussdb" />
<PARAM name="gaussdbToolPath" value="/data/opt/om"/>
<PARAM name="backIp1s" value="8.0.208.178,8.0.208.183"/>
<PARAM name="clusterType" value="single-inst"/>
<PARAM name="corePath" value="/data/opt" />
<PARAM name="dss_home" value="/data/opt/openGauss/cluster/dss_home"/>
<PARAM name="enable_dss" value="on"/>
<PARAM name="dss_config" value="[{'ss_ip':'8.0.208.178','ss_port':'16182'},{'ss_ip':'8.0.208.183','ss_port':'16183'}]"/>
<PARAM name="votingDiskPath" value="/dev/sdh"/>
<PARAM name="shareDiskDir" value="/dev/sdi"/>
<PARAM name="dss_vg_info" value="data:/dev/sde,p0:/dev/sdf,p2:/dev/sdg"/>
<PARAM name="ss_dss_vg_name" value="data"/>
<PARAM name="ss_interconnect_type" value="TCP"/>
<PARAM name="dss_ssl_enable" value="on"/>
</CLUSTER>
<DEVICELIST>
<DEVICE sn="linux208178">
<PARAM name="name" value="linux208178"/>
<PARAM name="backIp1" value="8.0.208.178"/>
<PARAM name="sshIp1" value="8.0.208.178"/>
<PARAM name="azName" value="AZ1"/>
<PARAM name="azPriority" value="1"/>
<!-- cm -->
<PARAM name="cmDir" value="/data/opt/openGauss/cluster/cm"/>
<PARAM name="cmsNum" value="1"/>
<PARAM name="cmServerPortBase" value="28888"/>
<PARAM name="cmServerListenIp1" value="8.0.208.178,8.0.208.183"/>
<PARAM name="cmServerHaIp1" value="8.0.208.178,8.0.208.183"/>
<PARAM name="cmServerlevel" value="1"/>
<PARAM name="cmServerRelation" value="linux208178,linux208183"/>
<PARAM name="dataNum" value="1"/>
<PARAM name="dataPortBase" value="18888"/>
<PARAM name="dataPortStandby" value="4500"/>
<PARAM name="dataPortDummyStandby" value="4800"/>
<PARAM name="dataNode1" value="/data/opt/openGauss/cluster/data1/dn1,linux208183,/data/opt/openGauss/cluster/data1/dn1"/>
</DEVICE>
<DEVICE sn="linux208183">
<PARAM name="name" value="linux208183"/>
<PARAM name="backIp1" value="8.0.208.183"/>
<PARAM name="sshIp1" value="8.0.208.183"/>
<PARAM name="azName" value="AZ2"/>
<PARAM name="azPriority" value="2"/>
<!-- cm -->
<PARAM name="cmDir" value="/data/opt/openGauss/cluster/cm"/>
<PARAM name="cmServerPortStandby" value="26600"/>
</DEVICE>
</DEVICELIST>
</ROOT>
```
--
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 #22569: In the federation scenario, after creating a view, and select the view, the result is "ERROR: relation "select_view" does not exist on dn_6001_6002_6003".
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22569:
URL: https://github.com/apache/shardingsphere/issues/22569#issuecomment-1335089567
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
[GitHub] [shardingsphere] zhaojinchao95 commented on issue #22569: In the federation scenario, after creating a view, and select the view, the result is "ERROR: relation "select_view" does not exist on dn_6001_6002_6003".
Posted by GitBox <gi...@apache.org>.
zhaojinchao95 commented on issue #22569:
URL: https://github.com/apache/shardingsphere/issues/22569#issuecomment-1340425266
```
public class JDBCDriver2 {
static final String DB_URL = "jdbc:mysql://127.0.0.1:3307/sharding_db";
static final String USER = "root";
static final String PASS = "root";
public static void main(String[] args) throws Exception {
Statement statement = getConnection().createStatement();
List<String> sqlList = Arrays.asList("drop table if exists t_new_order;", "drop table if exists t_merchant;", "create table t_new_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);",
"create table t_merchant (merchant_id int primary key, country_id int not null, merchant_name varchar(50) not null, business_code varchar(50) not null, telephone varchar(50) not null, creation_date date not null);");
String t_new_order = "insert into t_new_order values(1000, 10, 'init', 1, 'test', '2017-07-08')," +
"(1001, 10, 'init', 2, 'test', '2017-07-08')," +
"(2000, 20, 'init', 3, 'test', '2017-08-08')," +
"(2001, 20, 'init', 4, 'test', '2017-08-08')," +
"(1100, 11, 'init', 5, 'test', '2017-08-08')," +
"(1101, 11, 'init', 6, 'test', '2017-08-08')," +
"(2100, 21, 'finish', 7, 'test', '2017-08-08')," +
"(2101, 21, 'finish', 8, 'test', '2017-08-08')," +
"(1200, 12, 'finish', 9, 'finish', '2017-08-08')," +
"(1201, 12, 'finish', 10, 'test22', '2017-08-18')," +
"(2200, 22, 'finish', 11, 'test', '2017-08-18')," +
"(2201, 22, 'finish', 12, 'test', '2017-08-18')," +
"(1300, 13, 'finish', 13, '', '2017-08-18')," +
"(1301, 13, 'finish', 14, 'test', '2017-08-18')," +
"(2300, 23, 'finish ', 15, 'test', '2017-08-18')," +
"(2301, 23, 'finish', 16, 'test', '2017-08-18')," +
"(1400, 14, 'init', 17, '', '2017-08-18')," +
"(1401, 14, 'init', 18, 'test', '2017-08-18')," +
"(2400, 24, 'init', 19, 'test', '2017-08-18')," +
"(2401, 24, 'init', 20, 'test', '2017-08-18')," +
"(1500, 15, 'init', 1, '', '2017-08-28')," +
"(1501, 15, 'init', 2, 'test', '2017-08-28')," +
"(2500, 25, 'init', 3, 'test', '2017-08-28')," +
"(2501, 25, 'init', 4, 'test', '2017-08-28')," +
"(1600, 16, 'init', 5, 'test', '2017-08-28')," +
"(1601, 16, 'init', 6, '', '2017-08-28')," +
"(2600, 26, 'init', 7, 'test', '2017-08-28')," +
"(1700, 17, 'init', 9, 'test', '2017-08-28')," +
"(1701, 17, 'finish', 10, 'test', '2017-08-18')," +
"(2700, 27, 'finish', 11, 'test', '2017-08-18')," +
"(2701, 27, 'finish', 12, 'test', '2017-08-18')," +
"(1800, 18, 'finish', 13, 'test', '2017-08-18')," +
"(2800, 28, 'finish', 15, 'test', '2017-08-18')," +
"(2801, 28, 'finish', 16, 'test', '2017-08-18')," +
"(1900, 19, 'init', 17, 'test', '2017-08-18')," +
"(1901, 19, 'init', 18, 'test', '2017-08-18')," +
"(2900, 29, 'init', 19, 'test', '2017-08-18')," +
"(2901, 29, 'init', 20, 'test', '2017-08-18')," +
"(1902, 19, 'init', 17, 'test11', '2017-08-18')," +
"(1903, 19, 'init', 18, 'test12', '2017-08-18')," +
"(2902, 29, 'init', 19, 'test', '2017-08-18')," +
"(2903, 29, 'init', 20, 'test', '2017-08-18');";
String t_merchant = "insert into t_merchant values(1, 86, 'tencent', '86000001', '86100000001', '2017-08-08')," +
"(2, 86, 'haier', '86000002', '86100000002', '2017-08-08')," +
"(3, 86, 'huawei', '86000003', '86100000003', '2017-08-08');";
for (String each : sqlList) {
statement.execute(each);
}
statement.execute(t_merchant);
statement.execute(t_new_order);
statement.execute("create view select_view as select all * from t_new_order where order_id>2800;");
statement.executeQuery("select * from select_view;");
statement.execute("drop view select_view;");
statement.execute("create view select_view as select distinct(user_id) from t_new_order order by user_id;");
statement.executeQuery("select * from select_view;");
// statement.execute("drop view select_view;");
// statement.execute("create view select_view as select distinct on (user_id) * from t_new_order order by user_id,order_id;");
// statement.executeQuery("select * from select_view;");
statement.execute("drop view select_view;");
statement.execute("create view select_view as select o.order_id,o.user_id,status,remark,merchant_name from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 2 limit 10;");
statement.executeQuery("select * from select_view;");
statement.execute("drop view select_view;");
}
private static Connection getConnection() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to database...");
return DriverManager.getConnection(DB_URL, USER, PASS);
}
}
```
I can't reproduce it.
--
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