dataSource : com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

本文介绍了数据库迁移后遇到的mysql连接错误问题,该问题表现为偶尔能够访问页面,但有时会报错并提示连接失败,通过调整数据源配置解决了这一间歇性故障。

数据库迁移过后,一直被一个bug困扰,有时候可以访问页面,有时候报mysql连接错误,刷新一遍又可以了。因为迁移以前是在虚拟主机上装的mysql,可以修改mysql的配置文件,设置连接时长,但是迁移的新数据库是一个实例,所以只能在代码中改动。

解决办法如下:

数据源是dataSource

<property name="validationQuery" value="SELECT 1" />
<property name="testOnBorrow" value="true"/>


错误内容:

HTTP Status 500 - Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

type Exception report

message Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 325,737 milliseconds ago. The last packet sent successfully to the server was 14 milliseconds ago.
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:980)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:859)
javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:844)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
root cause

org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 325,737 milliseconds ago. The last packet sent successfully to the server was 14 milliseconds ago.
org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:245)
org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:427)
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
com.sun.proxy.$Proxy48.getAreas(Unknown Source)
com.xforce.storm.controller.StormDemoController.goStormDemo(StormDemoController.java:26)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:178)
org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:444)
org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:432)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:968)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:859)
javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:844)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
root cause

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 325,737 milliseconds ago. The last packet sent successfully to the server was 14 milliseconds ago.
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
java.lang.reflect.Constructor.newInstance(Constructor.java:526)
com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1129)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3720)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3609)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4160)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2828)
com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5372)
org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:331)
org.apache.commons.dbcp.PoolingDataSourcePoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:317)org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:225)org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:427)org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)com.sun.proxy.Proxy48.getAreas(Unknown Source)
com.xforce.storm.controller.StormDemoController.goStormDemo(StormDemoController.java:26)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:178)
org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:444)
org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:432)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:968)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:859)
javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:844)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
root cause

java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3166)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3620)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3609)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4160)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2828)
com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5372)
org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:331)
org.apache.commons.dbcp.PoolingDataSourcePoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:317)org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:225)org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:427)org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)com.sun.proxy.Proxy48.getAreas(Unknown Source)
com.xforce.storm.controller.StormDemoController.goStormDemo(StormDemoController.java:26)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:178)
org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:444)
org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:432)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:968)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:859)
javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:844)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
note The full stack trace of the root cause is available in the Apache Tomcat/8.0.33 logs.

Apache Tomcat/8.0.33

<think>嗯,用户遇到了com.mysql.jdbc.exceptions.jdbc4.CommunicationsException的问题,需要解决。首先,我得分析可能的原因。根据提供的引用内容,可能有几个常见原因。比如,网络问题,比如数据库服务器不可达,或者防火墙阻止了端口。然后是MySQL配置问题,比如wait_timeout设置导致连接被关闭,或者版本不兼容,比如JDBC驱动和MySQL版本不一致。还有连接池配置的问题,比如连接没有正确验证或回收。 用户提到程序第二天无法登录,这可能和wait_timeout有关,默认8小时后关闭空闲连接。这时候连接池可能还持有旧的连接,导致异常。所以需要检查连接池的配置,是否有设置自动重连或者验证连接的机制。比如在连接池中配置testOnBorrow或者testWhileIdle,确保连接有效。 另外,版本兼容性也是一个问题。用户如果用的是MySQL 8.0,而JDBC驱动可能还是旧版的,比如5.x,这会导致问题。需要换成8.x的驱动,并且注意URL中的时区设置,比如添加serverTimezone=UTC。 然后,网络问题需要检查数据库的IP、端口是否正确,防火墙是否开放了3306端口。可以用telnet或者ping命令测试连通性。还有MySQL的max_connections参数,是否连接数超过了限制,导致新连接被拒绝。 总结下来,解决方案应该包括检查网络、调整MySQL配置(wait_timeout和interactive_timeout)、更新JDBC驱动、配置连接池参数,以及处理时区问题。同时,异常处理和日志分析也是必要的步骤,帮助定位具体原因。 需要确保用户按照步骤一步步检查,可能还要根据他们的具体情况调整。比如,如果他们使用的是Tomcat连接池,可能需要不同的配置方式。此外,提醒用户查看服务器日志,是否有更详细的错误信息,这有助于更快定位问题。</think>### 分步解决方案:`com.mysql.jdbc.exceptions.jdbc4.CommunicationsException` 连接失败分析与解决 #### **1. 原因分析** - **网络问题**:数据库服务器不可达或端口被防火墙拦截[^3][^5]。 - **MySQL配置超时**:默认`wait_timeout=28800秒`(8小时),超时后MySQL主动关闭空闲连接,但连接池仍持有无效连接引用[^2][^5]。 - **版本不兼容**:JDBC驱动版本与MySQL服务器版本不匹配(如MySQL 8.0需使用`mysql-connector-java-8.x.jar`)[^4]。 - **连接池配置缺陷**:未启用连接有效性检查或自动重连机制。 --- #### **2. 解决方案** ##### **步骤1:检查网络连通性** - 使用`ping <数据库IP>`验证网络可达性。 - 使用`telnet <数据库IP> 3306`(或实际端口)检查端口开放状态。 ##### **步骤2:调整MySQL超时配置** - 修改`my.cnf`文件,增加或修改以下参数: ```ini wait_timeout = 86400 # 空闲连接超时时间(秒) interactive_timeout = 86400 max_connections = 200 # 避免连接数耗尽 ``` - 重启MySQL服务后生效[^5]。 ##### **步骤3:更新JDBC驱动与URL** - **驱动版本**:MySQL 8.0需使用`mysql-connector-java-8.0.x.jar`,替换旧版本驱动[^4]。 - **连接URL**:添加时区与SSL配置: ```java jdbc:mysql://localhost:3306/dbname?serverTimezone=UTC&autoReconnect=true&useSSL=false ``` ##### **步骤4:配置连接池参数** 以**HikariCP**为例,添加连接有效性检查与重试机制: ```java HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:mysql://localhost/dbname"); config.setConnectionTestQuery("SELECT 1"); // 验证连接的SQL config.setConnectionTimeout(30000); // 连接获取超时时间(毫秒) config.setIdleTimeout(600000); // 空闲连接超时时间(毫秒) config.setMaxLifetime(1800000); // 连接最大存活时间(毫秒) ``` ##### **步骤5:处理时区与SSL兼容性** - 若报时区错误,在URL中添加`serverTimezone=UTC`。 - 若SSL未启用,添加`useSSL=false`[^4]。 --- #### **3. 验证与异常处理** - **日志分析**:检查MySQL错误日志(默认路径`/var/log/mysql/error.log`)和应用日志,定位具体错误[^3]。 - **代码层重试机制**: ```java try { Connection conn = dataSource.getConnection(); // 执行SQL } catch (CommunicationsException e) { // 触发连接池重建或重试逻辑 } ``` --- ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值