Chances are, if you write Java applications using My SQL’s Connector/ J driver, you’ve run across the auto Reconnect 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 My SQL and re-issue my statement, instead of throwing this Exception?” There are actually a number of reasons, starting with loss of transactional integrity. The My SQL Manual states that “there is no safe method of reconnecting to the My SQL server without risking some corruption of the connection state or database state information.” Imagine the following series of statements: conn.create Statement.execute( UPDATE checking_account SET balance = balance - 1000.00 WHERE customer=' Todd' conn.create Statement.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 00.00, you just gave me 00.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.
I am not a user of this library, so my knowledge of it is only that last 10 mins worth, so please do verify. As a general rule, the best resource of such information about usage of various specific details of a library is to take a look at its unit tests. The best thing about OSS. So if you look at My SQL Connector/ C+ unit tests that can be found on their source tree, you will see the below extract. sql: Connect Options Map connection_properties;. connection_properties[ OPT_ RECONNECT ]=true; try con.reset(driver->connect(connection_properties catch (sql: SQLException For more information, please do the below, so that you can take a look yourselves. ~/tmp$ bzr branch lp:~mysql/mysql-connector-cpp/trunk mysql-connector-cpp ~/tmp$ vi mysql-connector-cpp/test/unit/classes/connection.cpp +170 ~/tmp$ vi mysql-connector-cpp/test/unit/classes/connection.h Having said all that, reconnect option in mysql has to be used very carefully, as you will have to reset any session variables, etc. You will have to treat a reconnected connection as a brand new connection. This has to be verified with the documentation of the particular version of My SQL you are working with.
Download Now