search

apache / shardingsphere

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

connection disabled when use com.alibaba.druid.pool.DruidDataSource #32765

Open HuLaLaGa opened 1 week ago

HuLaLaGa commented 1 week ago

Which version of ShardingSphere did you use?

5.2.1

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

ShardingSphere-JDBC

Expected behavior

SQL normally run

Actual behavior

SQL throw Exception(connection disabled) when use com.alibaba.druid.pool.DruidDataSource

Reason analyze (If you can)

  1. DruidConnection will disable itself when sql throw fatal exception(like 'java.sql.SQLSyntaxErrorException: INSERT command denied to user 'test_test'@'localhost' for table 'test'')。

    com.alibaba.druid.pool.DruidDataSource#handleFatalError

  2. ShardingSphere don't determine if the connection is closed。

    org.apache.shardingsphere.driver.jdbc.core.connection.ConnectionManager#getConnections

  3. Reuse this disabled connection will throw Exception(connection disabled)。

    com.alibaba.druid.pool.DruidPooledConnection#checkStateInternal

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

<!-- pom.xml -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>5.2.1</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.23</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <version>2.3.12.RELEASE</version>
</dependency>
# application.yaml
spring:
  datasource:
    druid:
      driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
      url: jdbc:shardingsphere:classpath:shardingsphere.yaml
# shardingsphere.yaml
databaseName: test_test
mode:
  type: Standalone
  repository:
    type: JDBC
dataSources:
  read:
    url: "jdbc:mysql://localhost:3306/test_test?characterEncoding=utf-8&useSSL=false"
    # GRANT SELECT ON test_test.* TO 'test'@'localhost';
    # GRANT SELECT ON test_test.* TO 'test'@'%';
    # test only have SELECT privilege
    username: test
    password: test
rules:
  - !READWRITE_SPLITTING
    dataSources:
      dev:
        staticStrategy:
          writeDataSourceName: read
          readDataSourceNames:
            - read
    loadBalancers:
      random:
        type: RANDOM
// test.java
final ExecutorService threadPool = Executors.newFixedThreadPool(10);
for (int i = 0; i < 100; i++) {
  threadPool.execute(() -> {
      jdbcTemplate.execute("select ''");
      jdbcTemplate.execute("insert into test_test.test(name) values('test')");
  });
}
// Exception
Exception in thread "pool-3-thread-12" org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [select '']; SQL state [null]; error code [0]; connection disabled; nested exception is java.sql.SQLException: connection disabled
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:388)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:418)
    at com.test.controller.Controller.lambda$init2$1(Controller.java:125)
    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)
Caused by: java.sql.SQLException: connection disabled
    at com.alibaba.druid.pool.DruidPooledConnection.checkStateInternal(DruidPooledConnection.java:1169)
    at com.alibaba.druid.pool.DruidPooledConnection.checkState(DruidPooledConnection.java:1154)
    at com.alibaba.druid.pool.DruidPooledConnection.createStatement(DruidPooledConnection.java:657)
    at org.apache.shardingsphere.driver.jdbc.core.statement.StatementManager.createStorageResource(StatementManager.java:46)
    at org.apache.shardingsphere.driver.jdbc.core.statement.StatementManager.createStorageResource(StatementManager.java:37)
    at org.apache.shardingsphere.infra.executor.sql.prepare.driver.jdbc.builder.StatementExecutionUnitBuilder.createStatement(StatementExecutionUnitBuilder.java:44)
    at org.apache.shardingsphere.infra.executor.sql.prepare.driver.jdbc.builder.StatementExecutionUnitBuilder.build(StatementExecutionUnitBuilder.java:39)
    at org.apache.shardingsphere.infra.executor.sql.prepare.driver.jdbc.builder.StatementExecutionUnitBuilder.build(StatementExecutionUnitBuilder.java:34)
    at org.apache.shardingsphere.infra.executor.sql.prepare.driver.DriverExecutionPrepareEngine.createExecutionGroup(DriverExecutionPrepareEngine.java:103)
    at org.apache.shardingsphere.infra.executor.sql.prepare.driver.DriverExecutionPrepareEngine.group(DriverExecutionPrepareEngine.java:91)
    at org.apache.shardingsphere.infra.executor.sql.prepare.AbstractExecutionPrepareEngine.prepare(AbstractExecutionPrepareEngine.java:62)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.createExecutionContext(ShardingSphereStatement.java:527)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute0(ShardingSphereStatement.java:473)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute(ShardingSphereStatement.java:384)
    at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:633)
    at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:409)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376)
    ... 5 more
Caused by: java.sql.SQLSyntaxErrorException: INSERT command denied to user 'test_test'@'localhost' for table 'test'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:762)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:646)
    at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:633)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.lambda$execute$13(ShardingSphereStatement.java:384)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement$2.executeSQL(ShardingSphereStatement.java:442)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement$2.executeSQL(ShardingSphereStatement.java:438)
    at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:95)
    at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:75)
    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:65)
    at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:49)
    at org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.doExecute(DriverJDBCExecutor.java:156)
    at org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.execute(DriverJDBCExecutor.java:145)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute(ShardingSphereStatement.java:450)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute0(ShardingSphereStatement.java:475)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute(ShardingSphereStatement.java:384)
    at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:633)
    at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:409)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:418)
    at com.test.controller.Controller.lambda$init2$1(Controller.java:126)
    ... 3 more

The Exception SQL[insert into test_test.test(name) values('test')] cause normal SQL[select ''] can't work

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

comecny commented 1 week ago

It seems to be a MySQL permission issue ?

HuLaLaGa commented 1 week ago

It seems to be a MySQL permission issue ?

In this case, The Exception SQL[insert into test_test.test(name) values('test')] cause normal SQL[select ''] can't work @comecny