Grails连MySQL - 连接池超时问题

[b]问题表象[/b]
上线后的Grails应用，第二天，第一次登陆总是提示密码错误，再登陆一下就能成功。

[b]推测倒是很准确[/b]
肯定是Tomcat或MySQL的某处超时设置导致的

[b]准确诊断[/b]
因为无法在开发环境下模拟问题，先想办法在服务器端记录日志
Grails集成了[color=blue]log4j[/color]，用起来超级简单，而且还支持“环境”选项。

log4j = {
    appenders {
        environments {
            production {
                file name: 'file', file: '/home/jcat/myoalog/myoa.log'
            }
            development {
                file name: 'file', file: 'c:\\myoa.log'
            }
        }
    }

    root {
        info 'stdout', 'file'
    }

    info 'grails.app', 'myoa.StaticFilter'
}

[color=red]然后在日志中发现了问题（这个log并不是我设计的，而是Shiro框架源码里的，可见人家是专业的）[/color]
2013-05-20 16:12:45,582 [http-bio-8080-exec-40] ERROR util.JDBCExceptionReporter - The last packet successfully received from the server was [color=red]282,966,727 milliseconds [/color]ago. The last packet sent successfully to the server was 282,966,728 milliseconds ago. is longer than the server configured value of [color=red]'wait_timeout'[/color]. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

2013-05-20 16:12:45,601 [http-bio-8080-exec-40] INFO app.realm - Unable to authenticate with myoa.DbRealm - could not execute query; nested exception is org.hibernate.exception.JDBCConnectionException: could not execute query

[b]模拟错误[/b]
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)


--重启后失效
set global wait_timeout=60; --60秒

--需要停机配置
cat /etc/my.cnf
[mysqld]
wait_timeout=60

有趣的是，连接池超时后，登录后的浏览器会话并不会超时，所以第一次刷新会报错，再刷新（并不需要登录）就正常工作了。

[b]最终解决[/b]
有3个途径
1. 调大MySQL的timout时间，网上说，最大到21天左右
2. 修改Hibernate的连接池组件，采用更高级的c3p0 http://blog.csdn.net/nethibernate/article/details/6658855
3. 修改默认的DBCP连接池的配置
[color=red]http://www.netingcn.com/dbcp-config.html[/color]
http://sacharya.com/grails-dbcp-stale-connections

【法1-推荐】

        dataSource {   
          //解决数据库连接池长期不用超时的问题
            properties {
                timeBetweenEvictionRunsMillis = 1000 * 60 * 60 * 3  //每3小时检查一次
                minEvictableIdleTimeMillis = 1000 * 60 * 60 * 3     //把超过3小时的空闲连接激活一次
                //这样最差的情况，一个连接最多空闲6小时，而MySQL配置的是8小时断开
            }
        }

【法2-这会导致从连接池拿连接之前都会执行一次select 1语句】

                testOnBorrow = true
                testWhileIdle = false
                testOnReturn = false
                validationQuery = "SELECT 1"