search

apache / shardingsphere

Distributed SQL transaction & query engine for data sharding, scaling, encryption, and more - on any database.
Apache License 2.0
19.86k stars 6.73k forks source link

connect blocked in Standalone mode #32150

Open liuzhenghua opened 3 months ago

liuzhenghua commented 3 months ago

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

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

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:

transaction: defaultType: XA providerType: Atomikos

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

logging: loggers:

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.
```yaml
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
  1. 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:///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:///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:///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:///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.
```bash
docker compose restart

mysql -hlocalhost -P3307 -uroot -p123456

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

liuzhenghua commented 3 months ago

The problem lies with PostgreSQL. I am using postgres:15-alpine, and this issue occurs after configuring storage units, whether in standalone mode or cluster mode. It doesn't happen with MySQL.

terrymanu commented 3 months ago

Any error log in ShardingSphere-Proxy?

liuzhenghua commented 3 months ago

Only the logs showing successful startups are available, while on the client side, there is an indefinite block. Additionally, when using ZooKeeper to start the provider in cluster mode, there is another issue: after executing the register storage unit, it initially cannot be found, but after a while, some can be retrieved. There was an error reported on the backend, but the specific logs were not saved.

zhaojinchao95 commented 3 months ago

Add proxy-frontend-database-protocol-type: MySQL in props configuration.

liuzhenghua commented 3 months ago

Add proxy-frontend-database-protocol-type: MySQL in props configuration.

It worked but I need delete the repository file which created before, Is there any way that do not need delete the old rules?

there is an error in console logs but it does not effect the usage, the stack trace is as follows:

[INFO ] 2024-07-18 09:04:08.547 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: ds_0 ::: SHOW COLUMNS FROM users
[ERROR] 2024-07-18 09:04:09.491 [ShardingSphere-Command-0] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "FROM"
  Position: 14
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:341)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:326)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:302)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:297)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:1310)
        at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:102)
        at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
        at org.apache.shardingsphere.proxy.backend.connector.jdbc.executor.callback.impl.ProxyStatementExecutorCallback.execute(ProxyStatementExecutorCallback.java:44)
        at org.apache.shardingsphere.proxy.backend.connector.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:69)
        at org.apache.shardingsphere.proxy.backend.connector.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:46)
        at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85)
        at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64)
        at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:99)
        at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:95)
        at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:78)
        at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:66)
        at org.apache.shardingsphere.proxy.backend.connector.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:73)
        at org.apache.shardingsphere.proxy.backend.connector.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:217)
        at org.apache.shardingsphere.proxy.backend.connector.ProxySQLExecutor.execute(ProxySQLExecutor.java:176)
        at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.doExecute(DatabaseConnector.java:220)
        at org.apache.shardingsphere.proxy.backend.connector.DatabaseConnector.execute(DatabaseConnector.java:173)
        at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.fieldlist.MySQLComFieldListPacketExecutor.execute(MySQLComFieldListPacketExecutor.java:76)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.doExecuteCommand(CommandExecutorTask.java:126)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:121)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:78)
        at com.alibaba.ttl.TtlRunnable.run(TtlRunnable.java:60)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
        at java.base/java.lang.Thread.run(Thread.java:1583)
liuzhenghua commented 2 months ago

this error is caused by executing sql select * from users;, I found that SHOW COLUMNS FROM users is the syntax of mysql, not for postgres.