Author: tbedford
Date: 2010-10-26 18:17:32 +0200 (Tue, 26 Oct 2010)
New Revision: 23393
Log:
autoReconnect and communication failures (thanks Todd Farmer)
Modified:
trunk/refman-common/connector-j.xml
Modified: trunk/refman-common/connector-j.xml
===================================================================
--- trunk/refman-common/connector-j.xml 2010-10-26 15:51:42 UTC (rev 23392)
+++ trunk/refman-common/connector-j.xml 2010-10-26 16:17:32 UTC (rev 23393)
Changed blocks: 1, Lines Added: 142, Lines Deleted: 0; 5786 bytes
@@ -6561,6 +6561,148 @@
</qandaentry>
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Why does Connector/J not reconnect to MySQL and re-issue
+ the statement after a communication failure, instead of
+ throwing an Exception, even though I use the
+ <literal>autoReconnect</literal> connection string option?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ There are several reasons for this. The first is
+ transactional integrity. The MySQL Reference Manual states
+ that <quote>there is no safe method of reconnecting to the
+ MySQL server without risking some corruption of the
+ connection state or database state information</quote>.
+ Consider the following series of statements for example:
+ </para>
+
+<programlisting language="java">
+<![CDATA[
+conn.createStatement().execute(
+ "UPDATE checking_account SET balance = balance - 1000.00 WHERE customer='Smith'");
+conn.createStatement().execute(
+ "UPDATE savings_account SET balance = balance + 1000.00 WHERE customer='Smith'");
+conn.commit();
+]]>
+</programlisting>
+
+ <para>
+ Consider the case where the connection to the server fails
+ after the <literal>UPDATE</literal> to
+ <literal>checking_account</literal>. If no exception is
+ thrown, and the application never learns about the
+ problem, it will continue executing. However, the server
+ did not commit the first transaction in this case, so that
+ will get rolled back. But execution continues with the
+ next transaction, and increases the
+ <literal>savings_account</literal> balance by 1000. The
+ application did not receive an exception, so it continued
+ regardless, eventually commiting the second transaction,
+ as the commit only applies to the changes made in the new
+ connection. Rather than a transfer taking place, a deposit
+ was made in this example.
+ </para>
+
+ <para>
+ Note that running with <literal>auto-commit</literal>
+ enabled does not solve this problem. When Connector/J
+ encounters a communication problem, there is no means to
+ determine whether the server processed the currently
+ executing statement or not. The following theoretical
+ states are equally possible:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The server never received the statement, and therefore
+ no related processing occurred on the server.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The server received the statement, executed it in
+ full, but the response was not received by the client.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ If you are running with <literal>auto-commit</literal>
+ enabled, it is not possible to guarantee the state of data
+ on the server when a communication exception is
+ encountered. The statement may have reached the server, or
+ it may not. All you know is that communication failed at
+ some point, before the client received confirmation (or
+ data) from the server. This does not only affect
+ <literal>auto-commit</literal> statements though. If the
+ communication problem occurred during
+ <literal>Connection.commit()</literal>, the question
+ arises of whether the transaction was committed on the
+ server before the communication failed, or whether the
+ server received the commit request at all.
+ </para>
+
+ <para>
+ The second reason for the generation of exceptions is that
+ transaction-scoped contextual data may be vulnerable, for
+ example:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Temporary tables
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ User-defined variables
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Server-side prepared statements
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ These items are lost when a connection fails, and if the
+ connection silently reconnects without generating an
+ exception, this could be detrimental to the correct
+ execution of your application.
+ </para>
+
+ <para>
+ In summary, communication errors generate conditions that
+ may well be unsafe for Connector/J to simply ignore by
+ silently reconnecting. It is necessary for the application
+ to be notified. It is then for the application developer
+ to decide how to proceed in the event of connection errors
+ and failures.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
</qandaset>
</section>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r23393 - trunk/refman-common | tony.bedford | 26 Oct |
