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

[16 Sep 2005 3:26] Leo Chan
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){}	
		}				
	}
}
[16 Sep 2005 3:27] Leo Chan
Test Program

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

[16 Sep 2005 3:35] Leo Chan
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)
[16 Sep 2005 3:45] Leo Chan
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
[16 Sep 2005 3:45] Leo Chan
Test Program for another Test case

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

[24 Sep 2005 9:40] Vasily Kishkin
Could you please say exact versions of mysql servers ?
[24 Sep 2005 11:26] Leo Chan
the mysql versionI used  is 4.1.14 installed at Red Hat ES 3
[28 Nov 2005 20:30] Mark Matthews
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.