connect blocked in Standalone mode

[liuzhenghua]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

5.5.0

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

mysql client can connect.

Actual behavior

mysql client blocked.

Reason analyze (If you can)

If I clean the config repository，the mysql client can connect successfully.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1. start a shardingsphere-proxy 5.5.0，using the following config for global.yaml file mysql not work either: jdbc_url: jdbc:mysql://192.168.137.101:3306/config_db?useSSL=false&allowPublicKeyRetrieval=true&autoReconnect=true&allowMultiQueries=true

mode:
  type: Standalone
  repository:
    type: JDBC
    props:
      jdbc_url: jdbc:h2:file:/opt/shardingsphere-proxy/data/config_data;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MYSQL;DB_CLOSE_ON_EXIT=FALSE;AUTO_RECONNECT=TRUE

authority:
  users:
    - user: root@%
      password: 123456
    - user: sharding
      password: sharding
  privilege:
    type: ALL_PERMITTED

transaction:
  defaultType: XA
  providerType: Atomikos

sqlParser:
  sqlStatementCache:
    initialCapacity: 2000
    maximumSize: 65535
  parseTreeCache:
    initialCapacity: 128
    maximumSize: 1024

logging:
  loggers:
  - loggerName: ShardingSphere-SQL
    additivity: true
    level: INFO

sqlFederation:
  sqlFederationEnabled: false
  executionPlanCache:
    initialCapacity: 2000
    maximumSize: 65535

props:
  system-log-level: INFO
  max-connections-size-per-query: 1
  kernel-executor-size: 16  # Infinite by default.
  proxy-frontend-flush-threshold: 128  # The default value is 128.
  # sql-show is the same as props in logger ShardingSphere-SQL, and its priority is lower than logging rule
  sql-show: true
  check-table-metadata-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.
  proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
  proxy-default-port: 3307 # Proxy default port.
  proxy-netty-backlog: 1024 # Proxy netty backlog.
  cdc-server-port: 33071 # CDC server port
  proxy-frontend-ssl-enabled: false
  proxy-frontend-ssl-cipher: ''
  proxy-frontend-ssl-version: TLSv1.2,TLSv1.3

2. using docker compose to start the instance.

services:
  shardingsphere-proxy:
    container_name: shardingsphere-proxy
    image: apache/shardingsphere-proxy:5.5.0
    ports:
      - "3307:3307"
    volumes:
      - ./volumes/conf:/opt/shardingsphere-proxy/conf
      - ./volumes/ext-lib:/opt/shardingsphere-proxy/ext-lib
      - ./volumes/data:/opt/shardingsphere-proxy/data
    # command: -f
    restart: always

3. connect to sharding-proxy, and register some storage unit.

create database demo_db;
use demo_db;
SELECT DATABASE();

REGISTER STORAGE UNIT IF NOT EXISTS ds_0 (
    URL="jdbc:postgresql://<ip:port>/db_0?serverTimezone=UTC&sslmode=disable&allowMultiQueries=true",
    USER="app_user",
    PASSWORD="123456",
    PROPERTIES("maximumPoolSize"=10, "idleTimeout"="30000")
);
REGISTER STORAGE UNIT IF NOT EXISTS ds_1 (
    URL="jdbc:postgresql://<ip:port>/db_1?serverTimezone=UTC&sslmode=disable&allowMultiQueries=true",
    USER="app_user",
    PASSWORD="123456",
    PROPERTIES("maximumPoolSize"=10, "idleTimeout"="30000")
);
REGISTER STORAGE UNIT IF NOT EXISTS ds_2 (
    URL="jdbc:postgresql://<ip:port>/db_2?serverTimezone=UTC&sslmode=disable&allowMultiQueries=true",
    USER="app_user",
    PASSWORD="123456",
    PROPERTIES("maximumPoolSize"=10, "idleTimeout"="30000")
);
REGISTER STORAGE UNIT IF NOT EXISTS ds_3 (
    URL="jdbc:postgresql://<ip:port>/db_3?serverTimezone=UTC&sslmode=disable&allowMultiQueries=true",
    USER="app_user",
    PASSWORD="123456",
    PROPERTIES("maximumPoolSize"=10, "idleTimeout"="30000")
);

4. restart the sharding proxy, and using mysql client to connect, It would be blocked.

docker compose restart

mysql -hlocalhost -P3307 -uroot -p123456

Example codes for reproduce this issue (such as a github link).