Mysql autoReconnect 的问题

MySQL官方不推荐使用autoReconnect=true，参见 http://bugs.mysql.com/bug.php?id=5020
需要另外找别的办法来解决超过8小时，链接断开的问题。

由于问题产生的根本原因在于服务到数据库的连接长时间没活动，既然重新连接的办法无效，就可以尝试另外一种办法，就是反空闲。
自己写一个线程来反空闲的话，比较麻烦。
最后在网上找到一个办法。为hibernate配置连接池，推荐用c3p0，然后配置c3p0的反空闲设置idle_test_period，只要小于MySQL的wait timeout即可。
在hibernate.cfg.xml中增加下面几项：

<!-- configuration pool via c3p0-->
<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="c3p0.min_size">5</property>
<property name="c3p0.max_size">30</property>
<property name="c3p0.time_out">1800</property> <!-- seconds --><!-- default: 0 -->
<property name="c3p0.max_statement">50</property> <!-- default: 0 -->
<property name="c3p0.acquire_increment">1</property> <!-- default: 1 -->
<property name="c3p0.idle_test_period">120</property>  <!-- seconds --><!-- default: 0 -->
<property name="c3p0.validate">true</property>

[12 Aug 2004 18:46] Mark Matthews

Note: Autoreconnect functionality will be depcreated and eventually removed in future
releases. 

The reason this isn't working for your particular case is that the methodolgy for
autoreconnect was changed to be safer after 3.0.11, and is related to autoCommit state,
which will also cause the current 'in-flight' transaction to fail (if you attempt your
transaction again _after_ the failure, the driver will reconnect). Please see the docs for
the explanation on how to correctly use this feature in the 'Troubleshooting' section.

In any case, there is no 100% safe way that a JDBC driver can re-connect automatically if
a TCP/IP connection dies without risking corruption of the database 'state' (even _with_
transactional semantics), which is why this feature will eventually be removed.

The JDBC spec does not specify that a connection is alive no matter what happens to the
underlying network for this very reason. 

Clients of JDBC drivers are responsible for dealing with network failures, as only the
application itself (really the developer of the application) 'knows' what the 'correct'
response to a transaction failing due to the network going down is. 'Wait_timeout'
expiring on the server is basically a 'forced' network failure by the server. You can
correct this in a non-robust way by setting 'wait_timeout' higher, however, you as a
developer should be handling SQL exceptions in your code and taking appropriate recovery
actions, not just passing them up the call stack.

Connection errors aways have a SQLState class of '08'. If you detect this, you can get
another connection and retry the transaction (if it is appropriate).

If this is impractical for whatever reason, configure your connection pool to test for
connection 'liveness' and discard connections that are idle longer than whatever you've
set 'wait_timeout' to on the server (all modern connection pools can do this, but the
configuration of them depends on the pool).

[1 Jun 2005 15:01] Kirk Wylie

If the argument is that there might be a transaction going, then a pretty simple case can
be made:
- If the connection is in autoCommit mode, it's safe to autoReconnect.
- If the connection is not in autoCommit mode, but there is no open transaction, it's safe
to autoReconnect (since the connection may be pooled).
- If the connection is not in autoCommit mode, and there is an open transaction, then
throw an exception.

This would resolve the concerns which you have with non-autoCommit mode AND preserve
application functionality.

However, I find it a little disturbing that this was changed and there's no notice in the
changelogs that would indicate that this behavior has changed (I just checked since I was
having the same problem). Since this change was likely to break many applications, it
probably should have been added to the CHANGES file.

评论

1 楼 fjjiaboming 2011-07-25  

那就稍微翻译一下 啊....