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

[ltylty]

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

related issue
flyway/flyway#2215
flyway/flyway#2240

[xyohn]

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

[mengfanhong]

同样遇到这个问题，通过分析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]

ping

[qzmer1104]

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

[dannyZhou]

change to version flyway 5.2.1 and try again.

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

[ZERONE-GO]

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

[linghengqian]

• I have opened Add property closeConnOnFatalError to match HikariCP's behavior #6143 .
• By setting closeConnOnFatalError to false, Druid's behavior will align with HikariCP, that is, the connection will no longer be closed when a fatal error defined by Druid occurs. This solves a series of controversies surrounding Hibernate, Flyway, and ShardingSphere. This is a huge regret introduced from 882470b 7 years ago.