MySQL wait_timeout参数设置与网上常见错误小纠

原创 2013年12月02日 18:27:13

应用遇到异常报警:

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

The last packet successfully received from the server was 23,579 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
        at sun.reflect.GeneratedConstructorAccessor40.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:409)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3055)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2941)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1364)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)
        at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:185)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
        ... 36 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2502)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2952)
        ... 47 more


问题比较明显,应用连接已经失效。

23.579s之前创建了一个连接,之后再次使用此数据库连接就超时了。


查看数据库配置:

mysql> show  global  variables like  'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 20    |
+---------------+-------+
1 row in set (0.00 sec)

服务器设置的时间为20s.从而说明上面超时的原因。


解决这个方法:


一:从应用层解决问题:修改连接池参数  缩短连接池内的连接超时时间。


二:从DB层解决问题:增大MySQL的连接失效时间。

mysql> show  global  variables like  'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 20    |
+---------------+-------+
1 row in set (0.00 sec)


动态修改global参数,wait_timeout;

set global wait_timeout=30;


再次查看参数:

mysql> show  global  variables like  'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 30    |
+---------------+-------+
1 row in set (0.00 sec)

设置完毕。


时间的大小设置不能随便,时间过长,导致过多的connection Sleep,占用较多系统资源。时间设置过小也不行。从业务出发,结合查询的耗时与业务吞吐量来进行设置比较合适吧。





常见误解:


网上有很多文章 没有清晰的认识 会话变量与全局变量的不同,查看了会话变量的超时时间,最终可能会导致将全局变量 wait_timeout设置成了31536000s!!


[mysqld]

wait_timeout=31536000


my.cnf里设置的参数是global级别。不是会话级别。


然而:

mysql> show variables like  'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

是会话级别。有些人会把会话参数误认为全局参数。


正确的参数初始化设置应该为:

[mysqld]

wait_timeout=30 




建议大家今后查询变量的时候不要使用缺省。

查询会话变量:

mysql> show session variables like  '%wait%'; 
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| innodb_spin_wait_delay   | 6        |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+
4 rows in set (0.00 sec)


查询全局变量:

mysql> show global variables like  '%wait%';        
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| innodb_spin_wait_delay   | 6        |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 30       |
+--------------------------+----------+
4 rows in set (0.00 sec)


版权声明:本文为博主原创文章,未经博主允许不得转载。 举报

相关文章推荐

tomcat链接mysql时超时报错java.io.EOFException: Can not read response from server. Expected to read 4 bytes,

需要在配置文件里加上下面就ok了
  • zhq426
  • zhq426
  • 2013-08-06 10:06
  • 9397

mysql Can not read response from server. Expected to read 4 bytes 异常

http://stackoverflow.com/questions/13950496/what-is-java-io-eofexception-message-can-not-read-respon...

我是如何成为一名python大咖的?

人生苦短,都说必须python,那么我分享下我是如何从小白成为Python资深开发者的吧。2014年我大学刚毕业..

Caused by: java.io.EOFException: Can not read response from server.

1、错误描述The last packet successfully received from the server was 76,997 milliseconds ago. The last p...

mysql Communications link failure Last packet sent to the server was X ms ago

想必大家在用MySQL时都会遇到连接超时的问题,如下图所示:      就是这个异常(com.mysql.jdbc.exceptions.jdbc4.Communication***ception:C...

ajax图片上传及时回显图片,自己总结 + ajaxFileUpload 上传文件 以及 返回值 带 <pre> 标签问题

图片上传和回显 环境: springMVC + spring + mybatis  使用:ajaxfileupload.js 扩展包 + ajax异步请求 思想流程:         1...

mysql 数据备份和还原

D:\xampp\mysql\bin>mysqldump -u phplamp -p  voting_system --skip-lock-tables >bb b.sql Enter passw...

springmvc + mybatis+mysql 分页

dao层:Select * from 表名 limit 12,4limit 后边的第一个参数是指从第几条开始也就是pageStart; 第二个参数是limit,也就是从pageStart位置往后读多...

mysql存储和读取图片

首先,介绍一下mysql相关的数据类型

MySql的Communications link failure解决办法

MySql的Communications link failure解决办法
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)