Todd's MySQL Blog

Tiny tidbits of trivia from Todd

Do you really want autoReconnect to silently reconnect?

Chances are, if you write Java applications using MySQL’s Connector/J driver, you’ve run across the autoReconnect property.  I remember that when I first found it, it seemed I had found the grail itself.  “No more nasty connection closed error messages,” I thought.  Except … it doesn’t really work that way, does it?  I’ve seen this question asked many times in many different contexts:  “Why doesn’t Connector/J just reconnect to MySQL and re-issue my statement, instead of throwing this Exception?”

There are actually a number of reasons, starting with loss of transactional integrity.  The MySQL Manual states that “there is no safe method of reconnecting to the MySQL server without risking some corruption of the connection state or database state information.”   Imagine the following series of statements:

conn.createStatement().execute(
  "UPDATE checking_account SET balance = balance - 1000.00 WHERE customer='Todd'");
conn.createStatement().execute(
  "UPDATE savings_account SET balance = balance + 1000.00 WHERE customer='Todd'");
conn.commit();

Now, what happens if the connection to the server dies after the UPDATE to checking_account? If no Exception is thrown, and the application never learns about the problem, it keeps going.  Except that the server never committed the transaction, so that gets rolled back. But then you start a new transaction by increasing the savings_account balance by 5. Your application never got an Exception, so it kept plodding through, eventually commiting. But the commit only applies to the changes made in the new connection, which means you’ve just increased the savings_account balance by 5 without the corresponding reduction to checking_account.  Instead of transferring $1000.00, you just gave me $1000.00.  Thanks!

“So?” you say.  “I run with auto-commit enabled.  That won’t cause any problems like that.”

Actually, it can be worse.  When Connector/J encounters a communication problem, there’s no way to know whether the server processed the currently-executing statement or not.  The following theoretical states are equally possible:

  • The server never received the statement, and therefore nothing happened on the server.
  • The server received the statement, executed it in full, but the response never got to the client.

If you are running with auto-commit enabled, you simply cannot guarantee the state of data on the server when a communication exception is encountered.  The statement may have reached the server; it may have not.  All you know is that communication died at some point, before the client received confirmation (or data) from the server.  This doesn’t just affect auto-commit statements, though – imagine if the communication problem pops up during Connection.commit().  Did it commit on the server before communication died?  Or did the server never receive the COMMIT request?  Ugh.

There’s also transaction-scoped contextual data to be concerned about.  For example:

  • Temporary tables
  • User-defined variables
  • Server-side prepared statements

These things all die when connections die, and if your application uses any of them, any number of ugly things could happen- some silently – if a connection is re-established, but the application plods on unknowingly.

The bottom line is that communication errors generate conditions which may well be unsafe for the driver to silently reconnect and retry, and the application should be notified.  As an application developer, how you handle that information is up to you, but you should be glad that Connector/J notifies you.

Single Post Navigation

5 thoughts on “Do you really want autoReconnect to silently reconnect?

  1. I agree to all points raised – thanks for the explanation.

    However, what if I have a read-only application, and I really just want it to reconnect?

  2. Todd Farmer on said:

    Even read-only applications can depend on connection-specific context such as user variables, temporary tables, or server-side prepared statements. If you have a read-only application where you absolutely know you can always retry statements when connections fail with no adverse impacts, you can write a utility method (or implement a statement interceptor) that retries automatically before returning an Exception.

  3. Todd, thanks!

  4. Pingback: » Exam Cram: MySQL Architecture for Developers (Section 1) Todd's MySQL Blog

  5. Pingback: » MySQL High Availability with Oracle Clusterware Todd's MySQL Blog

Leave a Reply

Your email address will not be published. Required fields are marked *


2 + four =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>