​autoReconnect = ture​ 链接池包错

我的服务原文访问：autoReconnect = ture

链接池报错
Text:3031 Create:2022-12-17 19:08:43.0 Edit:2022-12-19 19:47:18

链接池报错

### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 
The last packet successfully received from the server was 44,177,666 milliseconds ago.  
The last packet sent successfully to the server was 44,177,667 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超时设置的问题
         如果连接闲置8小时 (8小时内没有进行数据库操作), mysql就会自动断开连接, 要重启tomcat.

解决办法：
   一种. 如果不用hibernate的话, 则在 connection url中加参数: autoReconnect=true

    jdbc.url=jdbc:mysql://ipaddress:3306/database?autoReconnect=true&autoReconnectForPools=true


   二种。用hibernate的话, 加如下属性:
       <property name="connection.autoReconnect">true</property>
       <property name="connection.autoReconnectForPools">true</property>
       <property name="connection.is-connection-validation-required">true</property>

   三。要是还用c3p0连接池:
       <property name="hibernate.c3p0.acquire_increment">1</property>
       <property name="hibernate.c3p0.idle_test_period">0</property>
       <property name="hibernate.c3p0.timeout">0</property>
       <property name="hibernate.c3p0.validate">true</property>

   mybatis配置

• mybatis的数据库连接配置项
• 参考 【MyBatis】Spring整合MyBatis配置_骑着蜗牛ひ追导弹'的博客-优快云博客_mybatis-spring配置

url=jdbc:mysql://127.0.0.1:3306/ssm?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false   
driverClassName=com.mysql.cj.jdbc.Driver
password=123456
username=root
maxActive=20
initialSize=1
maxWait=60000
maxIdle=15
minIdle=10
   
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
   
validationQuery=SELECT 'x'
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
removeAbandoned=true

maxOpenPreparedStatements=20
removeAbandonedTimeout=1800
logAbandoned=true

mybatis.xml 配置

<environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
                <!--数据库8小时断开配置-->
                <property name="poolPingEnabled" value="true"/>
                <property name="poolPingQuery" value="select now() from kpi.lastupdatedlog limit 1"/>
                <property name="poolPingConnectionsNotUsedFor" value="3600000"/>
            </dataSource>
        </environment>
    </environments>

四。修改mysql配置文件属性：

      MySQL服务器默认的“wait_timeout”是28800秒即8小时，意味着如果一个连接的空闲时间超过8个小时，MySQL将自动断开该连接，               而连接池却认为该连接还是有效的(因为并未校验连接的有效性)，当应用申请使用该连接时，就会导致上面的报错。
只能修改MySQL的参数了，wait_timeout最大为31536000即1年，在my.cnf中加入：


[mysqld]
wait_timeout=31536000
interactive_timeout=31536000

重启生效，需要同时修改这两个参数。

半个月
wait_timeout=1314000
interactive_timeout=1314000

stanserver.cn stanserver@163.com

https://stanserver.cn:444/blog/%E9%93%BE%E6%8E%A5%E6%B1%A0%E6%8A%A5%E9%94%99.html