MySQL Bugs: #13255: Statement exceute Batch failed on JDBC Failover

Bug #13255	Statement exceute Batch failed on JDBC Failover
Submitted:	16 Sep 2005 3:26	Modified:	28 Nov 2005 20:30
Reporter:	Leo Chan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.10	OS:	Sun Java 5 Update 5
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
I have a simple java program to test JDBC failover, i.e I configure the JDBC Driver with 2 IPs.  I use Statement addBatch/executeBatch in my test case and reuse the connection like connection Pool. Before Mast DB shutdown, the Statement run fine.  But when I execute a Batch after the Master DB shutdown, I found the first statement in the batch has not been executed but the remained statement in batch has been executed to the slave database.

How to repeat:
import java.io.*;
import java.sql.*;
import java.util.Properties;

public class TestMysqlR{

	public static void main(String[] args){
		Connection conn=null;
		Statement st =null;
		try{	
			Class.forName("com.mysql.jdbc.Driver");			
			Properties props = new Properties();
			props.put("password", "123456");
			props.put("autoReconnect", "true");
			props.put("roundRobinLoadBalance", "false");
			props.put("failOverReadOnly","false");
			props.put("user", "ppg");
			props.put("password", "123456");
			props.put("autoReconnectForPools", "false");
			props.put("queriesBeforeRetryMaster","2");
			props.put("secondsBeforeRetryMaster","60000");
			props.put("autoCommit","true");
			props.put("connectTimeout", "3000");
			props.put("socketTimeout", "3000");
			props.put("initialTimeout","1");
			props.put("prepStmtCacheSize","50");
			conn=DriverManager.getConnection("jdbc:mysql://192.168.0.30:3306,192.168.0.26:3306/ppg_load", 
								props);	
			st=conn.createStatement();                                                                    
			st.addBatch("insert into TEST (ID, COUNT, UPDATE_TIME) values(1,1, now())");
			st.addBatch("insert into TEST (ID, COUNT, UPDATE_TIME) values(2,1, now())");
			st.addBatch("insert into TEST (ID, COUNT, UPDATE_TIME) values(3,1, now())");
			st.addBatch("update TEST set COUNT=10");
			st.executeBatch();
			st.close();
			System.out.println("Shutdown master");
			Thread.sleep(20000);
			System.out.println("master has shutdown");
			st=conn.createStatement();
			st.addBatch("insert into TEST (ID, COUNT, UPDATE_TIME) values(1,1, now())");//this statement has been losted
			st.addBatch("insert into TEST (ID, COUNT, UPDATE_TIME) values(2,1, now())");
			st.addBatch("insert into TEST (ID, COUNT, UPDATE_TIME) values(3,1, now())");
			st.addBatch("update TEST set COUNT=10");
			st.executeBatch();
		}catch(Exception e){
			e.printStackTrace(System.err);			
		}finally{
			try{
				if(st!=null)st.close();
				if(conn!=null) conn.close();	
			}catch(Exception e){}	
		}				
	}
}

Test Program

Attachment: TestMysqlR.java (application/octet-stream, text), 1.97 KiB.

Sorry, there is Exception throw out and forget to provide

java.sql.BatchUpdateException: Communications link failure due to underlying exc
eption:

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Software caused connection abort: recv failed

STACKTRACE:

java.net.SocketException: Software caused connection abort: recv failed
        at java.net.SocketInputStream.socketRead0(Native Method)
        at java.net.SocketInputStream.read(SocketInputStream.java:129)
        at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.ja
va:105)
        at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNe
cessary(ReadAheadInputStream.java:148)
        at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.ja
va:176)
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1899)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2348)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2858)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2972)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:929)
        at com.mysql.jdbc.Statement.executeBatch(Statement.java:688)
        at TestMysqlR.main(TestMysqlR.java:44)

** END NESTED EXCEPTION **

Last packet sent to the server was 90 ms ago.
        at com.mysql.jdbc.Statement.executeBatch(Statement.java:708)
        at TestMysqlR.main(TestMysqlR.java:44)

I tried in another case and lost more statement in the batch (only the second statement is executed), please see my new attachement program TestMysqlR1.java

Test Program for another Test case

Attachment: TestMysqlR1.java (application/octet-stream, text), 2.28 KiB.

Could you please say exact versions of mysql servers ?

the mysql versionI used  is 4.1.14 installed at Red Hat ES 3

Fixed for 3.1.12 and 5.0.0. The driver will not reconnect during execution of a batch if autoReconnect is enabled.

A workaround until the release of 3.1.12 is to not run batches in autoCommit=true mode, as the driver won't reconnect during a transaction.