现象:网站上线后,由于开始访问量不是很多,休息天一般都无人访问。发现星期一上班过来,打开网站,报页面找不到的应用错误。
调查原因,原来是DB链接失效,mysql 的数据库有一个全局变量,设定数据库链接有效期限的,默认为8小时。
如果超过这个时间,一直没有访问数据库的话,mysql会自动将其失效。
解决这个问题有两个方法:
参数设置:参考官网 http://commons.apache.org/dbcp/configuration.html
<?xml version="1.0" encoding="UTF-8"?>
<Context reloadable="true" antiResourceLocking="false">
<Resource name="jdbc/dbname"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.gjt.mm.mysql.Driver"
url="jdbc:mysql://IP.ADD.RE.SS:3306/dbname?characterEncoding=UTF-8"
username="******"
password="******"
maxActive="500"
maxIdle="30"
maxWait="10000"
validationQuery = "SELECT 1"
testWhileIdle = "true"
timeBetweenEvictionRunsMillis = "3600000"
minEvictableIdleTimeMillis = "18000000"
testOnBorrow = "true"/>
</Context>
The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect().
interactive_timeout:
The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory.
问题:
要同时设置interactive_timeout和wait_timeout才会生效。
set wait_timeout=100
set interactive_timeout=100
调查原因,原来是DB链接失效,mysql 的数据库有一个全局变量,设定数据库链接有效期限的,默认为8小时。
如果超过这个时间,一直没有访问数据库的话,mysql会自动将其失效。
解决这个问题有两个方法:
1.如下,自动激活DB链接。
设置参考:DB的context.xml文件,添加下面5行属性。参数设置:参考官网 http://commons.apache.org/dbcp/configuration.html
<?xml version="1.0" encoding="UTF-8"?>
<Context reloadable="true" antiResourceLocking="false">
<Resource name="jdbc/dbname"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.gjt.mm.mysql.Driver"
url="jdbc:mysql://IP.ADD.RE.SS:3306/dbname?characterEncoding=UTF-8"
username="******"
password="******"
maxActive="500"
maxIdle="30"
maxWait="10000"
validationQuery = "SELECT 1"
testWhileIdle = "true"
timeBetweenEvictionRunsMillis = "3600000"
minEvictableIdleTimeMillis = "18000000"
testOnBorrow = "true"/>
</Context>
2.可以修改mysql DB的全局参数。
将默认原来8小时的时效期限更新为合适的时长。
The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect().
interactive_timeout:
The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory.
问题:
如果在配置文件my.cnf中只设置参数wait_timeout=100,则重启服务器后进入,
执行: mysql> show variables like “%timeout%”
要同时设置interactive_timeout和wait_timeout才会生效。
set wait_timeout=100
set interactive_timeout=100
重启MySQL Server进入后,查看设置已经生效。