Bug #42929 closing jdbc streaming resultset waits for all records

• Submitted: 17 Feb 2009 11:23
• Modified: 19 Feb 2009 15:38
• Reporter: yoav morag
• Status: Not a Bug
• Category: Connector / J
• Severity: S5 (Performance)
• Version: connectorJ 5.1.6 / MySQL 5.0
• OS: Linux
• CPU Architecture: Any
• Tags: connectorj, Streaming

[17 Feb 2009 11:23] yoav morag

Description:
after reading part of a very large (10 million) rowset with streaming result set, I close the rowset. this operation hangs, and viewing the mysql processlist shows the select query is still executed. 
I have also tried to set clobberStreamingResults=true , but it does not help 

stack :
SocketInputStream.socketRead0(FileDescriptor, byte[], int, int, int) line: not available [native method]	
SocketInputStream.read(byte[], int, int) line: 129	
ReadAheadInputStream.fill(int) line: 113	
ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(byte[], int, int) line: 160	
ReadAheadInputStream.read(byte[], int, int) line: 188	
MysqlIO.readFully(InputStream, byte[], int, int) line: 2428	
MysqlIO.reuseAndReadPacket(Buffer, int) line: 2938	
MysqlIO.reuseAndReadPacket(Buffer) line: 2871	
MysqlIO.checkErrorPacket(int) line: 3414	
MysqlIO.checkErrorPacket() line: 910	
MysqlIO.nextRow(Field[], int, boolean, int, boolean, boolean, boolean, Buffer) line: 1405	
RowDataDynamic.nextRecord() line: 413	
RowDataDynamic.next() line: 392	
RowDataDynamic.close() line: 170	
JDBC4ResultSet(ResultSetImpl).realClose(boolean) line: 7473	
JDBC4ResultSet(ResultSetImpl).close() line: 881	
MySQLDatabase$JDBCDocumentSet.close() line: 888	

How to repeat:
read large results set, stop after reading few records and try to close

[17 Feb 2009 11:27] Tonci Grgin

Hi Yoav and thanks for your report.

I would very much like to see test case attached with comments inside. What does server log say?

[19 Feb 2009 9:37] yoav morag

minimal test case

Attachment: Test1.java (text/x-java), 1.63 KiB.

[19 Feb 2009 9:40] yoav morag

attaching minimal test case. 
the test case creates 3 tables : one with 100 records, one with 10000 and one with 1000000. from each table it reads the 1st 100 records, and then closes the rowset. the time it takes to close the rowset is recorded and printed.

I think a decent implementation should have an O(1) time for closing the rowset.

output on my machine 
took: 0
took: 68
took: 890

[19 Feb 2009 15:27] Tonci Grgin

Yoav, you are right. Verified as described with test case attached...

No matter what I do, I can only make things worse.

[19 Feb 2009 15:28] Tonci Grgin

Test case

Attachment: TestBug42929.java (text/x-java), 3.30 KiB.

[19 Feb 2009 15:38] Mark Matthews

> I think a decent implementation should have an O(1) time for closing the rowset.

If the mysql protocol worked that way, the JDBC driver would use it. There's no way currently to close a result set "midstream", all of the rows have to be read off the wire, so it's going to be O(n). 

If possible, it's better to select the rows you actually want to examine (using WHERE, etc), rather than pulling them all down and stopping mid-stream.

Feel free to file a bug against the mysql server protocol, if you really need the functionality.

[19 Feb 2009 17:39] Einav Itamar

Mark,
The mysql_use_result on the C API work as expected, so I think that the problem is not with the MySQL server protocol, but with the jdbc conenctor implementation

[24 Feb 2009 5:26] Jess Balint

Einav,
How did you show this?

[13 Oct 2010 18:04] Mark Matthews

libmysql drains the result set just like Connector/J does, see the cli_flush_use_result function in sql-common/client.c.