shardingsphere proxy 5.1.3 使用DistSQL配置后出现其他库消失的情况

求解,在5.1.3版本下,如果有存在多个库的情况下,在其中一个库中使用DistSQL配置分片规则后,会出现其他库消失的情况,重启后又恢复正常,这是什么情况?

请提供一下复现的配置和步骤呢,方便调查问题。


这是我操作的部分截图

mysql> ADD RESOURCE ds_0 (
    ->     HOST=127.0.0.1,
    ->     PORT=3306,
    ->     DB=demo_ds_0,
    ->     USER=root,
    ->     PASSWORD=123456
    -> ), ds_1(
    ->     HOST=127.0.0.1,
    ->     PORT=3306,
    ->     DB=demo_ds_1,
    ->     USER=root,
    ->     PASSWORD=123456
    -> );
Query OK, 0 rows affected (0.38 sec)

mysql> CREATE SHARDING TABLE RULE t_order (
    -> RESOURCES(ds_0, ds_1),
    -> SHARDING_COLUMN=order_id,TYPE(NAME=MOD,PROPERTIES("sharding-count"=4))
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> show databases;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| performance_schema |
| test               |
| sharding_db        |
| mysql              |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> CREATE SHARDING TABLE RULE t_order_item (
    -> RESOURCES(ds_0, ds_1),
    -> SHARDING_COLUMN=order_id,TYPE(NAME=MOD,PROPERTIES("sharding-count"=4))
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> show databases;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| performance_schema |
| test               |
| sharding_db        |
| mysql              |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> create sharding binding table rules(t_order,t_order_item);
Query OK, 0 rows affected (0.08 sec)

mysql> show databases;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| performance_schema |
| test               |
| sharding_db        |
| mysql              |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

@novice-gamer 我使用 master 分支不能重现你的问题,请试试最新的 commit 呢? 或者提供更多信息,包括:

  • mode 配置 (server.yaml)
  • commit 版本

我重新clone的master分支最新的版本,还是出现相同的问题:

[root@localhost application]# mysql -uroot -p -P3307 -h192.168.1.4
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37-ShardingSphere-Proxy 5.1.3-SNAPSHOT-dirty-7c56088 

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
Empty set (0.03 sec)

mysql> create schema bid_analyse_dev;
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+-----------------+
| schema_name     |
+-----------------+
| bid_analyse_dev |
+-----------------+
1 row in set (0.00 sec)

mysql> create database bid_analyse;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+-----------------+
| schema_name     |
+-----------------+
| bid_analyse_dev |
| bid_analyse     |
+-----------------+
2 rows in set (0.00 sec)

mysql> use bid_analyse_dev;
Database changed
mysql> ADD RESOURCE ds_0 (
    -> URL="jdbc:mysql://192.168.1.6:3306/bid_analyse_dev_0?serverTimezone=UTC&useSSL=false",
    -> USER=root,
    -> PASSWORD="xxxx",
    -> PROPERTIES("maxPoolSize"=200,"minPoolSize"=20)
    -> ),
    -> ds_1 (
    -> URL="jdbc:mysql://192.168.1.7:3306/bid_analyse_dev_1?serverTimezone=UTC&useSSL=false",
    -> USER=root,
    -> PASSWORD="xxxxxx",
    -> PROPERTIES("maxPoolSize"=200,"minPoolSize"=20)
    -> ),
    -> ds_2 (
    -> URL="jdbc:mysql://192.168.1.8:3306/bid_analyse_dev_2?serverTimezone=UTC&useSSL=false",
    -> USER=root,
    -> PASSWORD="xxxxxx",
    -> PROPERTIES("maxPoolSize"=200,"minPoolSize"=20)
    -> ),
    -> ds_3 (
    -> URL="jdbc:mysql://192.168.1.9:3306/bid_analyse_dev_3?serverTimezone=UTC&useSSL=false",
    -> USER=root,
    -> PASSWORD="xxxxxxx",
    -> PROPERTIES("maxPoolSize"=200,"minPoolSize"=20)
    -> );
Query OK, 0 rows affected (1.04 sec)

mysql> show databases;
+-----------------+
| schema_name     |
+-----------------+
| bid_analyse     |
| bid_analyse_dev |
+-----------------+
2 rows in set (0.00 sec)

mysql> CREATE SHARDING ALGORITHM bid_analyse_dev_csys_company_buffer_info_33_inline (
    -> TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=4)
    -> ));
Query OK, 0 rows affected (0.29 sec)

mysql> CREATE SHARDING ALGORITHM csys_company_buffer_info_33_inline (
    -> TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=2)
    -> ));
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE SHARDING TABLE RULE csys_company_buffer_info_33 (
    -> DATANODES("ds_${0..3}.csys_company_buffer_info_33_${0..1}"),
    -> DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=tag,SHARDING_ALGORITHM=bid_analyse_dev_csys_company_buffer_info_33_inline),
    -> TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=company_id,SHARDING_ALGORITHM=csys_company_buffer_info_33_inline)
    -> );
Query OK, 0 rows affected (0.89 sec)

mysql> CREATE SHARDING BINDING TABLE RULES (csys_company_buffer_info_33);
Query OK, 0 rows affected (0.10 sec)

mysql> show databases;
+-----------------+
| schema_name     |
+-----------------+
| bid_analyse_dev |
+-----------------+
1 row in set (0.00 sec)

server.yaml:

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: sharding_proxy_dev
      server-lists: zk_host_1:2181,zk_host_2:2181,zk_host_3:2181,zk_host_4:2181,zk_host_5:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
  overwrite: false

rules:
  - !AUTHORITY
    users:
      - root@%:root
      - sharding@:sharding
    provider:
      type: ALL_PERMITTED
#  - !TRANSACTION
#    defaultType: XA
#    providerType: Atomikos
#  - !SQL_PARSER
#    sqlCommentParseEnabled: true
#    sqlStatementCache:
#      initialCapacity: 2000
#      maximumSize: 65535
#    parseTreeCache:
#      initialCapacity: 128
#      maximumSize: 1024

props:
  max-connections-size-per-query: 1
  kernel-executor-size: 16  # Infinite by default.
  proxy-frontend-flush-threshold: 128  # The default value is 128.
  proxy-hint-enabled: false
  sql-show: true
  check-table-metadata-enabled: false
  show-process-list-enabled: false
    # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
    # The default value is -1, which means set the minimum value for different JDBC drivers.
  proxy-backend-query-fetch-size: -1
  proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
    # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
    # and block other clients if client connections are more than `proxy-frontend-executor-size`, especially executing slow SQL.
  proxy-backend-executor-suitable: OLAP
  proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
  sql-federation-enabled: false
    # Available proxy backend driver type: JDBC (default), ExperimentalVertx
  proxy-backend-driver-type: JDBC
  proxy-mysql-default-version: 5.7.37 # In the absence of schema name, the default version will be used.
  proxy-default-port: 3307 # Proxy default port.
  proxy-netty-backlog: 1024 # Proxy netty backlog.

@novice-gamer 为什么从你的输出中看不到这几个元数据库呢?是手动删除了吗?

没有,我只是设置了这个用户允许访问指定的库

了解,这个问题和 GitHub 上一个 issue 相似,应该在 master 分支已经修复了,可以从 master 构建并验证。

京ICP备2021015875号