Communications link failure错误分析<转>_sqlerror.createcommunicationexception-CSDN博客

原文地址:http://frankfan915.iteye.com/blog/1672465

异常描述Exception:

  
The last packet successfully received from the server was 556,627 milliseconds ago.  The last packet sent successfully to the server was 556,510 milliseconds ago.  
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)  
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)  
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)  
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)  
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)  
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)  
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3102)  
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2991)  
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3532)  
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)  
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)  
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)  
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)  
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:842)  
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:681)

2. 问题分析：

从网上分析得知，此问题一般是MySQL端数据发送timeout所致。MySQL中可以设置net_write_timeout这个变量，来调整IO写的时候的超时时间。

connect_timeout

The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake.

interactive_timeout

The number of seconds the server waits for activity on an interactive connection before closing it.

wait_timeout

The number of seconds the server waits for activity on a noninteractive connection before closing it.

net_read_timeout

The number of seconds to wait for more data from a connection before aborting the read.

net_write_timeout

The number of seconds to wait for a block to be written to a connection before aborting the write.

由此可知，当客户端长时间不接收数据的时候，就会断开连接。接下来我们尝试在MySQL这边设置net_write_timeout,但发现还是无效，百思不得其解。

查看jdbc驱动发现，当调用PreparedStatement的executeQuery（）方法的时候，如果我们是去获取流式resultset的话，就会默认执行SET net_write_timeout= ？这个命令去重新设置timeout时间。源代码如下：

if (doStreaming && this.connection.getNetTimeoutForStreamingResults() > 0) {
				
				java.sql.Statement stmt = null;
				
				try {
					stmt = this.connection.createStatement();
					
					((com.mysql.jdbc.StatementImpl)stmt).executeSimpleNonQuery(this.connection, "SET net_write_timeout=" 
							+ this.connection.getNetTimeoutForStreamingResults());
				} finally {
					if (stmt != null) {
						stmt.close();
					}
				}
			}

因此我们需要为MySQL驱动的connection这个类设置NetTimeoutForStreamingResults。当不设置NetTimeoutForStreamingResults这个变量的时候，默认是600秒。

接下来查看MySQL的源代码，发现最后影响的是sockopt的SO_SNDTIMEO。

void vio_timeout(Vio *vio, uint which, uint timeout)
{
  r= setsockopt(vio->sd, SOL_SOCKET, which ? SO_SNDTIMEO : SO_RCVTIMEO,
                IF_WIN(const char*, const void*)&wait_timeout,
                sizeof(wait_timeout));
}

在msdn上有如下解释

setsockopt(SO_RCVTIMEO) "If a send or receive operation times out on a
socket, the socket state is indeterminate, and should not be used; TCP
sockets in this state have a potential for data loss, since the
operation could be canceled at the same moment the operation was to be
completed."

3.问题重现

在类ReadAheadInputStream中的fill方法中的this.underlyingStream.read处设置一个断点。停留长于NetTimeoutForStreamingResults的时间，这个问题就会重现了。MySQL驱动最终用来读取网络数据的对象是underlyingStream。

private void fill(int readAtLeastTheseManyBytes) throws IOException {
		checkClosed();

		this.currentPosition = 0; /* no mark: throw away the buffer */

		this.endOfCurrentData = currentPosition;

		// Read at least as many bytes as the caller wants, but don't
		// block to fill the whole buffer (like java.io.BufferdInputStream
		// does)

		int bytesToRead = Math.min(this.buf.length - currentPosition,
				readAtLeastTheseManyBytes);

		int bytesAvailable = this.underlyingStream.available();

		if (bytesAvailable > bytesToRead) {

			// Great, there's more available, let's grab those
			// bytes too! (read-ahead)

			bytesToRead = Math.min(this.buf.length - currentPosition,
					bytesAvailable);
		}

		if (this.doDebug) {
			StringBuffer debugBuf = new StringBuffer();
			debugBuf.append("  ReadAheadInputStream.fill(");
			debugBuf.append(readAtLeastTheseManyBytes);
			debugBuf.append("), buffer_size=");
			debugBuf.append(this.buf.length);
			debugBuf.append(", current_position=");
			debugBuf.append(currentPosition);
			debugBuf.append(", need to read ");
			debugBuf.append(Math.min(this.buf.length - currentPosition,
					readAtLeastTheseManyBytes));
			debugBuf.append(" bytes to fill request,");

			if (bytesAvailable > 0) {
				debugBuf.append(" underlying InputStream reports ");
				debugBuf.append(bytesAvailable);

				debugBuf.append(" total bytes available,");
			}

			debugBuf.append(" attempting to read ");
			debugBuf.append(bytesToRead);
			debugBuf.append(" bytes.");

			if (this.log != null) {
				this.log.logTrace(debugBuf.toString());
			} else {
				System.err.println(debugBuf.toString());
			}
		}

		int n = this.underlyingStream.read(this.buf, currentPosition,
				bytesToRead);

		if (n > 0) {
			endOfCurrentData = n + currentPosition;
		}
	}

原文地址:http://frankfan915.iteye.com/blog/1672465