SocketException Thrown when Creating a Connection Using com.mysql.jdbc.ReplicationDriver during the Fail-Over

[John Lin]

Hi,

I try to test Mysql fail-over and fail-back function using com.mysql.jdbc.ReplicationDriver. When the fail-over happens (master server is shutdown), the following exception is thrown while trying to create a new connection. The same stand-alone test code and same setting are used to test the same functionality using com.mysql.jdbc.Driver and org.gjt.mm.mysql.Driver, no exception thrown, and the function (both fail-over and fail-back) works as expected. The test code basically is the same as in Mysql 5.0 document.

Can anyone help me to tell how to use com.mysql.jdbc.ReplicationDriver, or configure, or is there an issue with driver itself?

Thanks a lot.

Regards

-John

Test setting-up:

Master : lab003 (default port)
Slave : localhost (default port) --read-only mode
driver : com.mysql.jdbc.ReplicationDriver
version : 3.1.12
autoReconnection : true
roundRobinLoadBalance : false
autocommit : false
conection URL : jdbc:mysql://lab003,localhost/address

When both master and slave are alive, write/update request to the master, and read query from the slave, which are expected. Once, the Master is shutdown, the following exception is thrown:

java.sql.SQLException: Server connection failure during transaction. Due to underlying exception: 'java.net.SocketException: java.net.ConnectException: Connection refused'.

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: java.net.ConnectException: Connection refused

STACKTRACE:

java.net.SocketException: java.net.ConnectException: Connection refused
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:156)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:284)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2680)
at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
at com.mysql.jdbc.ReplicationConnection.<init>(ReplicationConnection.java:53)
at com.mysql.jdbc.NonRegisteringReplicationDriver.connect(NonRegisteringReplicationDriver.java:116)
at com.tango.mw.persistence.ReplicationDriverTest.main(ReplicationDriverTest.java:45)


** END NESTED EXCEPTION **


Attempted reconnect 3 times. Giving up.
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2749)
at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
at com.mysql.jdbc.ReplicationConnection.<init>(ReplicationConnection.java:53)
at com.mysql.jdbc.NonRegisteringReplicationDriver.connect(NonRegisteringReplicationDriver.java:116)
at com.tango.mw.persistence.ReplicationDriverTest.main(ReplicationDriverTest.java:45)
Create connection exception
java.sql.SQLException: Server connection failure during transaction. Due to underlying exception: 'java.net.SocketException: java.net.ConnectException: Connection refused'.

** BEGIN NESTED EXCEPTION **


############ the test code #########################

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.util.Properties;

import com.mysql.jdbc.ReplicationDriver;



public class ReplicationDriverTest {



static final String repDriver = "com.mysql.jdbc.ReplicationDriver";

static final String norDriver = "com.mysql.jdbc.Driver";

static final String mmDriver = "org.gjt.mm.mysql.Driver";

static final String url = "jdbc:mysql://lab003,localhost/address";

static boolean isReplicationDriver = true;



public static void main(String[] args) {



Properties props = new Properties();



props.setProperty("user", "test");

props.setProperty("password", "test");

props.setProperty("autoReconnect", "true");

props.setProperty("roundRobinLoadBalance", "false");

props.setProperty("autocommit", "false");



System.out.println("****************Mysql Drive Testing***************");

System.out.println("driver : " + repDriver);

System.out.println("version : " + "3.1.12");

System.out.println("autoReconnection : " + props.getProperty("autoReconnect"));

System.out.println("roundRobinLoadBalance : " + props.getProperty("roundRobinLoadBalance"));

System.out.println("autocommit : " + props.getProperty("autocommit"));

System.out.println("conection URL : " + url);

System.out.println("\nThis test program is to test raw Mysql connection driver regarding fail-over/-back function");

System.out.println("The slave is running in a read-only and slave-stopped mode");



for (int i = 1; i <= 40; i++ ) {

try {



ReplicationDriver theDriver = new ReplicationDriver();

Connection repConn = theDriver.connect(url,props);



//Class.forName(norDriver);

//Class.forName(mmDriver);

//Connection repConn = DriverManager.getConnection(url, props);



repConn.setReadOnly(false);

repConn.setAutoCommit(false);

int k = 5 + i;

try {



String sql = "insert into Address values (" + k +

",'401 Arapho Rd.','Richardson','TX','70440')";

repConn.createStatement().execute(sql);



// repConn.createStatement().execute("insert into Address values (5,'401 Arapho Rd.','Richardson','TX','70440')");

// repConn.createStatement().executeUpdate("update Address set state = 'CA' where addressid = 5");

System.out.println("Update Database");

repConn.commit();

}

catch (Exception ex) {

System.out.println("Database writing exception");

ex.printStackTrace();

}



repConn.setReadOnly(true);

try {

ResultSet rs = repConn.createStatement().executeQuery("Select * from Address");

System.out.println("\nI = " + i);

while (rs.next()) {

int addressId = rs.getInt(1);

String streetName = rs.getString(2);

System.out.println("Address ID = " + addressId +

" StreetName = " + streetName);

}

}

catch (Exception ex) {

System.out.println("Database reading exception");

ex.printStackTrace();

}

repConn.close();

}

catch (Exception ex) {

ex.printStackTrace();

System.out.println("Create connection exception");

}



if (i == 10) {

System.out.println("\n!!!!!!!!!!!!!!!! Shutdown the Master Server !!!!!!!!!!!!!!\n");

}

else if( i == 20 ) {

System.out.println("\n!!!!!!!!!!!!!!!! Start the Master Server !!!!!!!!!!!!!!!!!\n");

}



try {

Thread.sleep(1000);

}

catch (Exception e) {



}

} // end for

}



}