MySQL Bugs: #8487: Streaming results one row at a time from PreparedStatement not working

Bug #8487	Streaming results one row at a time from PreparedStatement not working
Submitted:	13 Feb 2005 10:28	Modified:	16 Feb 2005 17:28
Reporter:	Iain Hogg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.6	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
mysql server: 4.0.20a
connector/J: 3.1.6

I'm streaming back results from the server one at a time, using the following code (statement is the same, but with column/table names changed):

// Read one row at a time, too much to hold in memory
c = DriverManager.getConnection( m_url );
PreparedStatement stmt = c.prepareStatement(
           "SELECT A, B, C FROM TableA ORDER BY C ASC",
           ResultSet.TYPE_FORWARD_ONLY,
           ResultSet.CONCUR_READ_ONLY );

stmt.setFetchSize( Integer.MIN_VALUE );
ResultSet rs = stmt.executeQuery();

I was using connector/J version 3.0.14-production with server v4.0.20a and this was working fine.
I changed the connector/J to version 3.1.16, now running the same code will block and eventually the JVM will die with an OutOfMemoryException.  Based on this, I'm assuming that its no longer streaming back results one at a time.

How to repeat:
This will occur every time I run the code above using connector/J version 3.1.6, and it works fine every time I use version 3.0.14-production.

I was not able to reproduce it. I attache a test script that I used for testing. Could you please to check it on your computer.

my environment is: windows xp sp2 , java 1.5 , connector/j 3.1.6, mysql 4.1.9 and 4.0.23

testcase that I used

Attachment: bug8487.java (application/octet-stream, text), 1.52 KiB.

I can reproduce the problem on my machine using the test case you have provided.  I'm running in the debugger and trapping at the first execution of the line:

String test = rs.getString(3);

Running using connector/J 3.1.6, rs.rowData is an instance of RowDataStatic, and the value of rs.rowData.rows is an ArrayList of size 2, containing *both* rows even though they are supposed to be streamed.

Running using connector/J 3.0.14-production, the value of rs.rowData is RowDataDynamic, and rs.nextRow contains a byte[][] array holding the results of the first row only.

Are you seeing different results to this?  Some additional environment information: I'm running on WinXP SP1, java 1.5.0_00

I've been having a look at the connector/J 3.1.6 source when running in the debugger and the problem appears to be in com.mysql.jdbc.PreparedStatement at line 1812:

    protected ResultSet executeInternal(int maxRowsToRetrieve,
        Buffer sendPacket, boolean createStreamingResultSet,
        boolean queryIsSelectOnly, boolean unpackFields)
        throws SQLException {
        this.numberOfExecutions++;

        ResultSet rs;
        rs = this.connection.execSQL(this, null, maxRowsToRetrieve, sendPacket,
                this.resultSetType, this.resultSetConcurrency, false, false,
                this.currentCatalog, unpackFields);

        return rs;
    }

As you can see, the parameter createStreamingResultSet is ignored and the execSQL call always passes false for the streamResults parameter.
I'm not familiar with the mySQL code, but it appears the reason is here.

I altered executeInternal in com.mysql.jdbc.PreparedStatement to pass through createStreamingResultSet to the execSQL call, recompiled the connector/J source and it appears to have restored streaming rows one at a time from the server - trapping at the rs.getString line is now showing the value of rs.rowData as RowDataDynamic, and rs.nextRow contains a byte[][] array holding the results of the first row only i.e. same as 3.0.14-production behaviour.

Can you confirm this and re-open the bug?

I actually fixed this for 3.1.7, don't know how it ended up in can't repeat.