What makes you think jdbc autoreconnect is needed?
Application is idle for long periods at a time?
Wait_timeout too short?
Network failure or glitches?

Some good suggestions form Mark Matthews - Bug #5020

Having encountered the problem again myself today, trying to make jdbc for mysql reconnect any terminated connections using autoreconnect=true I figured out a way to work it out from the pooling side.


Introduction to the problem:

On the mysql side wait_timeout is set to default 8hrs and any connections idle for longer than that were beomg terminated despite setting the connection string to: url=jdbc:mysql://localhost:3306/dbname?autoReconnect=true. The application was thence throwing an exception.

The solution was to introduce a ping from the pooler which for “Ibatis”, the pooler technology used in this case, was:

This covers eventualities of network glitches or connections exceeding the wait_timeout having the pooler ping the database if the connection was idle for more than 10 seconds (the value is in milliseconds).

The configuration if Ibatis in this case is something like:

Ibatis

<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property value="${driver}" name="JDBC.Driver"/>
<property value="${url}" name="JDBC.ConnectionURL"/>
<property value="${username}" name="JDBC.Username"/>
<property value="${password}" name="JDBC.Password"/>
<property name="Pool.PingQuery" value="select 1"/>
<property name="Pool.PingEnabled" value="true"/>
<property name="Pool.PingConnectionsOlderThan" value="3600000"/> <!-- 1 hr -->
<property name="Pool.PingConnectionsNotUsedFor" value="10000"/> <!-- ping db 10 sec -->
</dataSource>
</transactionManager>





For every pooling technology there will be a different “Pool.PingConnectionsNotUsedFor” term, but the concept is still there. It worked like a charm.




JDBC driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/dbname
username=username
password=password




PS … autoreconnect is not recommended when using mysql jdbc!

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: +2 (from 2 votes)

mysql jdbc connector autoReconnect=true , 10.0 out of 10 based on 1 rating

  1. [...] MySQL Preacher » mysql jdbc connector autoReconnect=true beomg-terminated, connection, default-8hrs, despite-setting, idle-for, setting-the-connection, [...]

  2. Antony says:

    Should be in a big banner:

    “autoreconnect is extremely not recommended when using any transactional storage engine.”

*