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

[HuLaLaGa]

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]

It seems to be a MySQL permission issue ?

[HuLaLaGa]

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

[linghengqian]

• I introduced how to use Alibaba Druid's StatViewServlet, WebStatFilter, and custom Filter to monitor the logical data source and real data source of ShardingSphere JDBC at [Article Contribution] Welcome to Leave Your Article Link Here #12258 (comment) and https://www.yuque.com/linghengqian/meve2v/zoy35sivtf6nzp90 .
• In the relevant example, I used some virtual threads to execute the inevitable insert SQL with the H2database database instance managed by Alibaba Druid at a frequency of once per second, and I was completely unable to reproduce your problem. All connections pooled by Druid can continue to be used.
• I have noticed at least 3 Alibaba Druid bugs, and these bugs have existed for at least 8 years. But these bugs are actually irrelevant to your description.

1. According to Set the default value of testWhileIdle to false to prevent Error Log alibaba/druid#6128, alibaba/druid should not set the default value of testWhileIdle to true.
2. According to druid监控 302重定向问题 alibaba/druid#5158, alibaba/druid mistakenly hard-coded /druid/login.html.
3. According to Configuring DefaultAdvisorAutoProxyCreator when proxying via cglib alibaba/druid#6132, when Spring AOP is configured to use CGLib proxy, alibaba/druid's Spring Boot Starter does not actively create the Spring Bean of DefaultAdvisorAutoProxyCreator.

• The master branch of shardingsphere contains unit tests for transactions on mysql managed by hikaricp, which cannot be reproduced from hotspot jdk or graalvm native image.
• @HuLaLaGa I now suspect that there is a bug in the mysql jdbc driver you are using. Or there is a bug in the mysql server. I think it's necessary for you to provide a git containing unit tests, where the database should be initialized via testcontainers-java and junit5 .