java - mybatis 设置 mysql 在 tomcat 服务器上超时

Question

使用java7 tomcat7和mybatis作为ORM

config.xml是这样的

<transactionManager type="JDBC" />
    <dataSource type="POOLED">
        <property name="driver" value="com.mysql.jdbc.Driver" />
        <property name="url"    value="jdbc:mysql://localhost:3306/xxxxdb" />
        <property name="username" value="xxxxxxx" />
        <property name="password" value="xxxxxxx" />
        <property name="poolPingEnabled" value="true" />
        <property name="poolPingQuery" value="SELECT 1 " />
    </dataSource>
</environment>

mysql 设置都是默认设置。因此 interactive_timeout 是 28800。
当我登录我的服务时，第一次失败，然后第二次成功。
即使在 28800 秒内重新登录，有时也会出现上述错误。我将错误消息粘贴到服务器中

2015 10:03:49 org.apache.ibatis.datasource.pooled.PooledDataSource warn
WARN: Execution of ping query 'SELECT 1' failed: Communications link failure

The last packet successfully received from the server was 30,572,290 milliseconds ago.  The     last packet sent successfully to the server was 1 milliseconds ago.
org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:     The last packet successfully received from the server was 36,001,604 milliseconds ago.  The     last packet sent successfully to the server was 36,001,633 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.
### The error may exist in sql.xml
### The error may involve com.xxx.isRegistered-Inline
### The error occurred while setting parameters
### SQL: [query for login];
### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 36,001,604 milliseconds ago.  The last packet sent successfully to the server was 36,001,633 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.

我尝试在连接 url 的末尾添加“autoReconnect=true”，但它没有解决问题

Answer 1

Java app handling for connections getting dropped 可能重复

在这里报告BalusC的回答:

This exception suggests that you're opening the connection only once during application's startup and keeping forever open during the application's lifetime. This is bad. The DB will reclaim the connection sooner or later because it's been open for too long. You should close connections properly in the finally block of the very same try block as you're opening it and executing the query on it.

E.g.

public Entity find(Long id) throws SQLException {
    Connection connection = null;
    // ...

    try {
        connection = database.getConnection();
        // ...
    } finally {
        // ...
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    return entity;
}

If you have a performance concern regarding this (which is very reasonable as connecting is the most expensive task), then you should be using a connection pool. It also transparently handles this kind of "connection dropped" problems. For example, BoneCP. Please note that also in case of a connection pool, you should still be closing the connections in the finally block as per the above JDBC code idiom. It will namely make them available for reuse.