Grails5升级后默认dataSource出现连接关闭操作异常

问题概述 #

Grails项目从3.1.6升级至5.1.7后，使用默认dataSource查询MySQL表时频繁出现通信链路失败问题。仅默认dataSource受影响，dataSource_pa无此异常；使用GORM方法查询可避免该问题。

异常信息 #

WARNING [http-nio-8088-exec-2] groovy.sql.Sql$AbstractQueryCommand.execute Failed to execute: SELECT id, loa, loa_id FROM crosswalk_table WHERE client_id=:clientId AND update_status=:updateStatus; because: No operations allowed after connection closed. java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.

本地Tomcat控制台额外打印：

14-Dec-2023 00:05:44.096 SEVERE [Tomcat JDBC Pool Cleaner[1285282956:1702488174095]] org.apache.tomcat.jdbc.pool.ConnectionPool.reconnectIfExpired Failed to re-connect connection [org.apache.tomcat.jdbc.pool.ConnectionPool@7c24cbe8] that expired because of maxAge com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

复现场景 #

• 部署环境稳定触发该问题
• 本地环境电脑闲置约15分钟后可复现

项目配置 #

application.yml #

hibernate:
    cache:
        queries: false
        use_second_level_cache: true
        use_query_cache: false
        region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'

dataSources:
  dataSource:
      pooled: true
      jmxExport: true
      driverClassName: com.mysql.cj.jdbc.Driver
      dialect: "org.hibernate.dialect.MySQL5InnoDBDialect"
      properties:
          maxActive: 100
          maxIdle: 25
          minIdle: 5
          initialSize: 5
          maxWait: 10000
          removeAbandoned: true
          removeAbandonedTimeout: 400
          logAbandoned: true
          maxAge: 60000
          minEvictableIdleTimeMillis: 5000
          timeBetweenEvictionRunsMillis: 15000
          numTestsPerEvictionRun: 3
          testOnBorrow: true
          testWhileIdle: true
          testOnReturn: true
          validationQuery: "SELECT 1"
          validationQueryTimeout: 3
          validationInterval: 15000

  pa:
      pooled: true
      jmxExport: true
      driverClassName: com.mysql.cj.jdbc.Driver
      dialect: "org.hibernate.dialect.MySQL5InnoDBDialect"
      properties:
          maxActive: 100
          maxIdle: 25
          minIdle: 5
          initialSize: 5
          maxWait: 10000
          removeAbandoned: true
          removeAbandonedTimeout: 400
          logAbandoned: true
          maxAge: 60000
          minEvictableIdleTimeMillis: 5000
          timeBetweenEvictionRunsMillis: 15000
          numTestsPerEvictionRun: 3
          testOnBorrow: true
          testWhileIdle: true
          testOnReturn: true
          validationQuery: "SELECT 1"

数据库地址配置文件 #

dataSources:
  dataSource:
    dbCreate: none
    url: jdbc:mysql://localhost:3306/everest_dev?zeroDateTimeBehavior=convertToNull&useSSL=false
    username: root
    password: root
  pa:
    dbCreate: none
    url: jdbc:mysql://localhost:3306/solu_dev?zeroDateTimeBehavior=convertToNull&useSSL=false
    username: root
    password: root

代码示例 #

@Slf4j
class LoaService {
      def dataSource
  
      def getLoaEditDataD(Map params) {
        String dwClientId = params.get("dwClientId", String.class)
        Sql sqlDAS
        try {
            sqlDAS = Sql.newInstance(dataSource)
            def sqlQuery = "SELECT id, loa, loa_id FROM loa_editor_crosswalk_table " +
                    "WHERE client_id=:clientId AND update_status=:updateStatus;"
            def sqlParams = [clientId: dwClientId, updateStatus: 'Completed', isDeleted: false]
            def result = sqlDAS.rows(sqlQuery, sqlParams)
            return result
        } catch (Exception exc) {
            exc.printStackTrace()
        } finally {
            if (sqlDAS != null) sqlDAS.close()
        }
    }

排查方向与解决方案 #

1. 连接池配置差异核查 #

默认dataSource额外配置了validationInterval: 15000，该参数会限制testWhileIdle的执行频率，可能导致无效连接未被及时检测。建议移除该配置，或调整为与timeBetweenEvictionRunsMillis一致的值，确保闲置连接能被定期验证。

2. MySQL数据库超时参数对比 #

检查两个数据库的wait_timeout和interactive_timeout参数：

• 若everest_dev的超时参数小于连接池maxAge（当前60000ms即1分钟），会导致MySQL主动断开连接，连接池重连失败。
• 调整数据库超时参数，确保其值大于maxAge + timeBetweenEvictionRunsMillis，或调小连接池maxAge至数据库超时值的80%。

3. JDBC URL参数补全 #

默认dataSource的JDBC URL未配置时区参数，可能引发连接异常。添加serverTimezone参数：

jdbc:mysql://localhost:3306/everest_dev?zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=UTC

4. 连接获取方式优化 #

代码中使用Sql.newInstance(dataSource)直接获取连接，可能未触发连接池的testOnBorrow验证机制。改为通过Spring注入Sql实例：

@Slf4j
class LoaService {
      def sql // 直接注入Spring管理的Sql实例
  
      def getLoaEditDataD(Map params) {
        String dwClientId = params.get("dwClientId", String.class)
        try {
            def sqlQuery = "SELECT id, loa, loa_id FROM loa_editor_crosswalk_table " +
                    "WHERE client_id=:clientId AND update_status=:updateStatus;"
            def sqlParams = [clientId: dwClientId, updateStatus: 'Completed', isDeleted: false]
            def result = sql.rows(sqlQuery, sqlParams)
            return result
        } catch (Exception exc) {
            exc.printStackTrace()
        }
    }

Spring会管理连接的获取与释放，确保连接池的验证规则生效。

5. 配置加载验证 #

检查Grails启动日志，确认默认dataSource的配置是否被环境特定配置（如application-prod.yml）覆盖，确保所有连接池参数按预期生效。

6. 驱动版本一致性验证 #

确认两个dataSource使用的MySQL驱动版本一致，升级Grails后可能默认驱动版本变化，可在build.gradle中显式指定驱动版本：

runtimeOnly 'mysql:mysql-connector-java:8.0.33'

内容的提问来源于stack exchange，提问作者Bijay