Bug #41321 ClassCastException when using jdbc:mysql:loadbalance: and XAConnection
Submitted: 9 Dec 2008 9:19 Modified: 11 Nov 2009 2:39
Reporter: John Farrelly Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:5.1.7 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: Connector/J, loadbalance, xaconnection

[9 Dec 2008 9: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 10: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 10:47] John Farrelly
Hi Tonci,

Thanks for your quick response.  Unfortunately your suggestion did not work, I still get the ClassCastException.
[9 Dec 2008 15: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...
[17 Dec 2013 8:39] Manjunatha Nakshathri
Hello All, 

Is this bug fixed?  I am facing the same issue while connecting to My SQL cluster from JBoss XA data source. Please help. 

regards, 
Manjunath
[17 Dec 2013 8:56] Alexander Soklakov
Hi Manjunatha,

It isn't fixed, please see Bug#62577 for further discussion.