Bug #4524 autoReconnect fails after setAutoCommit

• Submitted: 12 Jul 2004 18:51
• Modified: 3 Aug 2004 17:44
• Reporter: Paolo Campanella
• Status: Not a Bug
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 3.0.14
• OS: Linux (Linux)
• Assigned to: Mark Matthews
• CPU Architecture: Any

[12 Jul 2004 18:51] Paolo Campanella

Description:

In the context of using autoReconnect, if one does the following:
- call setAutoCommit, trapping comm timeout exceptions
- issue an arbitrary query

the second call (and any subsequent calls) will fail. However, if one does this:
- call setAutoCommit, trapping comm timeout exceptions
- call setAutoCommit again
- issue an arbitrary query

everything works perfectly.

It is unlikely that a user would _not_ repeat the setAutoCommit call (an indeed would be crazy not to). However, this is unintuitive behaviour.

How to repeat:

Create "con" to be a connection with autoReconnect=true, and then:

		try {
			Statement cstmt = con.createStatement();
			cstmt.executeUpdate("set wait_timeout=5");
			System.out.println("Waiting for connection to time out");
			try { Thread.currentThread().sleep(8000); } catch (InterruptedException e) {}
			try {
				con.setAutoCommit(false);
			}
			catch (SQLException sqlE) {
				System.out.println("Caught SQL Exception: " + sqlE);
			}
			System.out.println("Waiting for autoreconnect to reconnect");
			try { Thread.currentThread().sleep(500); } catch (InterruptedException e) {}
			System.out.println("Issuing a simple query");
			// repeat earlier call - uncomment to make code work: con.setAutoCommit(false);
			cstmt = con.createStatement();
			ResultSet rs = cstmt.executeQuery("select 1");
			if (rs.next()) System.out.println("Got 1 row");
		}
		catch (Exception e) { e.printStackTrace(); }

Output is:

Waiting for connection to time out
Caught SQL Exception: java.sql.SQLException: Communication link failure: java.io.EOFException, underlying cause: null

[etc etc]

Waiting for autoreconnect to reconnect
Issuing a simple query
java.sql.SQLException: Communication link failure: java.io.IOException, underlying cause: Broken pipe

** BEGIN NESTED EXCEPTION ** 

java.io.IOException
MESSAGE: Broken pipe

STACKTRACE:

java.io.IOException: Broken pipe
	at java.net.SocketOutputStream.socketWrite(Native Method)
	at java.net.SocketOutputStream.write(SocketOutputStream.java:95)
	at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:78)
	at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:136)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:1758)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:1721)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1144)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
	at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1218)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2233)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2181)
	at com.mysql.jdbc.Statement.executeQuery(Statement.java:1158)
	at net.vsms.vsmsbackend.test.TestBed.<init>(TestBed.java:82)
	at net.vsms.vsmsbackend.test.TestBed.main(TestBed.java:820)

** END NESTED EXCEPTION **

	at java.lang.Throwable.<init>(Throwable.java:96)
	at java.lang.Exception.<init>(Exception.java:44)
	at java.sql.SQLException.<init>(SQLException.java:45)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:1779)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:1721)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1144)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
	at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1218)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2233)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2181)
	at com.mysql.jdbc.Statement.executeQuery(Statement.java:1158)
	at net.vsms.vsmsbackend.test.TestBed.<init>(TestBed.java:82)
	at net.vsms.vsmsbackend.test.TestBed.main(TestBed.java:820)

[3 Aug 2004 17:44] Mark Matthews

The autoReconnect feature is not really recommended to be used at all, it is a workaround for substandard connection pool implementations (which no longer exist) and non-robust JDBC use cases (i.e. developers not catching and/or dealing with SQLExceptions). It will in fact will most likely be deprecated in Connector/J 3.2. 

You should instead be setting wait_timeout to some high value, and catching communications exceptions (SQLState of '08S01') in your own code and dealing with them appropriately, as this is how the JDBC spec expects applications to work.

You can alternatively use the 'reconnectAtTxEnd' property which will attempt a reconnect at the end of each transaction, at the expense of some performance.