MySQL Bugs: #56411: Socket Hangs When Fetching Large Result Set

Bug #56411	Socket Hangs When Fetching Large Result Set
Submitted:	31 Aug 2010 15:26	Modified:	21 Jan 2013 9:11
Reporter:	Jon Felch	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.1.13	OS:	Any
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
1.) Connection is acquired

Connection is created with a basic URL plus following rules:

?zeroDateTimeBehavior=convertToNull&useCursorFetch=true&defaultFetchSize=25000

// removing zeroDateTimeBehavior=convertToNull is not an option because of PERL code that uses zero dates (not really a feature FYI..._

// removing &useCursorFetch=true&defaultFetchSize=25000 does help

2.) JDBC Query is executed for table with 360,000 rows as follows:

Statement stmt = connection.createStatement(
  ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);

stmt.setFetchSize(Integer.MIN_VALUE);
int count = 0;

PreparedStatement stmt = connection.prepareStatement();
ResultSet rs = stmt.executeQuery("select * from equity_options");
while (rs.next()) {
  // process data

}  

// randomly hangs on some tables but not others after about 900-1500 rows

Call stack when it dies looks like this:

at java.net.SocketInputStream.socketRead0(Native Method)
	at java.net.SocketInputStream.read(SocketInputStream.java:129)
	at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
	at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
	at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
	- locked <0x00007f1921d95cc0> (a com.mysql.jdbc.util.ReadAheadInputStream)
	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2499)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3008)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2941)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:919)
	at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1416)
	at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:418)
	at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:397)
	at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7171)

How to repeat:
Don't really know beyond what I have tolds you.  We do not used Apache DBCP, Hibernate, or anything else that might cause problems

Suggested fix:
It looks to me (based on my debugging and other bug reports) that there is a but in ReadAheadInputStream

Strangely adding an "order by" clause to the query removes the problem

Does changing to use non-read-ahead work, i.e. "useReadAheadInput=false&useUnbufferedInput=false" change this behavior?

Yes!

  useReadAheadInput=false

is the switch that makes the things work :-))

Perhaps a deadlock between the read and the read ahead thread ...

There's no separate thread. ReadAheadInput is essentially what BufferedInputStream should be (i.e. read into the buffer if data is available to read, but don't try and fill the buffer to capacity and block if no data is available). However, if inputStream.available() lies to us, we may block in a buggy way.

There would be a lot of log output, but if you add traceProtocol=true to your JDBC URL and remove the "useReadAheadInput=false" and post the tail-end of it (a few working rows, and then when it hangs), that would be helpful in diagnosing exactly what's wrong.

I close the report since there is no new input for 2 years.

Please, feel free to reopen if problem still exists.

I have reopened it with  #74739. In my ase however the table has only 21 rows. Adding an order by also prevented the problem
Please see some complete trace log attached.