springcloud项目来链接数据库几分钟后会断开连接

Question

我在本地idea启动了springcloud项目 连接的是远程的数据库 过几分钟后就会和数据库断开连接 然后报这个错

20:33:01:606  WARN 31872 --- [nio-8082-exec-1] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@251b62a6 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
20:33:01:870  INFO 31872 --- [nio-8082-exec-9] c.hmall.cart.controller.CartController   : user 1
20:33:06:707  WARN 31872 --- [nio-8082-exec-6] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@2670eba9 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
20:33:11:623  WARN 31872 --- [nio-8082-exec-1] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@39a0c21e (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
20:33:11:886  WARN 31872 --- [nio-8082-exec-9] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@2b9b783e (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
20:33:16:723  WARN 31872 --- [nio-8082-exec-6] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@50cbca48 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
20:33:21:650  WARN 31872 --- [nio-8082-exec-1] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@670afa73 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
20:33:21:687 ERROR 31872 --- [nio-8082-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30073ms.
### The error may exist in com/hmall/cart/mapper/CartMapper.java (best guess)
### The error may involve com.hmall.cart.mapper.CartMapper.selectList
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30073ms.] with root cause

com.mysql.cj.exceptions.ConnectionIsClosedException: No operations allowed after connection closed.
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:na]
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490) ~[na:na]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.23.jar:8.0.23]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.23.jar:8.0.23]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.23.jar:8.0.23]
    at com.mysql.cj.NativeSession.checkClosed(NativeSession.java:1171) ~[mysql-connector-java-8.0.23.jar:8.0.23]
    at com.mysql.cj.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:573) ~[mysql-connector-java-8.0.23.jar:8.0.23]
    at com.mysql.cj.jdbc.ConnectionImpl.setNetworkTimeout(ConnectionImpl.java:2491) ~[mysql-connector-java-8.0.23.jar:8.0.23]
    at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:566) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:173) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-4.0.3.jar:na]
    at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:159) ~[spring-jdbc-5.3.27.jar:5.3.27]
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:117) ~[spring-jdbc-5.3.27.jar:5.3.27]
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80) ~[spring-jdbc-5.3.27.jar:5.3.27]

    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.27.jar:5.3.27]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:481) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:390) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:926) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1791) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.75.jar:9.0.75]
    at java.base/java.lang.Thread.run(Thread.java:829) ~[na:na]

Answer 1

引自免费微信小程序：皆我百晓生

问题分析： 根据日志信息，可以看到项目连接到远程数据库后，几分钟后就会和数据库断开连接，导致应用程序无法访问数据库。

解决方案： 出现该问题可能有以下几个原因：

1. 数据库连接超时：数据库连接在一段时间内没有进行任何操作，超过了连接池的配置时间，连接就会被断开。

   解决方法：在连接池配置文件中调整maxLifetime参数的值，可以把它设置得更小一些，以避免连接超时。例如，将maxLifetime设置为较小的值（单位：毫秒），如30000（30秒）。

2. 数据库连接被防火墙关闭：检查远程数据库服务器的防火墙设置，确保应用服务器可以访问数据库服务器。

3. 数据库连接被远程服务器关闭：检查数据库服务器的配置文件或日志，看是否有设置了连接超时时间，并且在一段时间内没有活动后就关闭连接。

4. 数据库服务器负载过高：当数据库服务器的负载过高时，可能会主动关闭空闲连接，以节省资源。

针对上述可能的原因，你可以尝试以下解决方案：

1. 检查数据库连接池的配置文件，确保maxLifetime参数的值设置合理。

2. 检查数据库服务器的防火墙设置，确保应用服务器可以访问数据库服务器。

3. 检查数据库服务器的配置文件或日志，查看是否有设置连接超时时间，并且在一段时间内没有活动后就关闭连接。

4. 检查数据库服务器的负载情况，如果负载过高，考虑优化数据库查询，或增加数据库服务器的硬件资源。

如果以上方法仍然无法解决问题，可以尝试以下进一步排查方法：

1. 检查应用程序的日志，查看是否有其他错误或异常信息。

2. 尝试通过其他工具或方式连接到远程数据库，验证是否可以正常连接和执行查询。

希望以上解决方案能够帮助你解决问题。如果问题仍然存在，请提供更多的信息或日志，以便进一步分析和解决。