Spring使用tomcat jdbc数据源连接MYSQL异常:CommunicationsException

系统运行阶段出现报错 com.mysql.jdbc.exceptions.jdbc4.CommunicationsException异常

错误信息

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 427,583,591 milliseconds ago. The last packet sent successfully to the server was 427,583,592 milliseconds ago. is longer than the server configured value of 'wait_timeout'. 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.

mysql版本:5.6.41
数据库链接池:tomcat jdbc

相关配置

数据源配置文件

<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
            <property name="driverClassName" value="${jdbc.driver}" />
            <property name="url" value="jdbc:mysql://xxxx:3306/xxx?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false" />
            <property name="username" value="${jdbc.username}" />
            <property name="password" value="${jdbc.password}" />
            <property name="maxActive" value="100" />
            <property name="maxIdle" value="50" />
            <property name="minIdle" value="5" />
            <property name="maxWait" value="-1" />
            <property name="validationQuery" value="SELECT 1" />
            <property name="testWhileIdle" value="true" />
            <property name="timeBetweenEvictionRunsMillis" value="60000" />
            <property name="minEvictableIdleTimeMillis" value="60000" />
            <property name="defaultAutoCommit" value="false" />
        </bean>

当前数据源配置文件已加上 autoReconnect=true

mysql配置文件

[mysqld]
innodb_buffer_pool_size = 150G
 join_buffer_size = 256M
 sort_buffer_size = 20M
 read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
group_concat_max_len = 1024000

symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
transaction-isolation=READ-COMMITTED

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

mysql wait_timeout 值为默认值8小时 28800

MYSQL不建议使用autoReconnect = true参数 http://pages.citebite.com/p4x3a0r8pmhm 。换个数据源试试看了。

使用c3p0连接池的testConnectionOnCheckout 属性可以自动保持数据库的连接,其实就是获取连接的时候检查一下是否有效 ,如果失效就重新获取。