Bug #41321 ClassCastException when using jdbc:mysql:loadbalance: and XAConnection

• Submitted: 9 Dec 2008 10:19
• Modified: 11 Nov 3:39
• Reporter: John Farrelly
• Status: Won't fix
• Category: Connector/J
• Severity: S1 (Critical)
• Version: 5.1.7
• OS: Any
• Tags: Connector/J, loadbalance, xaconnection

[9 Dec 2008 10:19] John Farrelly

Description:
I am using using the load balancing Connector/J driver using the URL:

jdbc:mysql:loadbalance://host1,host2/db?loadBalanceStrategy=bestResponseTime

If I then try to use this URL in an XA connection, I get a ClassCastException:

MysqlXADataSource dataSource = new MysqlXADataSource();
dataSource.setUrl(loadBalanceURL);
dataSource.setUser(username);
dataSource.setPassword(password);

XAConnection conn = dataSource.getXAConnection(); // ClassCastException

The exception is: 

$Proxy0 cannot be cast to com.mysql.jdbc.Connection

Looking at the code, I see the loadbalance driver creates a Proxy, which implements the
jdbc.sql.Connection interface.  However, the code in MysqlXADataSource.getXAConnection()
tries to cast the driver to com.mysql.jdbc.Connection, and this fails since the
loadbalance proxy only implements the jdbc.sql.Connection.

How to repeat:
import java.sql.ResultSet;
import java.sql.Statement;

import javax.sql.XAConnection;

import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;

public class Test {

	private static final String URL =
"jdbc:mysql:loadbalance://host1,host2/db?loadBalanceStrategy=bestResponseTime";

	private static final String USERNAME = "dbuser";

	private static final String PASSWORD = "dbpass";

	public static void main(String[] args) throws Exception {

		MysqlXADataSource dataSource = new MysqlXADataSource();
		dataSource.setUrl(URL);
		dataSource.setUser(USERNAME);
		dataSource.setPassword(PASSWORD);

		XAConnection conn = dataSource.getXAConnection();

		Statement stmt = null;
		ResultSet rs = null;

		while (true) {
			try {
				Thread.sleep(1000);
				conn = dataSource.getXAConnection();
				stmt = conn.getConnection().createStatement();
				stmt.executeQuery("SELECT 1;");
				stmt.close();
			} catch (Throwable t) {
				System.err.println(t.getMessage());
			} finally {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			}
		}
	}
}

[9 Dec 2008 11:20] Tonci Grgin

Hi John and thanks for your report.

While I'm analysing it can you please try doing this
  XAConnection conn = dataSource.getXAConnection().getConnection();
and see if it works.

[9 Dec 2008 11:47] John Farrelly

Hi Tonci,

Thanks for your quick response.  Unfortunately your suggestion did not work, I still get
the ClassCastException.

[9 Dec 2008 16:46] Tonci Grgin

Hi John.

Ummm, where to start... The load balancing xa connection could work *but* there'd have to
be more work done. The current load balancing code picks new hosts on transaction
boundaries. But it only knows about commit(), rollback() and such, it doesn't know about
XA transaction boundaries. Thus your exception and there's no way to work around it. Even
worse, server XA implementation can easily put you into heuristic recovery, which, well,
is not good for you.
However, you should be able to use a normal JDBC connection with
"roundRobinLoadBalance=true" to accomplish most of the things you might need. It's not as
"glamorous" as jdbc:mysql:loadbalance but I don't see us supporting that with XA any time
soon, at least not until XA is better in the server. So, I can't really tell you when
"later" is as it will depend on many factors...