The connection pool shouldn't close a connection after an exception

ltylty commented 4 years ago

spring boot 2.2, flyway 6.0.8.

Caused by: org.flywaydb.core.internal.exception.FlywaySqlException: 
Unable to determine value for 'foreign_key_checks' variable
-----------------------------------------------------------
SQL State  : null
Error Code : 0
Message    : connection disabled

    at org.flywaydb.core.internal.database.mysql.MySQLConnection.getIntVariableValue(MySQLConnection.java:64)
    at org.flywaydb.core.internal.database.mysql.MySQLConnection.<init>(MySQLConnection.java:56)
    at org.flywaydb.core.internal.database.mysql.MySQLDatabase.doGetConnection(MySQLDatabase.java:162)
    at org.flywaydb.core.internal.database.mysql.MySQLDatabase.doGetConnection(MySQLDatabase.java:40)
    at org.flywaydb.core.internal.database.base.Database.getConnection(Database.java:122)
    at org.flywaydb.core.internal.database.base.Database.getMainConnection(Database.java:315)
    at org.flywaydb.core.Flyway.prepareSchemas(Flyway.java:550)
    at org.flywaydb.core.Flyway.execute(Flyway.java:487)
    at org.flywaydb.core.Flyway.migrate(Flyway.java:149)
    at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:65)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1855)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1792)
    ... 18 common frames omitted
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.prepareStatement(DruidPooledConnection.java:337)
    at org.flywaydb.core.internal.jdbc.JdbcTemplate.prepareStatement(JdbcTemplate.java:332)
    at org.flywaydb.core.internal.jdbc.JdbcTemplate.queryForInt(JdbcTemplate.java:147)
    at org.flywaydb.core.internal.database.mysql.MySQLConnection.getIntVariableValue(MySQLConnection.java:62)
    ... 29 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: SELECT command denied to user '' for table 'user_variables_by_thread'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)
    at org.flywaydb.core.internal.jdbc.JdbcTemplate.queryForStringList(JdbcTemplate.java:119)
    at org.flywaydb.core.internal.database.mysql.MySQLConnection.hasUserVariableResetCapability(MySQLConnection.java:84)
    at org.flywaydb.core.internal.database.mysql.MySQLConnection.<init>(MySQLConnection.java:54)
    ... 28 common frames omitted
Disconnected from the target VM, address: '127.0.0.1:0', transport: 'socket'

Process finished with exit code 1

xyohn commented 3 years ago

实测最新版本1.2.1仍有该问题，怀疑是当之前的SQL语句执行出现SQLException时，druid会做处理，使得后续的连接会直接disabled 从而导致这个问题

mengfanhong commented 3 years ago

同样遇到这个问题，通过分析debug后，发现引起这问题的原因是druid的ExceptionSorter设计；

无权限SQL执行时 SELECT command denied to user '' for table 'user_variables_by_thread MYSQL返回ErrorCode:1142

https://github.com/alibaba/druid/blob/master/src/main/java/com/alibaba/druid/pool/vendor/MySqlExceptionSorter.java 进行处理 case 1142，isExceptionFatal 为true的话，会进行handleFatalError的处理 discard connection。

feranwq commented 3 years ago

ping

qzmer1104 commented 3 years ago

2000个issue 没人处理，阿里是放弃了这个连接池了吧？

dannyZhou commented 3 years ago

change to version flyway 5.2.1 and try again.

Ref: https://programmer.group/5de080703a7a8.html

ZERONE-GO commented 6 months ago

I found another solution to solve this issue when you use druid and flyway after 5.2.1 version.

You can configure the flyway.url, and then flyway will not use the datasource which provide by druid, however, it will create mysql connection, and then the problems will be solved.

spring:
  flyway:
    enabled: true
    # 自己定义url不会使用druid自带的连接池，可以防止flyway 5.2.1以后需要performance_schema的问题
    url: jdbc:mysql://localhost:3306/BigData?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
    user: testUser
    password: testUser@2023

alibaba / druid

The connection pool shouldn't close a connection after an exception #3626