The same scene，create view sometimes success，sometimes failed.

[wsm12138]

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).

[tuichenchuxin]

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.

[tuichenchuxin]

This case may be supported if we have gloable DDL transaction.

[github-actions[bot]]

Hello , this issue has not received a reply for several days. This issue is supposed to be closed.