mysql wait_timeout设置导致连接池连接失效问题分析

    这两天开始陆续有同事反馈, 定时job访问mysql时,经常会报类似如下的错误.

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 18,826 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.

    首先仔细查看了下代码,并没有发现问题, 底层数据库操作的代码近期也没有改动过, 百度了上面提到的错误, 一般的解释是, mysql通过wait_timeout参数设置了连接的最大空闲时间, 默认是8小时.而如果数据库连接打开时间超过了wait_timeout参数设置的时间,则有可能因为空闲时间过长,导致mysql关闭了该连接而程序却并不知道,导致数据库操作失败.

    带着这个疑问去找了下DBA, 查了下数据库的设置, 一看不得了, 原来不知道什么时候,他们将wait_timeout设置为5s...

    回来后,看了下C3P0的设置, C3P0通过maxIdleTime参数设置了数据库连接池中连接的最大空闲时间, 原来的设置是20s. 将时间改为4s后,再也没有出现过上述问题.

    对这个问题的分析是,C3P0设置的连接池连接最大空闲时间如果是20s,即连接池中的连接处于空闲时间20s会将该连接释放,移出连接池. 但由于mysql设置了5s的wait_timeout, 一个连接超过5s处于空闲状态, mysql就会关闭这个连接, 而此时程序并不知道,依然认为这个连接可用, 当使用这个连接进行数据库操作时,就会导致这个问题. 

    DBA将wait_timeout设置为5s,导致程序中连接池的最大空闲时间设置只能设置在4s以内. 假设quartz调度的定时作业每分钟执行一次,这种情况下最好将C3P0的minPoolSize参数设置为0即连接池的最小连接数为0, 否则C3P0为了保证连接池中有不少于minPoolSize数量的可用连接,会每隔四秒钟从数据库中获取minPoolSize个连接. 对数据库存在一定的开销.