Why does autoReconnect=true not seem to work?

Question

I am using JDBC to connect to a MySQL server (no connection pooling I think). In the connection URL I have autoReconnect=true

But my connection still times out. I've even checked conn.isClosed() and its false. But when I try to use the connection I get the following exception.

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Software caused connection abort: socket write error

STACKTRACE:

java.net.SocketException: Software caused connection abort: socket write error
...

I know in Java 1.6 you can use conn.isValid(0) to check the connection, but I am using Java 1.5

Is there a way to either ensure it doesn't time out? Or am I going to have to upgrade to Java 1.6?

Sequent

Answer 1

I had the same issue and it was absolutely maddening. Here's what the docs say on the MySQL website (emphasis mine)

Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications do not handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, investigate setting the MySQL server variable "wait_timeout" to some high value rather than the default of 8 hours.

In my experience, it doesn't seem like the "reconnect on the next query" functionality worked either, but I was using MySQL 4.0, which may have been the reason for that.

I ended up writing a mini-framework that catches the exceptions, checks for that specific error, and attempts to reconnect and retry the query if possible.

ETA: This link provides a bit more information, and indicates that autoReconnect will probably be removed in the future anyways.

Rapturous

The advice not to use autoReconnect because it will be removed in the future is 8-10 years old. And yet I'm still seeing MySQL 5.x provide guidance that setting the Connector/J autoReconnect property is an option to solve this problem... – Medalist

From the linked doc: "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." – Omsk

Answer 2

autoReconnect still throws the exception so you can choose to do something about the situation if you like. If you catch it, you should find that the connection is there again afterward. (There's some more complexity if you're in a transaction -- your current transaction is pretty much dead.)

Misconstrue