MySQL创建连接时无响应,也不报错,求大神帮忙

liao1314 2013-09-13 09:36:45
最近遇到一个问题,从服务器A向服务器B的数据库插入数据,由于A,B之间的网络时断时续,当网络断开是造成A服务器的程序在连接B的数据库时无响应,即使AB之间的网络很快恢复程序也没响应,这时在A服务器通过mysql的命令行重新连接B数据库时完全正常,用jConsole查看,程序一直停留在getConnection,通过长时间的测试发现,每次都是要等两小时,A的程序才会报Communications link failure错误,请问是这是什么问题造成的?

jConsole查看如下:
阻塞总数:1  等待总数: 7,796

堆栈追踪:
java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.read(SocketInputStream.java:129)
com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
- 已锁定 com.mysql.jdbc.util.ReadAheadInputStream@3d53ec08
com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3116)
com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:599)
com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1086)
com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2486)
com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2519)
com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304)
- 已锁定 com.mysql.jdbc.JDBC4Connection@163c2595
com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
sun.reflect.GeneratedConstructorAccessor10.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
java.lang.reflect.Constructor.newInstance(Constructor.java:513)
com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1188)
org.apache.commons.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:79)
org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
com.test.dao.MyDataSource.getConnection(MyDataSource.java:38)




等待两小时后报错如下:
Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.GeneratedConstructorAccessor13.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:675)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1086)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2486)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2519)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor10.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1188)
at org.apache.commons.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:79)
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at com.test.dao.MyDataSource.getConnection(MyDataSource.java:38)
Caused by: java.net.SocketException: Connection reset
at java.net.SocketInputStream.read(SocketInputStream.java:168)
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3116)
at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:599)
... 20 more



以下是B服务器的mysql超时配置:
mysql> show variables like '%time%';
+----------------------------+-------------------+
| Variable_name | Value |
+----------------------------+-------------------+
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| flush_time | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 0 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 31536000 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 10.000000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1379035392 |
| wait_timeout | 31536000 |
+----------------------------+-------------------+


以下是DBCP配置:
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.1.10:3306/test
username=test
password=test123

#初始化连接数
initialSize=10

#最大连接数量
maxActive=50

#最大空闲连接
maxIdle=20

#最小空闲连接
minIdle=5

#超时等待时间(毫秒)
maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;]
#注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=rewriteBatchedStatements=true;useUnicode=true;characterEncoding=utf8

#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_COMMITTED

#是否自动回收超时连接
removeAbandoned=true
#超时时间(以秒数为单位)
removeAbandonedTimeout=180

#空闲时是否验证,若不通过断掉连接,前提是空闲对象回收器开启状态
testWhileIdle=true

timeBetweenEvictionRunsMillis=120000
minEvictableIdleTimeMillis=150000
validationQuery=SELECT 1
testOnBorrow=true
...全文
432 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
liao1314 2013-09-13
  • 打赏
  • 举报
回复
引用 3 楼 rucypli 的回复:
看来需要重启程序了。。
重启服务器时会正常,但是这个网络之间的异常没准不会发生,而且最近频率很高。
rucypli 2013-09-13
  • 打赏
  • 举报
回复
看来需要重启程序了。。
liao1314 2013-09-13
  • 打赏
  • 举报
回复
引用 1 楼 rucypli 的回复:
这应该是在连接字符串里设置autoreconnct吧
我在connectionProperties中加上了autoReconnect=true 还是一样的问题
rucypli 2013-09-13
  • 打赏
  • 举报
回复
这应该是在连接字符串里设置autoreconnct吧
miracleliu 2013-09-13
  • 打赏
  • 举报
回复
估计是DBCP连接池配置有问题,再查查看配置
liao1314 2013-09-13
  • 打赏
  • 举报
回复
求助啊~没有人帮忙么?

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧